Differences in ANSI SQL and MSDE Options

I have a database, and I want to run an update script on it. I
understand ANSI standard script. I've used Oracle 10g to learn
databases, and I've used C# for a while now with SQLCommand,
SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
say 2003 Compact...not sure). I have the script that attached this
database. It's biggest change in the actual running of script is
using GO, and it also doesn't have semi-colons at the end of
statements. Well, I need to know any other nuances like that when
using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
to use GO as I've seen people use it with plenty of commands and few
as well.
Another part that throws me for a loop (mostly cause I can't find
documentation or tutorials) is that it keeps using commands like "exec
sp_dboption," "exec sp_change_users_login," "exec
sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
TO." I'm more worried about how persistent these settings are than I
am anything else.
I just need to find a tutorial or some other form of help to figure
out what else needs to go into my .sql file that has all my DDL
statements. Any help would be appreciated. Any thoughts?
hi,
probably you will find better ANSI compliance if you move to SQLExpress, the
free edition of SQL Server 2005..
anyway..
Gold Panther wrote:
> I have a database, and I want to run an update script on it. I
> understand ANSI standard script. I've used Oracle 10g to learn
> databases, and I've used C# for a while now with SQLCommand,
> SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
> say 2003 Compact...not sure). I have the script that attached this
> database. It's biggest change in the actual running of script is
> using GO, and it also doesn't have semi-colons at the end of
> statements.
GO is not a SQL keyword.. it's just a batch terminator used in some
interactive tools like Enterprise Manager, Quary Analyzer, SQL Server
Management Studio, oSql.exe, SqlCMD.exe...
as these are the "official" tools provided by Microsoft, GO has become the
"standard" batch terminator in Microsoft SQL Server world...

>Well, I need to know any other nuances like that when
> using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
> to use GO as I've seen people use it with plenty of commands and few
> as well.
you can find lot of these in BooksOn Line, the official guide to SQL Server,
available for free downloadat:
SQL Server 2005 -
http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
SQL Server 2000 -
http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/docs/default.mspx
BTW, CREATE DOMAIN is not supported in Microsoft SQL Server..

> Another part that throws me for a loop (mostly cause I can't find
> documentation or tutorials) is that it keeps using commands like "exec
> sp_dboption," "exec sp_change_users_login," "exec
> sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
> TO."
these "kind of" statements are proprietary system stored procedures to
perform management tasks..
again, you can find them and their explanation in BOL..

> I'm more worried about how persistent these settings are than I
> am anything else.
eventually please review the "depracation" status of some
keywords/procedures/etc..
for instance, SQL Server 7.0 and 2000 used to attach databases via a system
stored procedure, sp_attach_db, now deprecated (in SQL Server 2005) in
favour of a proprietary extension of the CREATE DATABASE statement, CREATE
DATABASE ... FOR ATTACH, which perform the very same action, but can be
removed in future versions of SQL Server...
sp_adduser deprecated in favour of CREATE USER ... etc...

> I just need to find a tutorial or some other form of help to figure
> out what else needs to go into my .sql file that has all my DDL
> statements. Any help would be appreciated. Any thoughts?
you can have a look in BOL at the supported syntax of each DDL statement as
long as it's requirements..
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||First, make sure what version of SQL Server you're targeting. "SQL Server"
Compact Edition is not really SQL Server--it's SQL Mobile with a new name.
It does not share the same SQL engine as SQL Server.
If the purpose of this exercise is to move database schema from another
existing database I suggest using SQL Server Integration Services (SSIS) to
do the job. This utility can do everything that needs to be done without you
having to build and convert a bunch of scripts.
As you've been told, a SQL script (a set of SQL batch statements) is a file
that separates the individual batches that can't run sequentially with the
"GO" keyword. No, this is not TSQL or any SQL--it's used by all of the
Microsoft SQL utilities to help parse the batches. SQLCMD (or ISQL/OSQL, SQL
Server Management Studio or Visual Studio) all recognize this file syntax
for SQL scripts.
It usually takes more that a simple tutorial on scripts to get one's head
around the utilities used by SQL Server to configure a database, add users,
set the appropriate rights and everything else you seem to be
encountering...
I think my book might help...
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176491620.113814.27300@.d57g2000hsg.googlegro ups.com...
>I have a database, and I want to run an update script on it. I
> understand ANSI standard script. I've used Oracle 10g to learn
> databases, and I've used C# for a while now with SQLCommand,
> SQLConnection, etc. Now, I'm trying to update a SQL Server (I want to
> say 2003 Compact...not sure). I have the script that attached this
> database. It's biggest change in the actual running of script is
> using GO, and it also doesn't have semi-colons at the end of
> statements. Well, I need to know any other nuances like that when
> using CREATE DOMAIN, CREATE TABLE, and ALTER TABLE. I'm not sure when
> to use GO as I've seen people use it with plenty of commands and few
> as well.
> Another part that throws me for a loop (mostly cause I can't find
> documentation or tutorials) is that it keeps using commands like "exec
> sp_dboption," "exec sp_change_users_login," "exec
> sp_addsrvrolemember," "exec sp_addrolemember," "GRANT ... CREATE RULE
> TO." I'm more worried about how persistent these settings are than I
> am anything else.
> I just need to find a tutorial or some other form of help to figure
> out what else needs to go into my .sql file that has all my DDL
> statements. Any help would be appreciated. Any thoughts?
>
|||Thank both of you for the help. I believe you helped me find the
information I need. By the way, I started to buy your book Mr.
Vaughn, but I ran into multiple books of your 13 (I think that's how
many you said you have) that involve databases. I was not sure at the
time which to look into. If I look again today, I will probably be
able to determine that as it was Friday afternoon when I looked the
first time. Everybody gets a little drained by the end of the week.
Thank you both though.
|||Ah, yes. The most current, comprehensive and most complete is Hitchhiker's
Guide to Visual Studio and SQL Server (7th Edition). Let me know if it
helps--I'm convinced it will.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
------
Microsoft MVP, Author, Mentor
Microsoft MVP
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176730402.516417.311350@.w1g2000hsg.googlegro ups.com...
> Thank both of you for the help. I believe you helped me find the
> information I need. By the way, I started to buy your book Mr.
> Vaughn, but I ran into multiple books of your 13 (I think that's how
> many you said you have) that involve databases. I was not sure at the
> time which to look into. If I look again today, I will probably be
> able to determine that as it was Friday afternoon when I looked the
> first time. Everybody gets a little drained by the end of the week.
> Thank you both though.
>
|||William (Bill) Vaughn wrote:
> Ah, yes. The most current, comprehensive and most complete is
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). Let
> me know if it helps--I'm convinced it will.
>
I only have the 6th edition :D
great book..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||The 6th Edition was written before I left MS--almost a decade ago. A lot has
changed since then but a lot has really remained the same. The new book is a
total re-write from all of my books. All of the examples are new but many of
the best concepts are included and brought up to date. The 7th Edition is my
last book (on paper)--at least on technical subjects. I'm working on a
novel... ;)
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
------
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:58jhtvF2g5dshU1@.mid.individual.net...
> William (Bill) Vaughn wrote:
> I only have the 6th edition :D
> great book..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
|||> > William (Bill) Vaughn wrote:[vbcol=seagreen]
I will be testing that section once we get our test system in. If it
doesn't work the way I have it, I will definitely buy the book.
Thanks.
Off subject a little but what's the novel about?
|||It's about a clan of beings that live in the forest with a few magical
powers...
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Gold Panther" <lordbrucegiles@.yahoo.com> wrote in message
news:1176993337.593923.228560@.y80g2000hsf.googlegr oups.com...
> I will be testing that section once we get our test system in. If it
> doesn't work the way I have it, I will definitely buy the book.
> Thanks.
> Off subject a little but what's the novel about?
>
|||On Apr 19, 9:25 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.betav.com> wrote:
> It's about a clan of beings that live in the forest with a few magical
> powers...
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "Gold Panther" <lordbrucegi...@.yahoo.com> wrote in message
> news:1176993337.593923.228560@.y80g2000hsf.googlegr oups.com...
>
>
>
> - Show quoted text -
That sounds great! I might buy that when you're done too. : )