September 8, 2004 at 4:52 pm
We are currently running SQL Server 7.0, and are testing upgrade to SQL Server 2000. On SQL Server 7.0, we have many stored procedures with code similar to the following:
Create Procedure sp_TEST
(
@p_OrderNum int
)
AS
Select * From Table1 Where OrderNum = @p_OrderNum
In the sample above, the OrderNum field in Table1 is varchar(12). This works fine on SQL Server 7.0 - the server can handle the implicit data type conversion.
On SQL Server 2000, however, the same proc returns errors because there is non-integer data in the OrderNum field. A sample error message is "Syntax error converting the varchar value 'XYZ' to a column of data type int." Using the convert function to change the Stored Proc parameter to a varchar enables the procedure to function properly. However, we do not want to have to do that in thousands of stored procedures in our database.
Does anyone know of a way to make SQL Server 2000 behave like SQL Server 7.0 in this case without setting database compatibility mode back to 7.0?
Thanks for your help!
Ken
September 9, 2004 at 6:16 am
Ken,
I seem to remember we had something similar going from 6.5 to 2000.
In our case the solution was setting the code page to 1252, which is what I believe SQL Server pre-2000 uses.
I also remember that changing the code page was not trivial.
I know this is a bit vague but hopefully it will send you in the right direction!
Julian
September 9, 2004 at 9:38 am
Hi Ken,
This is one of the migration issues. In 7, a comparison between a column and a literal/variable of different types worked by implicitly converting the literal/variable to the type of the column, ie the column always took precedence. In 2000, the behaviour is different, and relies on precedence rules to enforce a hierarchy of data types.
This change in behaviour is documented in http://support.microsoft.com/?id=271566: "SQL Server Comparisons Between Columns and Constants with Different Data Types", but the short answer is that the implicit convert now converts the column to type int, which has the higher precedence.
If you change your SP to say "Select * From Table1 Where OrderNum = convert(varchar, @p_OrderNum)".
I'm not aware of any other option that avoids a code change, apart from changing the compatibility level, and that's generally considered to be undesirable.
Neil
September 9, 2004 at 9:55 am
Thank you for the link Neil. Looks like we will be adding converts to many stored procedures.
Take Care!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply