SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4115 Visits: 2613
serinor.e090266 (3/27/2009)
I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005

More information:
1)
Output for SELECT @@VERSION

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2) See the attached document.


This server is 2005 or not?



It's a SQL 2000 server you are connecting to, you're just using the 2005 client tool to do so. This is perfectly legal and shouldn't affect (or is it effect) your results...it will still behave like a SQL 2000 server.

Generally speaking, Enterprise manager or SSMS can connect to anything earlier than it from what I've seen.

The Redneck DBA
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4228 Visits: 2635
serinor.e090266 (3/27/2009)
I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005

More information:
1)
Output for SELECT @@VERSION

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2) See the attached document.


This server is 2005 or not?


Nope that's definitely SQL 2000, SP4!


Kev
i2lovefishing
i2lovefishing
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 137
Even with newer version of SQL being stricter on mixing "union" statements such as
select 7 union select 'A', the original explation is not adequite to say the least.
If that is all the problem, then there is no need to have the follow-up select statements.

The fact is, if you work around the new "union" feature/bug, by breaking the insertion to 2 parts - select 1 union 2... and select "A" union select "B", you have all items inserted into the table. Then, you still likely get an error running the follow-up select statements - I did.

Regardless the select SQL executed with error or without error, the explanation is based on how SQL interprates those select SQL statements - that clearly is not universally the same as we can be seen by different posts.

In my case, SQL is doing a "implicit conversion" on Col to compare with 1 and 6, therefor it fails for values such as "A".

If I change the filter condition from Between 1 and 6 -> Between '1' and '6', it runs with out errors.
Vijaya Kadiyala
Vijaya Kadiyala
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2935 Visits: 409
My Asnwer is perfectly valid i.e will display 1..6.. if we execute this query in 2000.

We should have this question specific to the SQL Server version.

Thanks -- Vijaya Kadiyala
www.dotnetvj.vom

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Mauve
Mauve
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3200 Visits: 2065
Vijaya Kadiyala (4/1/2009)
My Asnwer is perfectly valid i.e will display 1..6.. if we execute this query in 2000.

We should have this question specific to the SQL Server version.

Thanks -- Vijaya Kadiyala
www.dotnetvj.vom

It was specific to a particular SQL Server version. You didn't read the question carefully.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: Administrators
Points: 140873 Visits: 19415
The question was edited to note 2005.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLEnthusiastic
SQLEnthusiastic
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 104
I tried in SQL 2005. It worked fine.

While inserting, it perform implicit conversion of numerals into varchar.

But if SELECTed, it does not convert it.
Jochen Vleming
Jochen Vleming
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 69
Julie Breutzmann (3/26/2009)
While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.


I totally agree with Julie on this.
I'd rather learn a valuable lesson without getting points than learning nothing while getting thousands of points. w00t

Jochen
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 Visits: 1249
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.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33526 Visits: 9518
I believe that this happens in SQL 2000 because the compile "reads-ahead" or anticipates the datatype expected by the table column and then back applies it to the source expressions. Implementing something like this is highly dependent on the internals of the compiler which was completely rewritten in 2005.

So my guess is that that obscure feature was dropped as part of the rewrite. (These features can have some problematic side-effects too, though I cannot remember them at the moment).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search