Implicit conversion not working

Last Post 29 Jan 2009 03:16 AM by AlexB_SQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
AlexB_SQL
New Member
New Member

--
28 Jan 2009 10:14 AM
Hi,

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
WHERE col1=111111
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


SELECT PPDTLIQ1,*
FROM BMCPROPAUTORIZ..CPROP WITH (INDEX (CPROP_PK))
WHERE PPNRPROP=901032203

it works fine because crpop_pk is the primary key on ppnprop column



SELECT PPNRPROP
FROM BMCPROPAUTORIZ..CPROP
WHERE PPNRPROP=901032203

it works fine and explain plan uses cprop_in_20 index, that uses only PPAUTORIZ column


SELECT *
FROM BMCPROPAUTORIZ..CPROP WITH (INDEX (CPROP_IN_01))
WHERE PPNRPROP=901032203

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.


SELECT *
FROM BMCPROPAUTORIZ..CPROP
WHERE PPNRPROP=901032203


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.
SwePeso
New Member
New Member

--
29 Jan 2009 02:49 AM
Use VARCHAR instead?

SELECT PPDTLIQ1,*
FROM BMCPROPAUTORIZ..CPROP
WHERE PPNRPROP = '901032203'
AlexB_SQL
New Member
New Member

--
29 Jan 2009 03:16 AM
Yes, it works but I would like to know why implicit conversion works on a server and on the other one it doesn´t work. What causes this problem?
You are not authorized to post a reply.

Acceptable Use Policy