From: Zed Qyves (zqyves.spamtrap@gmail.com)
Date: Fri Jan 11 2008 - 04:35:36 EST
I just had to try regarding the text thingy...
---------------------------------------------------------------------------------------------------select 1,1,1,convert(text,'HELLO'),1union all select 1,1,1,1;
Server: Msg 206, Level 16, State 2, Line 1Operand type clash: text is incompatible with intServer: Msg 205, Level 16, State 1, Line 1All queries in an SQL statement containing a UNION operator must havean equal number of expressions in their target lists.
---------------------------------------------------------------------------------------------------
select 1,1,1,convert(nvarchar,'HELLO'),1union all select 1,1,1,1;Server: Msg 205, Level 16, State 1, Line 1All queries in an SQL statement containing a UNION operator must havean equal number of expressions in their target lists.
---------------------------------------------------------------------------------------------------
Oh f@$k!
On Jan 11, 2008 12:16 AM, Francois Larouche<francois.larouche-ml@sqlpowerinjector.com> wrote:> I wouldn't agree with your statement Zed.>> What he found was where the first conversion clash occurred. It seems> that your third expression is text field, no big deal. For some reason> SQL Server gives a higher priority on text is incompatible with int> error or any casting problem than the equal number of expressions in a> UNION clause. Believe me I learned that at my expense...>
Chances are I misread you above statement, but what do you mean by "or any casting problem "?The nvarchar casting above - below works as expected
----------------------------------------------------------------select 1,1,1,cast('HELLO' AS nvarchar),1union all select 1,1,1,1;
Server: Msg 205, Level 16, State 1, Line 1All queries in an SQL statement containing a UNION operator must havean equal number of expressions in their target lists.----------------------------------------------------------------
> The actual number is 16 if I counted well with his HAVING test.>> Your problem now Joseph is just to make sure that you can have the right> format. If you use NULL it will work each time, however you won't get> anything back...>
Partly true - oh well unless you found something else in your expense:). You just have to pick one column that is displayed back and putyour data selection there if you want multiple data rows to bereturned back. Alternatively pick an int column and union selectstrings on that. Detailed error messages - which you seem to have -will get you the data back one at a time. Recurse on that with NOT IN('data'...)
------------------------------------------------------------------------------------------------select 1,1,1,1,1union all select 1,1,1,suser_sname(),1
Server: Msg 245, Level 16, State 1, Line 1Syntax error converting the nvarchar value 'sa' to a column of data type int.------------------------------------------------------------------------------------------------
> The error you have is that you used SELECT ALL, in fact you need to use> UNION ALL SELECT .... the ALL goes between UNION and SELECT>Spot on.
Have fun,ZQ
-- ---------------------------------------------------------------------Κρέωνἐν τῇδ᾽ ἔφασκε γῇ· τὸ δὲ ζητούμενονἁλωτόν, ἐκφεύγειν δὲ τἀμελούμενον.Οιδίπους Τύρρανος [110]---------------------------------------------------------------------CreonIn this our land, so said he, those who seek Shall find; unsought, welose it utterly.Oedipus Rex [110]---------------------------------------------------------------------
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 10:58:19 EDT