If you ever generated scripts using SQL Server 2005 using Management Studio by right-clicking on table names, and tried executing it in SQL Server 2000 (using Query Analyzer), you would have encountered this problem… The Query Analyzer would report syntax errors.
Reproducing the problem:
Right-click on a table and generate a CREATE TABLE script from SQL Server 2005 Management Studio:
Paste the generated script in SQL Server 2000, Query Analyzer and verify the syntax by clicking on the “Parse Query” tool button or press CTRL + F5:
Query Analyzer would report syntax errors:
You will get a load of “Incorrect syntax near ‘(‘ ” or similar errors depending on the number of table queries you generated.
Solution for people who manage the target SQL Server 2000 database using SQL Server 2005 Management Studio
I found this solution when I Googled: http://www.waynejohn.com/post/2008/03/21/Scripting-a-SQL-2000-Database-from-SQL-2005.aspx
But note that Wayne John, the author of the blog says he couldn’t find the “Scripting” section in the “Tools –> Options” dialog box in one of his systems, where he had only SQL 2005 installed!
The solution for people who don’t have SQL Server Management Studio installed at the target to manage the SQL Server 2000
So If you are like me… using SQL Server 2000 + Query Analyzer at the target server while the source is a SQL Server 2005 + Management Studio, you can use the good old SQL Server Database Publishing Wizard to export schema.
The queries generated using the SQL Server Database Publishing Wizard worked perfectly with SQL Server 2000’s Query Analyzer (though a query for even a single table would look scary!).
But make sure you select “SQL Server 2000” as the value for the “Script for target database” property in the “Select Publishing Options” in the wizard.
NOTE: Double-check the “Drop existing objects in script” option to make sure it has “false” (unless you really want to drop objects)
There are plenty of other tools out there too, for example from Red Gate Software. But as a free piece software SQL Server Database Publishing Wizard offers enough for my needs of exporting schema and data from/to SQL Server 2005 and SQL Server 2000 databases.