Just in case anyone "in the know" is still on this thread, can anyone explain this phenomenon in SQL Server 2000?
I looked for an explanation among the many (fascinating) comments on this thread, but didn't see this specifically addressed anywhere - I might well have missed it though.
The "simple" case fails in the way that was originally expected by the author, and in line with documented conversion rules:
Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
UNION Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A' to a column of data type int.
That is normal / expected SQL Server behaviour.
When you add an INSERT (to a table with appropriate type) in SQL 2000, the error goes away:
Create Table Test(col varchar(10))
GO
Insert into Test
Select 1
union Select 2
union Select 3
union Select 4
union Select 5
union Select 6
union Select 7
UNION Select 'A'
union Select 'B'
union Select 'C'
union Select 'D'
(11 row(s) affected)
Does anyone know why/how this happens? Is it a bug, or expected behaviour?
Sorry if this was already addressed, I would appreciate any comments/reminders pointing in the right direction.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.