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 Friday, March 27, 2009 6:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 1,533, Visits: 2,259
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
Post #684976
Posted Friday, March 27, 2009 6:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:55 AM
Points: 2,716, Visits: 2,479
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
Post #684977
Posted Friday, March 27, 2009 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:24 PM
Points: 3, Visits: 83
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.

Post #685354
Posted Wednesday, April 1, 2009 9:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, 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



Post #688069
Posted Wednesday, April 1, 2009 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:22 AM
Points: 1,187, Visits: 1,984
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.
Post #688091
Posted Wednesday, April 1, 2009 9:31 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #688096
Posted Tuesday, April 7, 2009 1:57 PM
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
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.
Post #692486
Posted Wednesday, April 8, 2009 2:20 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, March 21, 2011 8:57 AM
Points: 418, Visits: 61
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
Post #692831
Posted Wednesday, April 8, 2009 2:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #692840
Posted Wednesday, April 8, 2009 6:12 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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."
Post #692983
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse