Andre's Blog

Personal blog of Andre Perusse

SQL Server 2005 Syntax Incompatible with SQL Server 2000

On a recent project we use SQL Server 2005 for development but the product officially supports installation on both SQL Server 2005 and SQL Server 2000. During development we'll create tables in the database (using SQL 2005) using the GUI tools in either Visual Studio or Management Studio. When it comes time to create the installation scripts, we'll "Generate CREATE scripts" from these GUI tools. With SQL 2005 (well, at least the version we're using, which is SP2), the CREATE TABLE script will now use a SQL 2005 specific syntax that will not work on SQL 2000.
 
For example, here is an auto-generated script that runs on SQL 2005, but not on SQL 2000:
 
CREATE TABLE [dbo].[Order](
 [orderID] [int] NOT NULL,
 [customerID] [int] NOT NULL,
 [orderDate] [datetime] NOT NULL,
 [shipDate] [datetime] NOT NULL,
 [shipperID] [int] NOT NULL,
 [shipperTrackingNumber] [varchar](50) NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
 [orderID] 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
 
If you try to run this on SQL 2000, you'll get the following error:
 
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '('.
 
It would appear as though SQL 2000 does not like the syntax of the primary key constraint included in the CREATE TABLE statement. Alternatively, the following script works on both SQL 2000 and SQL 2005:
 
CREATE TABLE [dbo].[Order] (
 [orderID] [int] NOT NULL ,
 [customerID] [int] NOT NULL ,
 [orderDate] [datetime] NOT NULL ,
 [shipDate] [datetime] NOT NULL ,
 [shipperID] [int] NOT NULL ,
 [shipperTrackingNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Order] ADD
 CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED
 (
  [orderID]
 )  ON [PRIMARY]
GO
 
Now, if that was the only problem I could probably live with that. But wait! There's more! When you create an object in SQL Server, it's generally good practice to first make sure the object doesn't already exist. In my day-to-day use, my scripts will often check for general objects, or foreign-key constraints. SQL 2005 uses the following code for these operations:
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Order_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
 
Run this on SQL 2000 and you'll get:
 
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.foreign_keys'.
 
Also, the following code is used by SQL 2005:
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO
 
which will give you the following on SQL 2000:
 
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
 
Microsoft changed the way that meta-data is stored in SQL 2005 to improve security, amongst other things, but this means that these scripts won't work on SQL 2000. Thankfully, however, they did provide "views" in SQL Server 2005 which mimic the old behavior on SQL 2000. To fix these errors on SQL 2000, you can use the following syntax which will work on both SQL 2005 and SQL 2000:
 
IF  EXISTS (SELECT * FROM dbo.sysforeignkeys WHERE fkeyid = OBJECT_ID(N'[dbo].[FK_Order_Customer]') AND rkeyid = OBJECT_ID(N'[dbo].[Order]'))
ALTER TABLE [dbo].[Order] DROP CONSTRAINT [FK_Order_Customer]
GO
 
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO
 
Thankfully, there is a way around this if you're using SQL Management Studio. Instead of right-clicking on a table to generate a CREATE script, right-click on the Database and select Tasks -> Generate Scripts. This will open the Script Wizard dialog. On the Choose Script Options page, set the "Script for Server Version" property to "SQL Server 2000" and your CREATE scripts will now be fully compatible. A little more clicking is required, but at least your scripts will work on both server versions.
 

Comments (11) -

  • Darth Mac

    9/26/2007 8:29:08 PM |

    Yeah, yeah. That's all nice, but what do you think of Halo 3?

  • Andre Perusse

    10/4/2007 8:54:26 AM |

    Halo 3 rocks! Check out my next blog post.  Smile

  • Darth Mac

    10/4/2007 8:26:08 PM |

    You think so? I thought it was more like a Halo 2 map pack. I just wasn't blown away. I also HATE the new controller layout and the fact you can't set your own button combinations....

  • Murman

    10/14/2007 9:32:07 AM |

    Darth Mac is set in his ways, although when he DOES change controller layout, it only gets worse

  • Huong

    3/24/2008 5:39:54 AM |

    I meet this situation like you. I also use Generate script for SQL SERVER 2000 version, i don't receive result that i want. after generation, i have scripts that doesn't work on sql server 2000. Do you know any tools that can generate scripts for sql server 2000 version from database on sql server 2005.

  • Andre Perusse

    4/6/2008 1:43:58 PM |

    Hi Huong,

    I know of no free tools that will do this, but you can check out Red Gate's lineup of commercial tools - I'm sure they have something that will do what you want.

  • Zhen

    4/15/2008 11:51:11 AM |

    I tried

    "set the "Script for Server Version" property to "SQL Server 2000"

    but the generated script is still the same like below. Problem not solved and the syntax is not acceptible in SQL 2000.

    CREATE TABLE [dbo].[tblZYTest](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nchar](10) NULL,
    CONSTRAINT [PK_tblZYTest] PRIMARY KEY CLUSTERED
    (
      [id] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END

  • eric

    9/15/2008 5:27:48 PM |

    Andre,
    I tried your method by setting the "Script for Server Version" to SQL Server 2000, but when I run the script on a SQL Server 2000 machine, I get the same syntax error "Line 11: Incorrect syntax near '('."

    I found that taking the first CREATE TABLE statement you have at the top and removing "WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" from it, it will work on 2000.

  • Topestguy

    2/27/2009 12:52:38 AM |

    Thanks for the article..it helped me

  • MikeyDesa

    3/12/2009 11:27:20 PM |

    Tried the script on SQL2005, the same error occured.

    Incorrect syntax near '('.

  • Karl Hoaglund

    5/1/2013 12:05:22 PM |

    Thanks so much! This was a big help.

Loading