sqlpubwiz Command Line Issue.
-
I didn't know whether to reply to my original question or post new - but as the original was asked (accidentally) under a different user name, I though I'd start a new thread. Apologies to anyone offended by that. (Original Here)[^] I have sqlpubwiz 1.3 and it fails to script correctly on SQL Server 2008 if there are any foreign keys (or other constraints). I'm kind of assuming this is something I am doing - as it's a pretty useless tool otherwise - I have test db with two tables
CREATE TABLE [dbo].[Table_1](
[id] [int] NOT NULL,
[num] [int] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[Table_2](
[id] [int] NOT NULL,
[num] [int] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([id])
REFERENCES [dbo].[Table_1] ([id])
GOALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GOThen I (try to) script it...
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2> sqlpubwiz script -d myDatabase -S localhost\MYDATABASE -f .\test.txt
and get the error...
Microsoft (R) SQL Server Database Publishing Wizard
Version 10.0.0.0
Copyright (c) Microsoft Corporation. All rights reserved.Generating script for database stit1
- Generating script targeted for SQL Server 2005
Gathering list of objects to script
Scripting objects
.....Error: Operation is not valid due to the current state of the object.
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2>
Delete the foreign key and all is well. I'd really appreciate it if someone could either confirm or deny the fact that this is 'just me' or, in fact, sqlpubwiz is a pile of donkey doo doo
___________________________________________ .\\axxx (That's an 'M')
- Generating script targeted for SQL Server 2005
-
I didn't know whether to reply to my original question or post new - but as the original was asked (accidentally) under a different user name, I though I'd start a new thread. Apologies to anyone offended by that. (Original Here)[^] I have sqlpubwiz 1.3 and it fails to script correctly on SQL Server 2008 if there are any foreign keys (or other constraints). I'm kind of assuming this is something I am doing - as it's a pretty useless tool otherwise - I have test db with two tables
CREATE TABLE [dbo].[Table_1](
[id] [int] NOT NULL,
[num] [int] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[Table_2](
[id] [int] NOT NULL,
[num] [int] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([id])
REFERENCES [dbo].[Table_1] ([id])
GOALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GOThen I (try to) script it...
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2> sqlpubwiz script -d myDatabase -S localhost\MYDATABASE -f .\test.txt
and get the error...
Microsoft (R) SQL Server Database Publishing Wizard
Version 10.0.0.0
Copyright (c) Microsoft Corporation. All rights reserved.Generating script for database stit1
- Generating script targeted for SQL Server 2005
Gathering list of objects to script
Scripting objects
.....Error: Operation is not valid due to the current state of the object.
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2>
Delete the foreign key and all is well. I'd really appreciate it if someone could either confirm or deny the fact that this is 'just me' or, in fact, sqlpubwiz is a pile of donkey doo doo
___________________________________________ .\\axxx (That's an 'M')
I'm able to use the publishing wizard command line to script a database with foreign keys. I was getting the same error but then I found this post: http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/ecfd4379-ee88-4a60-81a4-d6cc9542b1ff[^] which said to make sure that the case of the database name has the correct case. When I fixed that the error went away.
- Generating script targeted for SQL Server 2005