{"id":15,"date":"2009-05-27T13:23:00","date_gmt":"2009-05-27T07:53:00","guid":{"rendered":"http:\/\/guganeshan.com\/blog\/?p=15"},"modified":"2009-12-18T13:48:22","modified_gmt":"2009-12-18T08:18:22","slug":"scripts-generated-from-sql-server-2005-management-studio-are-incompatible-with-sql-server-2000","status":"publish","type":"post","link":"https:\/\/guganeshan.com\/blog\/scripts-generated-from-sql-server-2005-management-studio-are-incompatible-with-sql-server-2000.html","title":{"rendered":"Scripts generated from SQL Server 2005 Management Studio are incompatible with SQL Server 2000"},"content":{"rendered":"

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.<\/p>\n

Reproducing the problem:<\/span><\/span><\/strong><\/p>\n

Right-click on a table and generate a CREATE TABLE script from SQL Server 2005 Management Studio:<\/strong><\/p>\n

\"create<\/a><\/p>\n

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:<\/strong><\/p>\n

\"verify<\/a><\/p>\n

Query Analyzer would report syntax errors:<\/strong><\/p>\n

You will get a load of “Incorrect syntax near ‘(‘ ” or similar errors depending on the number of table queries you generated.<\/p>\n

\"QueryAnalyzerSynaxErrors\"<\/a><\/p>\n

<\/span><\/strong><\/p>\n

Solution for people who manage the target SQL Server 2000 database using SQL Server 2005 Management Studio<\/span><\/span><\/strong><\/p>\n

I found this solution when I Googled: http:\/\/www.waynejohn.com\/post\/2008\/03\/21\/Scripting-a-SQL-2000-Database-from-SQL-2005.aspx<\/a><\/p>\n

But note that Wayne John, the author of the blog says he couldn’t find the “Scripting” section in the “Tools –> Options<\/strong>” dialog box in one of his systems, where he had only SQL 2005 installed!<\/p>\n

The solution for people who don’t have SQL Server Management Studio installed at the target to manage the SQL Server 2000<\/span><\/span><\/strong><\/p>\n

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<\/strong><\/a> <\/strong>to export schema.<\/p>\n

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!).<\/p>\n

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.<\/p>\n

\"script<\/a><\/p>\n

NOTE: Double-check the “Drop existing objects in script” option to make sure it has “false” (unless you really want to drop objects)<\/span><\/p>\n

There are plenty of other tools out there too, for example from Red Gate Software<\/a>. But as a free piece software SQL Server Database Publishing Wizard<\/a> offers enough for my needs of exporting schema and data from\/to SQL Server 2005 and SQL Server 2000 databases.<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[16,68],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-troubleshooting","tag-sql-server","tag-troubleshooting"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":4,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":78,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/posts\/15\/revisions\/78"}],"wp:attachment":[{"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/guganeshan.com\/blog\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}