April 7, 2005 at 12:02 pm
Do any one know what is the difference between following two syntaxes. The first one is running ok but other one is throwing an error.
SELECT ddseqno,transno,procdat,glacnt,depodat,prseqno,voiddat,postqtr,postyr,voidqtr,voidyr,adduser,adddate,addtime,lckstat,lckuser,lckdate,lcktime,bankrec,pmvoid,pmexport,id_col FROM PRHDDT
UNION SELECT ddseqno,transno,procdat,glacnt,depodat,prseqno,voiddat,postqtr,postyr,voidqtr,voidyr,adduser,adddate,addtime,lckstat,lckuser,lckdate,lcktime,bankrec,pmvoid,pmexport,id_col FROM PRYHDD
select * from prhddt union select * from pryhdd
On First Syntax I include every field from table.
The Results are as follows:
(18253 row(s) affected)
Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.
Is it a bug in SQL server or Am I doing wrong?
I am using Sql server 2000.
April 7, 2005 at 12:23 pm
Is there a difference in the locations of the fields between the two tables? (i.e., does one table have a date in a location and the other table has a varchar in that location of its table?).
I wasn't born stupid - I had to study.
April 7, 2005 at 2:01 pm
Tables are truly identical. I check them may be 100 times since morning but couldn't find any difference. One is current table and other one is history table.
April 7, 2005 at 2:12 pm
What is the result of this query on the system tables:
Select * From syscolumns c1
Where id = object_id('prhddt')
And Exists (
Select 1
From syscolumns c2
Where c2.id = object_id('pryhdd')
And c2.colorder = c1.colorder
And (c2.name <> c1.name
or c2.xusertype <> c1.xusertype
or c2.length <> c1.length
or c2.isnullable <> c1.isnullable)
)
April 7, 2005 at 2:26 pm
I appoligize for my last post. I generated the script for both tables and found that there is one difference. lckuser and lckdate are interchanged in tables. I changed the position and now it is working. Thanks for the help.
April 8, 2005 at 7:08 am
This is espicially why you should always list the columns one by one in the statements. If this is production code and that the column order rechanges for any reason, you'll be back in the same situation.
April 8, 2005 at 1:11 pm
I agree with Remi - it is better to have a target list rather than a * - you know what you have.
Quand on parle du loup, on en voit la queue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply