wayne on March 21, 2008
I manage several databases (both SQL 2005 and SQL 2000) in my daily work routine and I finally decided to figure out something that has been troubling me every now and then. If you have ever tried to script a SQL 2000 table object from the SQL 2005 interface, you'll run into issues. The script generated will fail to execute due to differences between the two versions accepted forms of SQL script. More specifically, you will see this error:
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
In this post, I will explain a little about why these differences occur and how to work around them.
The Problem
When you generate a Create Table script on a table object that contains a primary key field setting using the SQL Server 2005 Enterprise Management Studio and then try to execute the generated script against a SQL 2000 database, you will get the following error:
Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
When you script a table object using the SQL 2000 Enterprise Manager you will be dealing with the 'Generate SQL Scripts' dialog to do your scripting. In SQL 2005, you can simply navigate to the object, right-click and click 'Script Table As' -> Create To -> New Query Editor Window. While this is not the problem, it does product two different results as show below. While this may occur elsewhere, it happens in the following script due to the Primary Key. I have removed the unimportant lines in these scripts.
SQL 2000 Table Script
CREATE TABLE [dbo].[FtpTestTable-DeleteMe] (
[FtpId] [int] IDENTITY (1, 1) NOT NULL ,
[BusinessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FtpTestTable-DeleteMe] WITH NOCHECK ADD
CONSTRAINT [PK_FtpTestTable-DeleteMe] PRIMARY KEY CLUSTERED
(
[FtpId]
) ON [PRIMARY]
GO
SQL 2005 Table Script
CREATE TABLE [dbo].[FtpTestTable-DeleteMe](
[FtpId] [int] IDENTITY(1,1) NOT NULL,
[BusinessName] [varchar](50) NOT NULL,
CONSTRAINT [PK_FtpTestTable-DeleteMe] PRIMARY KEY CLUSTERED
(
[FtpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
You can see by looking at the two scripts why this may be happening. The SQL 2000 script first creates the table and then alters the table to add the primary key constraint whereas the SQL 2005 generated scripts creates a single CREATE TABLE script that attempts to do everything.
The Solution
Of course, for every problem we programmers face, there are a couple different approaches we can take to overcome the issue. The first and perhaps the most obvious would be to simply 'jimmy' the script around and then create the key yourself manually.
This may be fine for one or two tables. What if you needed to do this across hundreds of tables? The manual effort instantly becomes manual labor and a huge waste of your time. So what do you do?
NOTE! I don't see the Scripting section in the environment on one of my systems where I only have SQL2005 installed. I think perhaps that this is only available on systems where both 2000 and 2005 instances are installed perhaps. I'll check this out more and update this.
Thankfully, you still do not need to use the old SQL 2000 Enterprise Manager interface at all. There is a setting under the ‘Tools – Options’ dialog that can help us. On the Options dialog there is a 'Scripting' node that contains all the options for the scripting engine that are made available to you. One of those properties is 'Script for server version'.
The drop-down on my system contains two entries, SQL 2000 and SQL 2005. By changing this to SQL Server 2000, your script will be output in a format that SQL 2000 will accept when executed from the SQL 2005 Management Studio interface. Here is what the same table script looks like after changing this setting:
CREATE TABLE [dbo].[FtpTestTable-DeleteMe](
[FtpId] [int] IDENTITY(1,1) NOT NULL,
[BusinessName] [varchar](50) NOT NULL,
CONSTRAINT [PK_FtpTestTable-DeleteMe] PRIMARY KEY CLUSTERED
(
[FtpId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
You can see that it still generates a single statement to execute, but this one will actually work and not throw an error in your face.
Conclusion
There may be more going on here that I am not aware of, and there is most likely. This little problem has vexed me from time to time and I finally decided to figure it out. I hope that someone will find this useful.
If you happen to know more about the differences between the T-SQL versions or more specifics about this problem, please leave me a comment explaining them or a link to a page where more info on this can be acquired. All comments are appreciated and responded to, so feel free to comment all you like.
Related Links
http://support.microsoft.com/kb/894257
