InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:11:21 GMT20http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxgood explanation John....the error is coming due to incompatibility of datatypes in UNION....but if we do like this -DECLARE @id varchar(10)set @id = 'A'select isnumeric(@id) It always gives 1why?Fri, 15 Feb 2013 23:01:58 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxThis works Insert into test Select 'A' union Select 1;also Insert into test Select 1 union Select 'A'; worksbut only select statement not works.Select 1 union Select 'A';Give error :Syntax error converting the varchar value 'A' to a column of data type int.Fri, 21 Jan 2011 02:22:38 GMTNitu for uhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxHi thereI work in a SQL 2000 developer edition. when I run the provided script, it runs successfully returning rows 1 through 6.I did not dound any conversion error.AshokFri, 08 Jan 2010 04:42:45 GMTAshok Kumar-377014http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxWith SQL Server 2005, I am able to create the table with no error, so the answer is wrong.However, when subsequently I query the table, the conversion error appears.1 "SELECT col FROM test" succeeds => 1234567ABCD2 "SELECT col FROM test WHERE IsNumeric(col)=1" succeeds => 12345673 The query given fails with the conversion errorFri, 18 Dec 2009 02:03:33 GMTJon Summershttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxMy take on ths is that SQL 2008 has introduced a serious defect! Changing to behave in this irrational manner has probably broken rather a lot of SQL. And since the behaviour in SQL 2008 is different from the behaviour in all previous versions, it would have been useful (to say the least) if the question had specified teh version.Tue, 23 Jun 2009 07:18:39 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]arconen (3/26/2009)[/b][hr]In future must show the version of MS SQL Server in order to prevent a misunderstanding.I answered right for version 2000, but for 2008 I made a mistake.[/quote]I absolutely agree with that. And, it brings up another huge problem... these type of changes to the database engine are complete and utter non-sense that break huge amounts of code. Such "improvements" to the engine cause more pain than the Y2K problem ever thought of causing.Thu, 16 Apr 2009 11:52:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]RBarryYoung (4/8/2009)[/b][hr][quote][b]Jochen Vleming (4/8/2009)[/b][hr][quote][b]Julie Breutzmann (3/26/2009)[/b][hr]While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.[/quote]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[/quote]Actually, you get far more points by participating in the discussion. That's one of the nice things about the point system on this site.[/quote]That's very true. Just look at the Top QotD points versus the Top Forum Posters points: [url=http://www.sqlservercentral.com/TotalScores]http://www.sqlservercentral.com/TotalScores[/url]Wed, 08 Apr 2009 08:02:20 GMTppcxhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxAs I said before, the script in questions works fine in 2000 and 2005 as well.Without the INSERT, if you only SELECT it doesn't. The SELECT with different datatypes, does not do an implicit conversion.Wed, 08 Apr 2009 07:25:36 GMTSQLEnthusiastichttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]Jochen Vleming (4/8/2009)[/b][hr][quote][b]Julie Breutzmann (3/26/2009)[/b][hr]While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.[/quote]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[/quote]Actually, you get far more points by participating in the discussion. That's one of the nice things about the point system on this site.Wed, 08 Apr 2009 06:14:32 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxI 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).Wed, 08 Apr 2009 06:12:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxJust 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:[code]Select 1union Select 2union Select 3union Select 4union Select 5union Select 6union Select 7UNION Select 'A'union Select 'B'union Select 'C'union Select 'D'[/code][quote]Server: Msg 245, Level 16, State 1, Line 1Syntax error converting the varchar value 'A' to a column of data type int.[/quote]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:[code]Create Table Test(col varchar(10))GOInsert into TestSelect 1union Select 2union Select 3union Select 4union Select 5union Select 6union Select 7UNION Select 'A'union Select 'B'union Select 'C'union Select 'D'[/code][quote](11 row(s) affected)[/quote]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.Wed, 08 Apr 2009 02:32:48 GMTTao Klerkshttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]Julie Breutzmann (3/26/2009)[/b][hr]While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.[/quote]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:JochenWed, 08 Apr 2009 02:20:21 GMTJochen Vleminghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxI 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.Tue, 07 Apr 2009 13:57:36 GMTSQLEnthusiastichttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxThe question was edited to note 2005.Wed, 01 Apr 2009 09:31:06 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]Vijaya Kadiyala (4/1/2009)[/b][hr]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 Kadiyalawww.dotnetvj.vom[/quote]It [u]was[/u] specific to a particular SQL Server version. You didn't read the question carefully.Wed, 01 Apr 2009 09:29:29 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxMy 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 Kadiyalawww.dotnetvj.vomWed, 01 Apr 2009 09:18:30 GMTVijaya Kadiyalahttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxEven 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.Fri, 27 Mar 2009 13:32:46 GMTi2lovefishinghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]serinor.e090266 (3/27/2009)[/b][hr]I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005More information:1)Output for SELECT @@VERSIONMicrosoft 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?[/quote]Nope that's definitely SQL 2000, SP4!KevFri, 27 Mar 2009 06:25:42 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]serinor.e090266 (3/27/2009)[/b][hr]I dont know if there is a problem, but i have tested the sentence in a SQL Server 2005More information:1)Output for SELECT @@VERSIONMicrosoft 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?[/quote]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.Fri, 27 Mar 2009 06:25:42 GMTJason Shadonixhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxI dont know if there is a problem, but i have tested the sentence in a SQL Server 2005More information:1)Output for SELECT @@VERSIONMicrosoft 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?Fri, 27 Mar 2009 06:06:23 GMTj nuñezhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]serinor.e090266 (3/27/2009)[/b][hr]And, this is my version:Edition: Enterprise EditionEngine: 3Version: 8.00.2039upgrade: SP4[/quote]That is SQL Server 2000, [i][b]NOT[/b][/i] SQL Server 2005.Fri, 27 Mar 2009 05:24:35 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxFYI:In SQL 2000, the code runs without any errors--data is input into the table and the query returns 1,2,3,4,5 and 6.In SQL 2005, database in compatibility mode 80, the data is input into the table and the query returns both the above result set and the error message.In SQL 2005, database in compatibility mode 90, no data is input into the table and the query returns only the error message.Unfortunately I don't have SQL 2008. Still a fascinating outcome of one sample code run against various versions of SQL Server and database compatibility mode.(BTW--got it wrong as I first cross ckecked against SQL 2000).Fri, 27 Mar 2009 04:13:52 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]serinor.e090266 (3/27/2009)[/b][hr]And, this is my version:Edition: Enterprise EditionEngine: 3Version: 8.00.2039upgrade: SP4[/quote]And the compatibility mode for the database you ran the code in?Fri, 27 Mar 2009 02:06:05 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxAnd, this is my version:Edition: Enterprise EditionEngine: 3Version: 8.00.2039upgrade: SP4Fri, 27 Mar 2009 02:02:20 GMTj nuñezhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxI ran the code in a SQL Server 2005 and the results were:123456So the correct answer is: c.I expect you correct this situation, and give me my points (lol, ja, ja, ja)Fri, 27 Mar 2009 01:52:37 GMTj nuñezhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxWhy? That's the puzzle. It looks as though [b] [size="3"] the optimizer is not really building an intermediate resultset of numeric values, but rather is applying the various conditions (col>=1, col<=6, IsNumeric=1) all against the original table Test. [/size][/b] Sure enough, looking at the estimated plan (highlight the query and press Ctrl+L), we see that the query will be handled by a table scan with a predicate of[quote]CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)>={1)AND CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)<=(6)AND isnumeric([MyDatabase].[dbo].[Test].[col])=1[/quote] Which suggests that the execution plan might be different depending on the result experienced... The question is How/why would that happen?Thu, 26 Mar 2009 16:21:19 GMTBen Leightonhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]don (3/26/2009)[/b][hr]In SQL 2008, the error is produced by the Between clause, not the initial Insert and Unions. The Help states the expressions used in the Between statement must all be the same type."Select COL From test Where COL Between 1 and 6" produces an error."Select COL From test Where COL Between 'A' and 'D'" is successful.This was a poor question because the SQL Version wasn't stated, and even the explanation was incorrect![/quote]Don,I don't have 2008, so maybe you can tell me if it really is different from SQL 2005. After running the script and getting the conversion error, I find the table is still empty. How could the error be referring to the BETWEEN operation in the WHERE clause if there is no row with an "A" trying to cast to an int?The error seems to come from the UNION of incompatible types. This code, stark as it is, fails: ([b]Note again, I'm running SQL2005[/b][code]Select 7UNION Select 'A'[/code]In further testing, I find that after creating the table, I can insert a bunch of numbers OR a bunch of letters, but only in separate runs.This works:[code]Insert into TestSelect 1union Select 2[/code]and this works:[code]Insert into TestSelect 'A'union Select 'B'[/code]But this fails with the conversion error:[code]Insert into TestSelect 'A'union Select 2[/code]and this fails with the conversion error:[code]Insert into TestSelect 1union Select 'B'[/code]The weird stuff starts with various SELECT queries.This works, finding all the numbers:[code]Select Col From Test Where Isnumeric(Col)=1[/code]And this works, finding numbers in a range:[code]Select Col From Test Where Isnumeric(Col)=1 And Col Between 1 and 3[/code]The subselect run by itself finds the numbers:[code]Select Col From Test Where Isnumeric(Col)=1[/code]But the original problem code fails.[code]Select ColFrom (Select Col From Test Where Isnumeric(Col)=1) XWhere Col Between 1 and 6[/code]Why? That's the puzzle. It looks as though [b] [size="3"] the optimizer is not really building an intermediate resultset of numeric values, but rather is applying the various conditions (col>=1, col<=6, IsNumeric=1) all against the original table Test. [/size][/b] Sure enough, looking at the estimated plan (highlight the query and press Ctrl+L), we see that the query will be handled by a table scan with a predicate of[quote]CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)>={1)AND CONVERT_IMPLICIT(int,[MyDatabase].[dbo].[Test].[col],0)<=(6)AND isnumeric([MyDatabase].[dbo].[Test].[col])=1[/quote] Thu, 26 Mar 2009 15:43:45 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]dphillips (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr]Have you checked the compatibility level?[/quote]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.[/quote]Yeah, like I said there is one heck of a wicked question waiting to be born there. Something like"[quote]Jane has three databases: A, B and C on three servers. One is a Level 80 DB on a version 2000 server, one is a Level 80 DB on a version 2005 server and one is a Level 90 DB on a version 2005 server. Unfortunately, Jane no longer remembers which is which.Jane executes the following query on all three databases: {insert query here}. On database A it returns 1, 2, 3, 4, 5, and 6. In database B it returns the following error: {insert error message here}. On database C it returns [i]both[/i].Which database is which?[/quote]Now [i]that's[/i] a wicked question! :w00t:Thu, 26 Mar 2009 15:37:09 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxJust switched databases to one with 2005 compatibility mode... no result set this time.Got only the message:Msg 245, Level 16, State 1, Line 1Conversion 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.Thu, 26 Mar 2009 15:34:50 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]dphillips (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]Jason Shadonix (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]ganeshi (3/26/2009)[/b][hr]This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh[/quote]I have tested it on SQL Server 2005 and I get answer (a).[/quote]Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.[/quote]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.[/quote]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.[/quote]Have you checked the compatibility level?[/quote]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.Thu, 26 Mar 2009 15:25:31 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxJust manually typed it all in exactly as displayed, same result... a result and a messages tab.Thu, 26 Mar 2009 15:16:18 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]RBarryYoung (3/26/2009)[/b][hr]You know, correctly reformulated this could be one heck of a wicked question.[/quote]Yes, despite the whining from the first couple of pages, it uncovered some interesting information.Thu, 26 Mar 2009 15:12:48 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]dphillips (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]Jason Shadonix (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]ganeshi (3/26/2009)[/b][hr]This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh[/quote]I have tested it on SQL Server 2005 and I get answer (a).[/quote]Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.[/quote]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.[/quote]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.[/quote]Have you checked the compatibility level?Thu, 26 Mar 2009 15:09:35 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]Jason Shadonix (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]ganeshi (3/26/2009)[/b][hr]This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh[/quote]I have tested it on SQL Server 2005 and I get answer (a).[/quote]Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.[/quote]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.[/quote]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.Thu, 26 Mar 2009 15:04:18 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxBe 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.Thu, 26 Mar 2009 15:03:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxYou know, correctly reformulated this could be one heck of a wicked question.Thu, 26 Mar 2009 15:02:54 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspx[quote][b]Jason Shadonix (3/26/2009)[/b][hr][quote][b]RBarryYoung (3/26/2009)[/b][hr][quote][b]ganeshi (3/26/2009)[/b][hr]This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh[/quote]I have tested it on SQL Server 2005 and I get answer (a).[/quote]Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.[/quote]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.Thu, 26 Mar 2009 14:58:36 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxOn 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 TestWhere Col Between 1 and 6I 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! :-PThu, 26 Mar 2009 11:46:14 GMTStephanie J Brownhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxMicrosoft 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 1Conversion failed when converting the varchar value 'A' to data type int.But in 2000 it give result 1,2,3,4,5,6.Thu, 26 Mar 2009 10:53:58 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic683808-1517-1.aspxI'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.Thu, 26 Mar 2009 10:49:00 GMTSteve Jones - SSC Editor