March 26, 2009 at 10:31 am
In 2005 if I'm comparing those instances where it works and those where it doesn't... I'd be interested to see how/whether the execution plans differ.
March 26, 2009 at 10:45 am
Ben Leighton (3/26/2009)
In 2005 if I'm comparing those instances where it works and those where it doesn't... I'd be interested to see how/whether the execution plans differ.
I'd bet they don't differ at all - this seems more a difference in compiling/parsing than execution
Sorry I've not enough different instances to try on!
Kev
March 26, 2009 at 10:46 am
Version I ran on:
2005 Standard Edition29.00.1399.06RTM
Gives both a result set of 1 to 6 AND the error that was noted.
March 26, 2009 at 10:49 am
I've tested this in build 9.00.3077. That's my test instance.
I return an error in SSWK5, AdventureWorks, compat mode 90. No rows inserted.
I can't necessarily verify on every version. If you have a different version, and it returns different things, I can award points, but I'm not going to test every version. Note the differences here in the discussion and that helps people learn and understand issues.
I have edited the explanation to get the precedence link from Mr. Young in there.
March 26, 2009 at 10:53 am
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A' to data type int.
But in 2000 it give result 1,2,3,4,5,6.
SQL DBA.
March 26, 2009 at 11:46 am
On my SQL 2000 server (using Enterprise Manager 2005), I get 1,2,3,4,5,6 and no error message when using the original question code. Sorry, not sure which build we are at - I'm a developer, not a DBA.
If I then run this code against the table:
Select * from Test
Where Col Between 1 and 6
I get the 1,2,3,4,5,6 result, with the additional error message tab.
If I just run "Select * from Test" I get all rows back from the table; no error messages.
On my SQL 2005 server (also using Enterprise Manager 2005), I just get the error, and no data is loaded to the table. I believe this server is still on SP1.
Fascinating to see the differences between versions, builds, service packs, and possibly local settings!
Oh yes, and I answered "wrong" because I cross-checked on SQL 2000 first, and it worked. If only I had checked on the 2005 server, I'd be 2 points richer! 😛
Steph Brown
March 26, 2009 at 2:58 pm
Jason Shadonix (3/26/2009)
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
Yes I am. And for both versions (the original and my "fix") I get the error message only. Furthermore, I do not see how you can possibly get both in SQL 2005(Level 90) unless you erroneously already have the data in the table before the INSERT command.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2009 at 3:02 pm
You know, correctly reformulated this could be one heck of a wicked question.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2009 at 3:03 pm
Be sure you have the original query in your SSMS, and then issue a "drop table test" before you run it.
I've tested on 2 2005 instances, both show the error, not insertion (select * from test afterwards) and no "results" tab.
March 26, 2009 at 3:04 pm
RBarryYoung (3/26/2009)
Jason Shadonix (3/26/2009)
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
Yes I am. And for both versions (the original and my "fix") I get the error message only. Furthermore, I do not see how you can possibly get both in SQL 2005 unless you erroneously already have the data in the table before the INSERT command.
I did not have the data in the table or even the table before first run. I got both tabs also... in 2005.
It may be possible that a formatting error ran the inner select independent of the insert... I have no other guesses.
March 26, 2009 at 3:09 pm
dphillips (3/26/2009)
RBarryYoung (3/26/2009)
Jason Shadonix (3/26/2009)
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
Yes I am. And for both versions (the original and my "fix") I get the error message only. Furthermore, I do not see how you can possibly get both in SQL 2005 unless you erroneously already have the data in the table before the INSERT command.
I did not have the data in the table or even the table before first run. I got both tabs also... in 2005.
It may be possible that a formatting error ran the inner select independent of the insert... I have no other guesses.
Have you checked the compatibility level?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2009 at 3:12 pm
RBarryYoung (3/26/2009)
You know, correctly reformulated this could be one heck of a wicked question.
Yes, despite the whining from the first couple of pages, it uncovered some interesting information.
March 26, 2009 at 3:16 pm
Just manually typed it all in exactly as displayed, same result... a result and a messages tab.
March 26, 2009 at 3:25 pm
RBarryYoung (3/26/2009)
dphillips (3/26/2009)
RBarryYoung (3/26/2009)
Jason Shadonix (3/26/2009)
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
Yes I am. And for both versions (the original and my "fix") I get the error message only. Furthermore, I do not see how you can possibly get both in SQL 2005 unless you erroneously already have the data in the table before the INSERT command.
I did not have the data in the table or even the table before first run. I got both tabs also... in 2005.
It may be possible that a formatting error ran the inner select independent of the insert... I have no other guesses.
Have you checked the compatibility level?
Score. 2 points. Thanks for asking... yes, the defaulted DB was in fact creating the table in database that was set to 2000 compatibility mode.
March 26, 2009 at 3:34 pm
Just switched databases to one with 2005 compatibility mode... no result set this time.
Got only the message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A' to data type int.
Interesting. Why, I don't know... but interesting none the less. Assumptions snagged me. 10 DBs on this 2005 installation, only one set to 2K compat... and it just happens to be the default one, as it was the first DB ported over... from 2K... go figure.
Viewing 15 posts - 46 through 60 (of 87 total)
You must be logged in to reply to this topic. Login to reply