How to check if a SQL script is compatible with sql server 2000

Question

I wrote a lot of scripts for SQL Server 2005, but now I have to rewrite those original scripts to make them work on SQL Server 2000.

I don't remember all the differences between 2005 and 2000. For instance, CTE was announced only in 2005 - and I must rewrite these queries to work on SQL Server 2000.

I need a way to check the syntax, while not having SQL Server 2000 at hand. Setting the compatibility level to 80 does not solve the issue - I get no warning, no errors.

Is there a tool to use for cheking the syntax of the scripts? Can it be done with SSMS 2008?

JotaBe · Accepted Answer · 2012-04-16 10:59:16Z

1

You need to open a connection to the SQL Server 2000 database where you would execute the commands.

Then you set the parse only in the connection and "execute" the command. It will not get exceuted, but, if there's any syntax problem, it will throw the corredponding errors.

It's important to do it in th e database where you'd exceute the commands, as they depend on the database objects (existing table names, for example).

Here you have the information about this SET option:

SET PARSEONLY

answered Apr 16, 2012 at 10:59

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vadim Loboda Over a year ago

Good option. But the problem is that SQL Server 2000 is not available. So having SQL Server 2008 R2, I need to check syntax without actual deploy.

JotaBe Over a year ago

You can install a SQL Server Express 2000 and use it for this prupose.

Aaron Bertrand Over a year ago

No such thing as SQL Server 2000 Express. Maybe you meant MSDE? bing.com/search?q=download+msde+2000 ... If you go this route, PLEASE make sure you apply Service Pack 4. The last thing you want is to open yourself up to the slammer worm.

JotaBe Over a year ago

Yes, you're completley right. I meant MSDE. I didn't remember the name (and architecture and installation) change at this moment. Besides there's now a new version available which includes fixes for slammer: microsoft.com/download/en/details.aspx?id=22661

Vadim Loboda Over a year ago

I'm a total loser. I have Win7 x64. MSDE is not compatible with this version of windows. :)

|

Collectives™ on Stack Overflow

How to check if a SQL script is compatible with sql server 2000

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related