Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
1fred
Posting Yak Master
158 Posts |
 Posted - 2007-05-02 : 10:57:48
|
I have 2 tables company table from different database. I did simplify the select with what is below. The filed co_id is a varchar(4) datatype. It used to contain 4 digits until recently they start adding letters in the co_id field of one of the 2 tables only. Now I get an error message when I join on the 2 tables. What is wrong with SQL server? Does he evaluate what is the data looking like and if he thinks that varchar value looks like an integer, he'll cast all the values to INT and then join the 2 tables?Explicitly casting my values as varchar on the join does solve my problem but I do not like that solution of casting varchar data as varchar datatype...So is there a configuration that I could either SET to ON or OFF when I start my Stored proc or I could ask my DBA to set it for everyone on the server.select * from company c inner company2 c2 on c2.co_id = c.co_idSyntax error converting the varchar value 'amp1' to a column of data type int. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-02 : 11:02:31
|
| Are you sure both table's CO_ID column is of the same data type i.e. varchar(4). Also you are missing JOIN keyword in the statement.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-02 : 11:04:40
|
Double check the data type for co_id in both of the table. One of them is int.
KH |
|
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2007-05-02 : 11:33:46
|
Both are varchar, could it be because I'm accessing my 2nd table from a link server? I found this very weird and it is the second time that such a thing happens to me in this new company I'm working for, I never had that bug before.The previous time it was something like this :select * from company c inner join department dept on c.code = left(dept.dept_id,4) c.code is a varchar(4) and the left function should return a string data type, but I had the same error.I did solve this issue by using the substring funcion instead of left .... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-02 : 11:44:38
|
| Is it a direct access to a link server or is there a view to the other server?If it is a view, look at the view creation code and make sure that every column is separated by a comma. As I ran into an issue where it created the view with all the columns, but a comma was missing and SQL picked INT for the data type of the column directly after the missing comma. |
|
|
|
|
|
|
Is SQL Server chaning data type on join?