Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789

TSQL Expand / Collapse
Author
Message
Posted Wednesday, April 8, 2009 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:18 PM
Points: 198, Visits: 84
As 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.
Post #693066
Posted Wednesday, April 8, 2009 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 413, Visits: 407
RBarryYoung (4/8/2009)
Jochen Vleming (4/8/2009)
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.

Jochen

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.

That's very true. Just look at the Top QotD points versus the Top Forum Posters points: http://www.sqlservercentral.com/TotalScores
Post #693122
Posted Thursday, April 16, 2009 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 35,532, Visits: 32,115
arconen (3/26/2009)
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.


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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #698738
Posted Tuesday, June 23, 2009 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 7,847, Visits: 9,596
My 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.


Tom
Post #740128
Posted Friday, December 18, 2009 2:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 3, 2014 7:16 AM
Points: 484, Visits: 45
With 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 => 1234567ABCD
2 "SELECT col FROM test WHERE IsNumeric(col)=1" succeeds => 1234567
3 The query given fails with the conversion error



Regards, Jon Summers
Post #836177
Posted Friday, January 8, 2010 4:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, March 7, 2010 11:12 PM
Points: 76, Visits: 32
Hi there

I 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.



Ashok
Post #844204
Posted Friday, January 21, 2011 2:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, March 11, 2011 6:14 AM
Points: 412, Visits: 27
This works
Insert into test Select 'A' union Select 1;

also
Insert into test Select 1 union Select 'A'; works

but 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.

Post #1051386
Posted Friday, February 15, 2013 11:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 1,938, Visits: 2,372
good 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 1

why?



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1420860
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse