Script wackiness

I have tried to script all the tables and stored procedures from several
SQL2005 dbs and I noticed that I get double-quotes for single quotes and a
lot of extra single quotes near the END statements -- making the whole thing
unusable. Anyone know what this is?Could you please post some code ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Here are some examples. What the sprocs do is irrelevant, they've been used
for a long time. But this is script output from 2005 (previously scripted in
from 2000).
*****************************************
CAST(IsNull(ME.ActualH,0) AS nvarchar(5))+'' ''+
CASE
WHEN ActualHFrac = 0 THEN ''''
WHEN ActualHFrac = 0.0625 THEN ''1/16''
...
/* note the double quotes for singles */
******************************************
...
COMMIT TRANSACTION
SELECT @.err = @.@.error
IF @.err <> 0
RETURN @.err
'
END
/* note the wacky little single quote before END, this does not show in 2000
output */
******************************************
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[sel_ContactBrandsCRM]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROCEDURE [dbo].[sel_ContactBrandsCRM]
@.ContactID int,
@.SaleID int
AS
...
/* note the single quote after the N on the EXEC line */
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168464966.601900.217650@.p59g2000hsd.googlegroups.com...
> Could you please post some code ?
> HTH, Jens K. Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
>|||Hi
you told that the scripts are unusable, if that means that the script
break on execution or creation you will have to use the SET
QUOtED_IDENTIFIERS OFF option .
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Script Syntax Errors

i have new server, win2003, sql2005. trying to restore a .bak file from
sql2000. when excuting the RESTORE DATABASE dbase1 from
disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
displayed.
Executing the query ...
Query (1, 9) Parser: The syntax for 'database' is incorrect.
Execution complete
any ideas?
thanks in advance.
paulWhere do you execute this restore command? The error seems like some client application error...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>i have new server, win2003, sql2005. trying to restore a .bak file from
> sql2000. when excuting the RESTORE DATABASE dbase1 from
> disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> displayed.
> Executing the query ...
> Query (1, 9) Parser: The syntax for 'database' is incorrect.
> Execution complete
> any ideas?
> thanks in advance.
> paul|||i'm in the server management studio, right click on the database, new query,
mdx.
thanks.
paul
"Tibor Karaszi" wrote:
> Where do you execute this restore command? The error seems like some client application error...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >i have new server, win2003, sql2005. trying to restore a .bak file from
> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> > displayed.
> >
> > Executing the query ...
> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> > Execution complete
> >
> > any ideas?
> > thanks in advance.
> > paul
>|||mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> i'm in the server management studio, right click on the database, new query,
> mdx.
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Where do you execute this restore command? The error seems like some client application error...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> > displayed.
>> >
>> > Executing the query ...
>> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> > Execution complete
>> >
>> > any ideas?
>> > thanks in advance.
>> > paul
>>|||oops, you are correct.
thanks.
when i try to connect to Database Engines a connection error is displayed;
name pipes provider error 40 and ms sql server error 53
"Tibor Karaszi" wrote:
> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> > i'm in the server management studio, right click on the database, new query,
> > mdx.
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Where do you execute this restore command? The error seems like some client application error...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> > displayed.
> >> >
> >> > Executing the query ...
> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> > Execution complete
> >> >
> >> > any ideas?
> >> > thanks in advance.
> >> > paul
> >>
> >>
>|||Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> oops, you are correct.
> thanks.
> when i try to connect to Database Engines a connection error is displayed;
> name pipes provider error 40 and ms sql server error 53
>
> "Tibor Karaszi" wrote:
>> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
>> command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> > i'm in the server management studio, right click on the database, new query,
>> > mdx.
>> > thanks.
>> > paul
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Where do you execute this restore command? The error seems like some client application
>> >> error...
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> > displayed.
>> >> >
>> >> > Executing the query ...
>> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> > Execution complete
>> >> >
>> >> > any ideas?
>> >> > thanks in advance.
>> >> > paul
>> >>
>> >>
>>|||in SQL Server Config Manager there are 3 items listed; Service, Network
Config, Client Config.
in Services all are running; Integration,Analysis,Reporting, and Browser
in Network Config there are no items listed
in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
VIA
thanks.
paul
"Tibor Karaszi" wrote:
> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> > oops, you are correct.
> > thanks.
> >
> > when i try to connect to Database Engines a connection error is displayed;
> > name pipes provider error 40 and ms sql server error 53
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> >> command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> > i'm in the server management studio, right click on the database, new query,
> >> > mdx.
> >> > thanks.
> >> > paul
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> error...
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> > displayed.
> >> >> >
> >> >> > Executing the query ...
> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> > Execution complete
> >> >> >
> >> >> > any ideas?
> >> >> > thanks in advance.
> >> >> > paul
> >> >>
> >> >>
> >>
> >>
>|||Seems you didn't install SQL Server:
> in Services all are running; Integration,Analysis,Reporting, and Browser
If you had installed SQL Server, you would have also a "SQL Server" service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> in SQL Server Config Manager there are 3 items listed; Service, Network
> Config, Client Config.
> in Services all are running; Integration,Analysis,Reporting, and Browser
> in Network Config there are no items listed
> in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> VIA
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
>> > oops, you are correct.
>> > thanks.
>> >
>> > when i try to connect to Database Engines a connection error is displayed;
>> > name pipes provider error 40 and ms sql server error 53
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
>> >> Server
>> >> command.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> >> > i'm in the server management studio, right click on the database, new query,
>> >> > mdx.
>> >> > thanks.
>> >> > paul
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Where do you execute this restore command? The error seems like some client application
>> >> >> error...
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> >> > displayed.
>> >> >> >
>> >> >> > Executing the query ...
>> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> >> > Execution complete
>> >> >> >
>> >> >> > any ideas?
>> >> >> > thanks in advance.
>> >> >> > paul
>> >> >>
>> >> >>
>> >>
>> >>
>>|||i'm an idiot.
thanks.
paul
"Tibor Karaszi" wrote:
> Seems you didn't install SQL Server:
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> If you had installed SQL Server, you would have also a "SQL Server" service.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> > in SQL Server Config Manager there are 3 items listed; Service, Network
> > Config, Client Config.
> >
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> > in Network Config there are no items listed
> > in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> > VIA
> >
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> >> > oops, you are correct.
> >> > thanks.
> >> >
> >> > when i try to connect to Database Engines a connection error is displayed;
> >> > name pipes provider error 40 and ms sql server error 53
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
> >> >> Server
> >> >> command.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> >> > i'm in the server management studio, right click on the database, new query,
> >> >> > mdx.
> >> >> > thanks.
> >> >> > paul
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> >> error...
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> >> > displayed.
> >> >> >> >
> >> >> >> > Executing the query ...
> >> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> >> > Execution complete
> >> >> >> >
> >> >> >> > any ideas?
> >> >> >> > thanks in advance.
> >> >> >> > paul
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Script DB Object Level Security with SQL2005

We regularly need to take production DB backups and restore them onto our
development SQL Server boxes for testing (SQL2005 SP2). Since DB level
security is much different between production and stage, we need to script
stage rights (server and object level) and refresh those rights onto the
restored production DB. I was able to do that with EM in SQL 2000, but am
unable to do it with 2005 (SP2). I have tried (I think) every possible
option in Management Studio under Tasks / Generate Scripts for the DB and
none generate SQL for object level security. Is this a "bug" with SQL2005
SP2? I believe we were able to do it with SP1, but I don't recall which
options we chose.
--
KevinKevinL (KevinL@.discussions.microsoft.com) writes:
> We regularly need to take production DB backups and restore them onto our
> development SQL Server boxes for testing (SQL2005 SP2). Since DB level
> security is much different between production and stage, we need to script
> stage rights (server and object level) and refresh those rights onto the
> restored production DB. I was able to do that with EM in SQL 2000, but am
> unable to do it with 2005 (SP2). I have tried (I think) every possible
> option in Management Studio under Tasks / Generate Scripts for the DB and
> none generate SQL for object level security. Is this a "bug" with SQL2005
> SP2? I believe we were able to do it with SP1, but I don't recall which
> options we chose.
There is a scripting option "Script Object-level Permissions". that you
can use when you use the Generate Scripts task.
Under Tools->Options there is a new page "Scripting" where you can set
options for when you script individual objects from Object Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
That appears to have resolved the problem (bug?) with scripting object level
permission, but role membership is still not scripted. Any idea why that
might be?|||KevinL (KevinL@.discussions.microsoft.com) writes:
> That appears to have resolved the problem (bug?) with scripting object
> level permission, but role membership is still not scripted. Any idea
> why that might be?
How do you script? When I script a database by right-clicking it, selecting
Tasks/Generate Scripts and include both Database Roles and Users I do get
role membership scripted at the end.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click Tasks,
then Generate Script. The DB I right clicked is highlighted, I click next.
I verified the Script Options to make sure Script Object-Level Permissions i
s
True (there is no Option that mentions Roles) and click next. On Object
Types window choices are Schema, Stored Procedures, Tables, User-defined dat
a
types, User-defined functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.
--
Kevin
"Erland Sommarskog" wrote:

> KevinL (KevinL@.discussions.microsoft.com) writes:
> How do you script? When I script a database by right-clicking it, selectin
g
> Tasks/Generate Scripts and include both Database Roles and Users I do get
> role membership scripted at the end.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||KevinL (KevinL@.discussions.microsoft.com) writes:
> I see no option to script roles. This is what I did:
> Open Management Studio, expand databases, right click the DB, click
> Tasks, then Generate Script. The DB I right clicked is highlighted, I
> click next. I verified the Script Options to make sure Script
> Object-Level Permissions is True (there is no Option that mentions
> Roles) and click next. On Object Types window choices are Schema,
> Stored Procedures, Tables, User-defined data types, User-defined
> functions and Users.
> No matter which option(s) I choose, and I've tried every combination I can
> think of, Role level permissions are not scripted.
If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.
You can script all role membership with this SELECT:
SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script databsae does not compatible with SQL2000

I've created a databse on SQL2005, and I did not use any new data type for columns. I script the database, and select SQL2000 for "Script for Server version", howvever, the script generated cannot be run on SQL2000, it can be only run on SQL2005.

The script generated by SQL2005 is not backward compatible? like SQL2000 can generated script compatible wtih SQL7.

CREATE TABLE [dbo].[Table1](
[Col1] [nvarchar](16) NOT NULL,
[Col2] [nvarchar](100) NOT NULL,
[Col3] [nvarchar](10) NULL,
[Col4] [datetime] NULL,
[Col5] [nvarchar](10) NULL,
[Col6] [datetime] NULL,
[Col7] [bit] NULL CONSTRAINT [DF_Table1_Col7] DEFAULT ((0)),
[Col8] [bit] NULL CONSTRAINT [DF_Table1_Col8] DEFAULT ((0)),
[Col9] [int] NULL CONSTRAINT [DF_Table1_Col9] DEFAULT ((0)),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Error detected on the line "IGNORE_DUP_KEY = OFF".

This is a known bug. From the feedback center:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a6510471-9c40-4184-9611-5656457864d8

Edit: I have tried it and it does seem to be fixed in the SP1 CTM

Louis

|||Generally, you should not rely on the scripting capabilities other than for ad-hoc stuff. It is better to maintain your scripts in a source code control system and use it for reference / maintenance. There are other problems with scripting other than syntax issue with the CONSTRAINT in your example. For example, resolving dependencies is not accurate since the engine itself doesn't guarantee it for all cases. There are also issues with expressions which are normalized / modified by the engine so if you try to compare the script generated from the engine with your source it will not match even though they produce identical results.|||I'm sorry, but I can't hold back. The condescension in your reply to this stranded user is almost palpable. These types of responses are absolutely maddening, especially when the bugs in the SQL Server 2005 tools are so *painfully* obvious upon even casual observation. People don't need lectures on why you shouldn't be using a feature YOU included in the server which doesn't work because YOU did incredibly inadequate testing before you shipped. Seriously, just about any script you create from SQL Mgmt Studio which you want to run on 2000 will not work because it uses sys.objects. That bug should have never been included in the release, and frankly is a sign of a real management problem inside your team which needs to be addressed.|||

I won't dispute the 2005 tools have errors comment, and neither did he (in fact I would sing in that chorus with you if need be). However, he post was not a "well, you shouldn't be using it anyway" comment, I don't think. He goes on to explain that some parts of scripting out objects is just really quite hard (like maintaining precedence of which script to run first.)

As a side note, the sys.objects issue has been changed in the CTP as well for the 2000 compatible scripting.

All he was trying to say was that the best way to use the tools and do development is to maintain scripts of your work, and not rely on the tools to script out a database. I doubt that he was suggesting to never use the scripting tools for any reason. (especially since he advocated it for ad-hoc stuff.) I know I use it quite often, if for no other reason than to post a table structure to a discussion. But for a production system you should generally should have scripts for all objects that were created outside of using the SSMS tools and then scripting the objects.

It is very common to include a mini-lecture with any post where someone sees that a person *might* be abusing a feature. I learned a ton from my early days in the newsgroups because a friendly user or two (and an unfriendly albeit, very intelligent, jerk) lectured me on how something should be done.

|||Thanks, ***. Fix your God damned product or don't ship it. SQL 2005 is ***.

Script a database fail

Dear experts,

I've a database in sql2005 and now I want to build a same one on another machine. I've searched thru google and told that i can use backup& restore or using script. I've tested using backup/restore and it works great now i want to give a test to script. I've script the database successfully to a sql script file, however, when i run it against the new database server, the new database was not created as expected. Could anybody explain why? Thanks in advance

Are you getting any error messages?

|||

How did you create script? Have you tried to create it with Database Publishing Wizard:http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
With this peace of software you can create script directly from Visual Studio and you can choose do you want only schema of your database, or do you want schema and data.