I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!
Peca, developer from Serbia
If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.
If possible, you should make changes where necessary.
I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.
/Kenneth
|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.
/Kenneth
|||All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm
|||Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!
|||>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").
Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.
|||Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.
So, this is not some new behaviour by Express.
Small demo below from SQL Server 2000: (this is the expected behaviour)
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go
pk a pk b
-- - - -
1 a 1 b
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.
/Kenneth
|||Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).
Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'
go
select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go
My example that does the same
table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int
they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)
query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107
query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...
values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow
query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)
Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.
Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?
|||Sorry KeWin, I wrote Kenneth by mistake :)|||:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)
Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.
select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11
..see if anything changes..?
/Kenneth
|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!
No comments:
Post a Comment