getting error in SQL Server 2000 but not in SqlServer 2005

Question

Hi,

I am executing a stored procedure and i got an error saying 'Error SQL Server Database Error: Error converting data type varchar to int." in sqlserver 2000. But when I execute the same stored proc in Sql server 2005,i am not getting any error and result is getting displayed.

Any one is having idea regarding this??Please do help me.

Thanks Rupa

Answer 1

this could be data dependant and not a 2000 vs 2005 issue. It is most likely where the rows the query is working on are different in both databases.

start to comment out item from the query that failes, and run it each time, until it works. the last thing you commented out is where the problem is happening. then look at the tata related to that part.

Answer 2

It might dependt on the executionplan the stored procedure uses

An example

The table Mytable looks like this

     Mytable    
    __________
    |Val|type |
    -----------
    |'1'|'num'|
    |'2'|'num'|
    |'a'|'chr'|
    |'b'|'chr'|
    |'8'|'num'|

My select looks like this

select convert(int,Val) from Mytable where type = 'num'

This might fail depending on the executionplan. You can actually have a stored procedure that works fine for a while but suddenly fails just because of the amount of rows int your table have changed and the sqlserver has created a new executionplan

Here another example: http://www.windows-tech.info/15/a0fc276997660092.php