Geek Answers Handbook

Compatible with SqlServer 2000

The development environment db server is SqlServer 2005 (developer version)

Is there any way to make sure that my SQL queries will run in SqlServer 2000?

This database is configured for compatibility level "SQL Server 2000 (80)", but some queries that run without problems in the development system cannot be executed on the test server (SqlServer).

(It seems that the problems are in the subqueries)

+3
source share
3 answers

Compatibility levels are designed to work the other way around: for an older version of T-SQL to work unchanged in a newer version of SQL Server. Changes typically include T-SQL syntax and reserved words, and you can use SQL Server 2005 features such as INCLUDED columns in indexes in a database at compatibility level 80. However, you cannot use T-SQL 2005 features such as CROSS .

Your best option is to develop / test all your code on an instance of SQL Server 2000. Note that you can use 2005 Management Studio to connect to an instance of SQL Server 2000, so you don’t have to go back in terms of tools.

+2
source

Problem resolved:

In correlated subqueries, you must (in SQL2000) explicitly define an external field.

SQL2005:

  SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE COLLATERAL_LOAN = LOAN_NUMBER)

SQL2000:

  SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE COLLATERAL_LOAN = Loans.LOAN_NUMBER)
0
source

You must always explicitly define all fields, otherwise you will not get an error on error and write

SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE LOAN_NUMBER=Loans.LOAN_NUMBER)

If the Collaterals-table does not have a LOAN_NUMBER column, the Loans table is used instead.

0
source

More articles:


All Articles