SQL 6.5 to SQL 2000 Convert function

SQL 6.5 to SQL 2000 Convert function

Post by TXVubn » Thu, 20 May 2004 21:51:10


I have upgraded my database from SQL 6.5 to SQL 2000. Now one of the SQL proc is failed to execute as there is following select stmt

SELECT CONVERT(INT(2),COUNT(*)
FROM tablenam
WHERE conditio
this stmt execute fine on SQL 6.5 but not on SQL 2000. It generates following erro
Server: Msg 291, Level 16, State 1, Line
CAST or CONVERT: invalid attributes specified for type 'int

NOw I am sure because numeric to int is implicit conversion in SQL 2000 and if I use following code works fine on SQL 200
SELECT CONVERT(INT,COUNT(*)
FROM tablenam
WHERE conditio

<B>But Can pass anyother parameter or change setting that allow me to execute
SELECT CONVERT(INT(2),COUNT(*)
FROM tablenam
WHERE conditio
stmt in SQL 2000 without changing code? </B

Please help.
 
 
 

SQL 6.5 to SQL 2000 Convert function

Post by Erland Som » Tue, 25 May 2004 07:37:45

Munnu ( XXXX@XXXXX.COM ) writes:

No.

SQL Server offers the possibility to set a database in compatibility
mode, so that old syntax still works. However, your particular example
is not covered by the compatibility 65. (I just tried.) I have never
seen int(2) before, and I don't think this was ever documented or
supported in SQL6.5, it just happened to work.

To change the compatibility level of a database, you use sp_dbcmptlevel,
look in Books Online for details. However, I recommend that you fix
all syntax errors, so that you can use compatibility level 80 and use
SQL2000 at full strength.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.yqcomputer.com/