access 2003 to SQL server migration

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We are migrating one of our clients databases from an access 2003 = database to their sql server 2000 database being that i have mostly = worked with oracle datbases I had come up with a list of things that we = were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and = not insert update or delete from any exisitng table and any new tables = without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes = QA have them promoted to DBO or can you only do that with stored = procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We are migrating one of our clients = databases from an access 2003 database to their sql server 2000 database being that i = have mostly worked with oracle datbases I had come up with a list of things = that we were going to have to implement but i had some questions.


can you set it so certain roles can = only select from a table and not insert update or delete from any exisitng table = and any new tables without having to do it for each individual new = table
Can users create tables under thier = user and then once it passes QA have them promoted to DBO or can you only do that = with stored procedures
--=_NextPart_000_0008_01C5FBF1.DED8AFC0--This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
1. You can make them a member of the datareader role and then they have =access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP

"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and =not insert update or delete from any exisitng table and any new tables =without having to do it for each individual new table 2.. Can users create tables under thier user and then once it passes =QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_002A_01C5FC12.D9101D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

1. You can make them a member of the =datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" wrote in message news:ZA0mf.139758$y_1.98475=@.edtnps89...
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some =questions.


can you set it so certain roles can =only select from a table and not insert update or delete from any exisitng table =and any new tables without having to do it for each individual new =table Can users create tables under thier =user and then once it passes QA have them promoted to DBO or can you only do =that with stored procedures

---

--=_NextPart_000_002A_01C5FC12.D9101D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
makes sense on #2 i jsut wanted to rule it out because i'm sure its =going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message =news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they =have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your =production server anyway.
-- Andrew J. Kelly SQL MVP

"Gary Townsend (Spatial Mapping Ltd.)" =<garytNOSPAM@.spatialmapping.com> wrote in message =news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 =database to their sql server 2000 database being that i have mostly =worked with oracle datbases I had come up with a list of things that we =were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table =and not insert update or delete from any exisitng table and any new =tables without having to do it for each individual new table 2.. Can users create tables under thier user and then once it =passes QA have them promoted to DBO or can you only do that with stored =procedures
--=_NextPart_000_0036_01C5FBFE.57EB4AB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

makes sense on #2 i jsut wanted to rule =it out because i'm sure its going to get asked of me.
"Andrew J. Kelly" wrote in message news:%23JvZHzD$FHA.3=28@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the =datareader role and then they have access to read all tables but nothing =else.

2. Basically no. Development and QA =should not be done on your production server anyway.
-- Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" wrote in message news:ZA0mf.139758$y_1.98475=@.edtnps89...
We are migrating one of our clients =databases from an access 2003 database to their sql server 2000 database being =that i have mostly worked with oracle datbases I had come up with a list of =things that we were going to have to implement but i had some questions.


can you set it so certain roles =can only select from a table and not insert update or delete from any =exisitng table and any new tables without having to do it for each =individual new table Can users create tables under =thier user and then once it passes QA have them promoted to DBO or can you only =do that with stored =procedures

---

--=_NextPart_000_0036_01C5FBFE.57EB4AB0--

about identifier

in the MSDN
http://msdn2.microsoft.com/en-us/library/ms175874.aspx
it says that
The rules for the format of regular identifiers depend on the database
compatibility level. This level can be set by using sp_dbcmptlevel.
When the compatibility level is 90, the following rules apply:
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 3.2. The
Unicode definition of letters includes Latin characters from a through
z, from A through Z, and also letter characters from other languages.
* The underscore (_), at sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have
special meaning in SQL Server. A regular identifier that starts with
the at sign always denotes a local variable or parameter and cannot be
used as the name of any other type of object. An identifier that starts
with a number sign denotes a temporary table or procedure. An
identifier that starts with double number signs (##) denotes a global
temporary object. Although the number sign or double number sign
characters can be used to begin the names of other types of objects, we
do not recommend this practice.
Some Transact-SQL functions have names that start with
double at signs (@.@.). To avoid confusion with these functions, you
should not use names that start with @.@..
2. Subsequent characters can include the following:
* Letters as defined in the Unicode Standard 3.2.
* Decimal numbers from either Basic Latin or other national
scripts.
* The at sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL
Server reserves both the uppercase and lowercase versions of reserved
words.
4. Embedded spaces or special characters are not allowed.
When identifiers are used in Transact-SQL statements, the identifiers
that do not comply with these rules must be delimited by double
quotation marks or brackets.
so, why can I create a database with name "!nihao nihao" (it contradict
with item 1 and 4 above )?
Thanks
Because, as the paragraph immediately after #4 explains, using double
quotation marks is a way to 'override' the normal naming requirements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1163388171.249209.74300@.b28g2000cwb.googlegro ups.com...
> in the MSDN
> http://msdn2.microsoft.com/en-us/library/ms175874.aspx
> it says that
> --
> The rules for the format of regular identifiers depend on the database
> compatibility level. This level can be set by using sp_dbcmptlevel.
> When the compatibility level is 90, the following rules apply:
> 1. The first character must be one of the following:
> * A letter as defined by the Unicode Standard 3.2. The
> Unicode definition of letters includes Latin characters from a through
> z, from A through Z, and also letter characters from other languages.
> * The underscore (_), at sign (@.), or number sign (#).
> Certain symbols at the beginning of an identifier have
> special meaning in SQL Server. A regular identifier that starts with
> the at sign always denotes a local variable or parameter and cannot be
> used as the name of any other type of object. An identifier that starts
> with a number sign denotes a temporary table or procedure. An
> identifier that starts with double number signs (##) denotes a global
> temporary object. Although the number sign or double number sign
> characters can be used to begin the names of other types of objects, we
> do not recommend this practice.
> Some Transact-SQL functions have names that start with
> double at signs (@.@.). To avoid confusion with these functions, you
> should not use names that start with @.@..
> 2. Subsequent characters can include the following:
> * Letters as defined in the Unicode Standard 3.2.
> * Decimal numbers from either Basic Latin or other national
> scripts.
> * The at sign, dollar sign ($), number sign, or underscore.
> 3. The identifier must not be a Transact-SQL reserved word. SQL
> Server reserves both the uppercase and lowercase versions of reserved
> words.
> 4. Embedded spaces or special characters are not allowed.
> When identifiers are used in Transact-SQL statements, the identifiers
> that do not comply with these rules must be delimited by double
> quotation marks or brackets.
> so, why can I create a database with name "!nihao nihao" (it contradict
> with item 1 and 4 above )?
> Thanks
>

about identifier

in the MSDN
http://msdn2.microsoft.com/en-us/library/ms175874.aspx
it says that
--
The rules for the format of regular identifiers depend on the database
compatibility level. This level can be set by using sp_dbcmptlevel.
When the compatibility level is 90, the following rules apply:
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 3.2. The
Unicode definition of letters includes Latin characters from a through
z, from A through Z, and also letter characters from other languages.
* The underscore (_), at sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have
special meaning in SQL Server. A regular identifier that starts with
the at sign always denotes a local variable or parameter and cannot be
used as the name of any other type of object. An identifier that starts
with a number sign denotes a temporary table or procedure. An
identifier that starts with double number signs (##) denotes a global
temporary object. Although the number sign or double number sign
characters can be used to begin the names of other types of objects, we
do not recommend this practice.
Some Transact-SQL functions have names that start with
double at signs (@.@.). To avoid confusion with these functions, you
should not use names that start with @.@..
2. Subsequent characters can include the following:
* Letters as defined in the Unicode Standard 3.2.
* Decimal numbers from either Basic Latin or other national
scripts.
* The at sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL
Server reserves both the uppercase and lowercase versions of reserved
words.
4. Embedded spaces or special characters are not allowed.
When identifiers are used in Transact-SQL statements, the identifiers
that do not comply with these rules must be delimited by double
quotation marks or brackets.
---
so, why can I create a database with name "!nihao nihao" (it contradict
with item 1 and 4 above )?
ThanksBecause, as the paragraph immediately after #4 explains, using double
quotation marks is a way to 'override' the normal naming requirements.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1163388171.249209.74300@.b28g2000cwb.googlegroups.com...
> in the MSDN
> http://msdn2.microsoft.com/en-us/library/ms175874.aspx
> it says that
> --
> The rules for the format of regular identifiers depend on the database
> compatibility level. This level can be set by using sp_dbcmptlevel.
> When the compatibility level is 90, the following rules apply:
> 1. The first character must be one of the following:
> * A letter as defined by the Unicode Standard 3.2. The
> Unicode definition of letters includes Latin characters from a through
> z, from A through Z, and also letter characters from other languages.
> * The underscore (_), at sign (@.), or number sign (#).
> Certain symbols at the beginning of an identifier have
> special meaning in SQL Server. A regular identifier that starts with
> the at sign always denotes a local variable or parameter and cannot be
> used as the name of any other type of object. An identifier that starts
> with a number sign denotes a temporary table or procedure. An
> identifier that starts with double number signs (##) denotes a global
> temporary object. Although the number sign or double number sign
> characters can be used to begin the names of other types of objects, we
> do not recommend this practice.
> Some Transact-SQL functions have names that start with
> double at signs (@.@.). To avoid confusion with these functions, you
> should not use names that start with @.@..
> 2. Subsequent characters can include the following:
> * Letters as defined in the Unicode Standard 3.2.
> * Decimal numbers from either Basic Latin or other national
> scripts.
> * The at sign, dollar sign ($), number sign, or underscore.
> 3. The identifier must not be a Transact-SQL reserved word. SQL
> Server reserves both the uppercase and lowercase versions of reserved
> words.
> 4. Embedded spaces or special characters are not allowed.
> When identifiers are used in Transact-SQL statements, the identifiers
> that do not comply with these rules must be delimited by double
> quotation marks or brackets.
> ---
> so, why can I create a database with name "!nihao nihao" (it contradict
> with item 1 and 4 above )?
> Thanks
>

about identifier

in the MSDN
http://msdn2.microsoft.com/en-us/library/ms175874.aspx
it says that
--
The rules for the format of regular identifiers depend on the database
compatibility level. This level can be set by using sp_dbcmptlevel.
When the compatibility level is 90, the following rules apply:
1. The first character must be one of the following:
* A letter as defined by the Unicode Standard 3.2. The
Unicode definition of letters includes Latin characters from a through
z, from A through Z, and also letter characters from other languages.
* The underscore (_), at sign (@.), or number sign (#).
Certain symbols at the beginning of an identifier have
special meaning in SQL Server. A regular identifier that starts with
the at sign always denotes a local variable or parameter and cannot be
used as the name of any other type of object. An identifier that starts
with a number sign denotes a temporary table or procedure. An
identifier that starts with double number signs (##) denotes a global
temporary object. Although the number sign or double number sign
characters can be used to begin the names of other types of objects, we
do not recommend this practice.
Some Transact-SQL functions have names that start with
double at signs (@.@.). To avoid confusion with these functions, you
should not use names that start with @.@..
2. Subsequent characters can include the following:
* Letters as defined in the Unicode Standard 3.2.
* Decimal numbers from either Basic Latin or other national
scripts.
* The at sign, dollar sign ($), number sign, or underscore.
3. The identifier must not be a Transact-SQL reserved word. SQL
Server reserves both the uppercase and lowercase versions of reserved
words.
4. Embedded spaces or special characters are not allowed.
When identifiers are used in Transact-SQL statements, the identifiers
that do not comply with these rules must be delimited by double
quotation marks or brackets.
---
so, why can I create a database with name "!nihao nihao" (it contradict
with item 1 and 4 above )?
ThanksBecause, as the paragraph immediately after #4 explains, using double
quotation marks is a way to 'override' the normal naming requirements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Benny" <wuyuebing@.gmail.com> wrote in message
news:1163388171.249209.74300@.b28g2000cwb.googlegroups.com...
> in the MSDN
> http://msdn2.microsoft.com/en-us/library/ms175874.aspx
> it says that
> --
> The rules for the format of regular identifiers depend on the database
> compatibility level. This level can be set by using sp_dbcmptlevel.
> When the compatibility level is 90, the following rules apply:
> 1. The first character must be one of the following:
> * A letter as defined by the Unicode Standard 3.2. The
> Unicode definition of letters includes Latin characters from a through
> z, from A through Z, and also letter characters from other languages.
> * The underscore (_), at sign (@.), or number sign (#).
> Certain symbols at the beginning of an identifier have
> special meaning in SQL Server. A regular identifier that starts with
> the at sign always denotes a local variable or parameter and cannot be
> used as the name of any other type of object. An identifier that starts
> with a number sign denotes a temporary table or procedure. An
> identifier that starts with double number signs (##) denotes a global
> temporary object. Although the number sign or double number sign
> characters can be used to begin the names of other types of objects, we
> do not recommend this practice.
> Some Transact-SQL functions have names that start with
> double at signs (@.@.). To avoid confusion with these functions, you
> should not use names that start with @.@..
> 2. Subsequent characters can include the following:
> * Letters as defined in the Unicode Standard 3.2.
> * Decimal numbers from either Basic Latin or other national
> scripts.
> * The at sign, dollar sign ($), number sign, or underscore.
> 3. The identifier must not be a Transact-SQL reserved word. SQL
> Server reserves both the uppercase and lowercase versions of reserved
> words.
> 4. Embedded spaces or special characters are not allowed.
> When identifiers are used in Transact-SQL statements, the identifiers
> that do not comply with these rules must be delimited by double
> quotation marks or brackets.
> ---
> so, why can I create a database with name "!nihao nihao" (it contradict
> with item 1 and 4 above )?
> Thanks
>

About Function:GETDATE()

Hello,
I useSELECT CONVERT(VARCHAR(30),GETDATE(),105)
and get the now data
25-04-2006,
but if i want to get the data format is 2006-04-25
How?
Thank you!Hi

There is a more efficient way of stripping the time portion off however it doesn't quite match your format. Second function is perhaps more what you are after.
SElECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

SELECT CONVERT(CHAR(10),GETDATE(),120)

HTH|||the result is right! but

SELECT CONVERT(CHAR(10),GETDATE(),120)
is it insecurely?|||Um... I don't know. What do you mean by "insecurely"?

BTW - you can look up the constants in the BOL entry for CAST and CONVERT too.|||ok,thank you!

About DateTime in SQL

Hi,
I am making a report in SQL Reporting with the following format.
Does any have any idea how can I get date in this format "Monday, March 26, 2007" and how can I get time of each tranction in Reporting.I tried Formatdatetime function but it doesn't work..Plzz help me Thanks

Monday, March 26, 2007
10am 100
10am 110
1pm 100
Total: 310
Tuesday,March 27,2007
11am 500
6 pm 500
Total: 1000
Grad Total 1310

Quote:

Originally Posted by ri58776

Hi,
I am making a report in SQL Reporting with the following format.
Does any have any idea how can I get date in this format "Monday, March 26, 2007" and how can I get time of each tranction in Reporting.I tried Formatdatetime function but it doesn't work..Plzz help me Thanks

Monday, March 26, 2007
10am 100
10am 110
1pm 100
Total: 310
Tuesday,March 27,2007
11am 500
6 pm 500
Total: 1000
Grad Total 1310

----
For Date format you can use....Example,

SELECT substring(CONVERT(CHAR(20),'March 26, 2007 05:32:08 PM',109),1,14)

For Time part... Example
SELECT CONVERT(CHAR(15),'05:32:08 PM',114)