September 13, 2012 at 11:02 am
The issue is that the query runs in test and doesnt run in prod.
We have 2 fields in my example: field 1 and field 2
an example if rows:
table 1
field1 field2
11 BX-00006767
12 BX-00006767
13 BX-00006767
14 BX-00006767
DT-1205-006049 BX-00006768
The query is :
Select CAST(RIGHT(field1, LEN(field1)-1) AS INT)
from table1
where field2 = BX-00006767
On Prod we get the error we get is "Conversion failed when converting the varchar value 'DT-1205-006049' to data type int."
I believe that since that value is not in the result set due to a WHERE statement, it is evaluating the CASE first rather than the WHERE on prod.
In DEV this runs fine with a refresh of DEV a day ago. Both have the same data in the table.
The showpan for both are different. so it appears to be an order of operations thing.
I can post the showplan but I don't think it is relevant to the question below.
The question is, without changing my code is there any way I can force the same plan that is on DEV to run in prod?
Thanks!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 13, 2012 at 11:18 am
Where I've run into that kind of thing, I usually have the script/proc insert the rows I want to work on into a temp table, then run the string manipulation on the temp table. Is that an option for you in this case?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2012 at 11:21 am
That was my suggestion to the developer. they have now created an emergency change in change control to get it done. now its not as urgent butmy curiousity is till there. can I move a query plan from one server to another using forced plan without adding anything to the query:)
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 14, 2012 at 6:11 am
Nope. You'd have to at least add the plan to the query. And even that's not guaranteed to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 6:13 am
thought so. thanks all!
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 17, 2012 at 2:27 pm
OK. here is what we have found.
.....the prod db there is little activity IO wise...
sorry. I mis-posted this. this was supposed to go to a different thread.. sorry (http://www.sqlservercentral.com/Forums/Topic1349180-1550-4.aspx?Update=1)
Michael B
Data Architect
MCT, MCITP(SQL DBA 2005/2008),MCTS, MCP,MCDBA
September 17, 2012 at 2:52 pm
I don't know since the following runs with no problems for me:
create table dbo.table1 (
Field1 varchar(30),
Field2 varchar(30));
go
insert into dbo.table1
values
('11','BX-00006767'),
('12','BX-00006767'),
('13','BX-00006767'),
('14','BX-00006767'),
('DT-1205-006049','BX-00006768');
go
select Field1, Field2 from dbo.table1;
go
select cast(right(Field1,len(field1)-1) as int)
from dbo.table1
where Field2 = 'BX-00006767';
go
drop table dbo.table1;
go
All I can think of is that we aren't getting the full story. Something else is going on and we just don't see it.
September 18, 2012 at 4:14 am
Is that query in the OP transcribed correctly? Because you don't seem to have enclosed the BX-00006767 value in quotes, which means SQL server isn't going to interpret it as a string...although I would expect that to fail on both machines, to be honest.
September 18, 2012 at 5:57 am
paul.knibbs (9/18/2012)
Is that query in the OP transcribed correctly? Because you don't seem to have enclosed the BX-00006767 value in quotes, which means SQL server isn't going to interpret it as a string...although I would expect that to fail on both machines, to be honest.
Agree, OP did not provide DDL for the table, but based on what was posted, the data types had to varchar (possibly nvarchar). To insert the data provided this meant that the values had to surrounded in single quotes. It also meant the query posted by the OP was incorrect in its WHERE clause as the character value was not surrounded by single quotes unless that value was supposed to be a column name, which is not supported by what the OP posted.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply