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.
