I have the same DB on two different SQL 2k(SP4 isntalled) servers and in these DB thre is a table with a pk on col1, using varchar9) data type.
When I run a query like this one below
on server A, it does not make an implicit conversion from int to varchar9 and generates following error message:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '000******' to a column of data type int
when I run same query on another server, it works fine!!!
I found out that it depends on the index that is used to find rows that influence error occurence.
Here are some tests with the real table that I made with different indexes
FROM BMCPROPAUTORIZ..CPROP WITH (INDEX (CPROP_PK))
it works fine because crpop_pk is the primary key on ppnprop column
it works fine and explain plan uses cprop_in_20 index, that uses only PPAUTORIZ column
FROM BMCPROPAUTORIZ..CPROP WITH (INDEX (CPROP_IN_01))
it gives Server: Msg 245, Level 16, State 1, Line 1 error message. What is interesting is that when I run this query without index hint on the other server, it works fine and explain plan uses CPROP_IN_01, wich uses only PPCONTROLE column.
it gives Server: Msg 245, Level 16, State 1, Line 1 error message and explain plan also tries to use CPROP_IN_01 index, that uses PPCONTROLE column.
I also discovered that it uses CPROP_IN_01 or CPROP_IN_20 because coluns used on these indexes are all NULL, so, cost of reading it is far less than cost of reading full clustered index of the PK, since it would have to read the whole table to read whole PK.
Does anyone knows what could be hapenning with this implicit conversion? Before someone talk about data type precedence to converts, I know that and on "server B" the implicit convertion from int to varchar9 works fine, so, precedence has no influence on that.