Scripting Options Not Working for Server Version = SQL Server 2000 - by Ansible

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


0
0
Sign in
to vote
ID 369609 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/24/2008 8:46:07 AM
Access Restriction Public

Description

Can someone please explain why the Scripting Options setting for "Server Version SQL Server 2000" is being ignored?  Thank you.

Note:  In BOL "Options (SQL Server Object Explorer/Scripting Page)" it says the scripting options are supposed to work for the Modify command (now called Design in SP2).

I updated my SQL Server 2005 to SP2 (running on XP Pro) so I would be able to set the Scripting Options.  I set the Script for Server Version option to SQL Server 2000.  I set the Script USE <database> option to True.  Then I right clicked a table name in a database that runs in SQL 2000 compatibility mode and picked Design (used to be called Modify).  I changed the width of a varchar column from 4 to 6 characters.  Then clicked the Generate Change Script button.

 
*  The resulting script does NOT include the USE <database> statement.  
*  The resulting script will not run in SQL Server 2000 on a Win 2000 server ( SQL Server 2000 Version 8.00.2039.    SQL - DMO - 8.00.02    ODBC 03.52.0000).  
*  It generates the exact same script as it does when the Script for Server Version option is set to SQL Server 2005.
*  It inserts a GO statement immediately after a BEGIN TRANSACTION statement which causes error on SQL 2000.


Here is the beginning of the script that is generated:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CLAIM_21526
 DROP CONSTRAINT FK_CLAIM_RO
GO
ALTER TABLE dbo.CLAIM_21526
 DROP CONSTRAINT FK_CLAIM_526_RO
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CLAIM_21526
 DROP CONSTRAINT FK_CLAIM_21526_ACNT_TYPE
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CLAIM_21526
 DROP CONSTRAINT FK_CLAIM_21526_GENDER_TYPE
GO
COMMIT

... etc.


Here are the errors when run in SQL 2000


Msg 3728, Level 16, State 1, Line 1
'FK_CLAIM_RO' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Msg 3728, Level 16, State 1, Line 1
'FK_CLAIM_526_RO' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

... etc.


Further down in the script, it generates this code:

ALTER TABLE dbo.CLAIM_21526 ADD CONSTRAINT
 PK_CLAIM PRIMARY KEY NONCLUSTERED 
 (
 claim_id
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


If I leave the "WITH (STATISTICS... " line in, I get this error:  Line 5 - incorrect syntax near '('


I have 17 tables that need to have these minor column width changes made.  How can I get SQL 2005 to made a correct Change Script for SQL 2000?
Sign in to post a comment.
Posted by Microsoft on 11/13/2008 at 6:28 AM
Thanks for reporting the issue. Scripting options set in tools are for object explorer scripting and not change script. The change script generated will always be with respect to current server and is generated using capture sql mode which means “it is exact TSQL that would have executed if you had pressed ok for the change instead of scripting it”. This is by design.

Regards,
Alok