0

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

Improve this question
3
  • Are the data and/or parameters that you're using exactly the same in both databases? Commented Sep 1, 2009 at 12:00
  • yes..i have executed the same procedure in both sqlserver 2000 and 2005. Commented Sep 1, 2009 at 12:04
  • Can you trace out the convert? Commented Sep 1, 2009 at 12:07

2 Answers 2

Reset to default
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.

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

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.