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 12»»

Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 2:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:46 AM
Points: 21, Visits: 125
Hello,

We have a problem in the query within the attached file. When I run in sql server 2000 it returns the result without errors, but when I run the SQL Server 2008 R2 SP2 it returns the error message: Msg 245, Level 16, State 1, Line 1 Conversion failed converting the varchar value When 'PA 'to data type int.
When I comment the line of the join "and w.unidade = b.Unidade" in SQL Server 2008 r2 sp2 works without showing any error. Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


What could is happening? Someone help us?

Query :
Regards,

Antonio Estima


  Post Attachments 
query_sql2008r2sp2.txt (9 views, 720 bytes)
Post #1364292
Posted Tuesday, September 25, 2012 2:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 246, Visits: 641
antonio.estima 4150 (9/25/2012)
Hello,

We have a problem in the query within the attached file. When I run in sql server 2000 it returns the result without errors, but when I run the SQL Server 2008 R2 SP2 it returns the error message: Msg 245, Level 16, State 1, Line 1 Conversion failed converting the varchar value When 'PA 'to data type int.
When I comment the line of the join "and w.unidade = b.Unidade" in SQL Server 2008 r2 sp2 works without showing any error. Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


What could is happening? Someone help us?

Query :
Regards,

Antonio Estima


What data types are these: w.unidade, b.Unidade
Post #1364296
Posted Tuesday, September 25, 2012 4:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 901, Visits: 7,170
If one of these tables is actually a view, please post the DDL for the view.



And then again, I might be wrong ...
David Webb
Post #1364332
Posted Tuesday, September 25, 2012 4:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 6,237, Visits: 7,391
antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.

You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.

Short version, you have two ways out of this. You have to procedurally control the query. The first is dumping only restricted data into a temp table and then using the temp table from there on out (my preferred method). The second is OPTION ( FORCE ORDER), in which you use a subquery for your WHERE clause'd table, then link the subquery as a table to the rest, like so:

select
a.*,
b.*
FROM
(SELECT * from tblA WHERE SomeCol <> 'PA') AS a
JOIN
tblB
ON a.SomeCol = b.SomeCol
OPTION ( FORCE ORDER)

You've overloaded a column, and your best option here is to clean up the source data and remove these workarounds eventually. There is no 'good' method to fix this.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364337
Posted Wednesday, September 26, 2012 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:46 AM
Points: 21, Visits: 125
w.unidade is int and b.unidade varchar
Post #1364612
Posted Wednesday, September 26, 2012 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:46 AM
Points: 21, Visits: 125
Dear David,

The all objects in this query are not views. They are tables.

The question is this query work very well in sql server 2000 enviroment when put it in sql server 2008 R2 SP2 enviroment don't work very well.

Regards,

Antonio Estima
Post #1364617
Posted Wednesday, September 26, 2012 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,208, Visits: 13,667
antonio.estima 4150 (9/25/2012)
... Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" ...


This doesn't make sense.
SUBSTRING (nomeint, 1,1) = 'P' would include "PA"


Here's the query, reformatted:

select	
w.Unidade,
w.Data,
w.Interface,
w.NomeInterface

from (
select
c.unidade,
c.Data,
c.Interface,
c.NomeInterface
from cad_interface c
inner join sam.dbo.Unidade u
on (c.unidade = u.codigounidade)
where left(interface,6) = 'Inform'
and unidade = 999
and data between 20120701 and 20120702
) as w

left outer join (
select
dataint Data,
left(nomeint,11) COLLATE Latin1_General_CI_AS 'Interface',
SUBSTRING(nomeint,4,2) Unidade
from openquery(BS,'select * from PDTABLE')
where SUBSTRING(nomeint,1,1) = 'P'
) as b
on (w.data = b.data
and w.unidade = b.Unidade
and w.nomeinterface = left(b.interface,8))

order by w.data, w.unidade



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1364657
Posted Wednesday, September 26, 2012 7:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,805, Visits: 35,925
Evil Kraig F (9/25/2012)
antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.


Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.

You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.


This.

The order that a query is processed is not guaranteed. The where clause is not necessarily processed left to right, or right to left, or any other order. It depends on the exec plan created for the query.

Short version, you got lucky on SQL 2000, you need to go and fix your code.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1364671
Posted Wednesday, September 26, 2012 7:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:46 AM
Points: 21, Visits: 125
Dear Evil,

Let me see and understand you.

If we have a query follow:

select w.Unidade,
w.Data,
w.Interface,
w.NomeInterface
from ( select c.unidade,
c.Data,
c.Interface,
c.NomeInterface
from cad_interface c
inner join sam.dbo.Unidade u
on(c.unidade=u.codigounidade)
where left(interface,6)='Inform'
and unidade = 999
and data between 20120701 and 20120702 ) as w
left outer join
( select dataint Data,
left(nomeint,11) COLLATE Latin1_General_CI_AS 'Interface',
SUBSTRING(nomeint,4,2) Unidade
from openquery(BS,'select * from PDTABLE')
where SUBSTRING(nomeint,1,1) = 'P' ) as b
on(w.data = b.data
and w.unidade = b.Unidade
and w.nomeinterface = left(b.interface,8))
order by w.data, w.unidade


In SQL Server 2K or 2K5 the WHERE clause where SUBSTRING(nomeint,1,1) = 'P' is run first in the subquery and then it does the join. However, in SQL Server 2K8 the engine return all informations and then after it does the join?

Thanks for help

Antonio Estima
Post #1364700
Posted Wednesday, September 26, 2012 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,805, Visits: 35,925
Probably not.

The order that the query will be processed in is not defined or guaranteed. The SQL 2008 query optimiser is just producing a plan where the conversion is done before the non-numeric values are filtered out.

The query optimiser changes in every version, therefore plans can change as well



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1364710
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse