Error on Migrating to V2000 SQL from V7

I have just upgraded to SQL 2000 as per the companies allowed versions and
when trying to run an Stored Procedure I am getting the following error.
Syntax error converting the varchar value '2165.05' to a column of data type
int.
The Stored Proc just selects values into a Table, the wierd thing is this
works on v7 (same data / schema everything, but not on 2000) I can code
around it with Convert etc but was wondering if its a common problem and if
there is anything more I should know.
Cheers
Stumpy
Message posted via http://www.droptable.com
Without seeing a repro, I can only guess. It could be that datatype precedence has changed. Say, for
instance, you have a column of datatype varchar and a variable of datatype nvarchar, and then do:
WHERE col = @.a
7.0 would convert the value side to the column sides datatype. But 2000 will go by the rules
specified in Books Online "Datatype Precedence", and above would result in converting the column
side to the value sides datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>
|||MS changed the rules for implicit date type converstion between V7 and 2000.
There is nothing you can do about it.
If you use a char data type in a context in which a numeric type is
required, the char value will be converted to int, unless the context
explicitly implies (sic!) that decimal or float is requied. Since 2165.05 is
not an int, but a decimal, the conversion will fail. Since you fail to
provide the SQL code, there's no chance of my giving more details.
Use CAST (val as decimal (18, 2)) [or whatever data type you want] to
resolve. Note: CONVERT is an MS-specific function, so CAST is preferable.
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message
news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data
> type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and
> if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>
|||Thanks all for your help, sorry I didn't post the DDL etc, the problem was
ownership of the Database in the end.I did a ChangeDBOwner on all the tables
and the problem went away.
Cheers for all the help..
Lee
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1