473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

SQL Server setting has any effect on queries?

Hello,

When I try to run the following query on two different SQL Servers,
I get error on one of the server (both has same set of data). I was
trying to get rows for ProductCode='XYZ_Q1'.

SELECT ProductName, ProductType, ProductDesc FROM Product WHERE
DepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,
CHARINDEX('_', ProductCode)+2, 1) AS int)=1

Example data
ProductCode|ProductName|ProductType|DepartmentID|P roductDesc
XYZ_T_1|Test1|1000|12|Test
XYZ_T_2|Test2|1000|12|Test
ABC_T_1|Test3|1000|11|Test
ABC_T_2|Test4|1000||11|Test
XYZ_Q1|Test5|1000|12|Test
ABC_Q1|Test6|1000|11|Test

It's trying to cast all values under 'ProductCode' column instead of
applying to subset with condition 'DeparmentID=12 AND ProductType >
2000 '

I solved the problem by equating it to whole string rather than
trying to extract the integer part of it. But I wanted find-out reason
as to why this is happenning.
Is there any SQL Server setting that's causing this?

Thank you very much
Manchaiah
Jul 20 '05 #1
1 1343
Madhu (ma*******@hotmail.com) writes:
When I try to run the following query on two different SQL Servers,
I get error on one of the server (both has same set of data). I was
trying to get rows for ProductCode='XYZ_Q1'.

SELECT ProductName, ProductType, ProductDesc FROM Product WHERE
DepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,
CHARINDEX('_', ProductCode)+2, 1) AS int)=1


Please, next you get an error, include the error message in your post.
This may improve the quality of the answer you get.

For this time, I will have to guess and that is that the two SQL Servers
applies the conditions in different order, so that one machine it
never attempts to case to int something which cannot be cast to int.

This is not necessarily due to a setting, but could be because the
statistics of the tables are different. It cold also be that there
different versions of SQL Server and the optimizer was changed. In
any case, you should not rely on that SQL Server does not try a CAST
that may fail.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: T.S.Negi | last post by:
To support remote transaction on SQL Server i have configured user options to 16384. However if i make changes in other properties of SQL Server, user option setting reverts to its original value....
1
by: Rene | last post by:
We have an SQL server 2000 server with four Intel Xexon cpu's. The version of SQL Server is: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003...
16
by: Rico | last post by:
I'm moving some queries out of an Access front end and creating views out of them in SQL Server 2005 express. In some of the numeric fields, I use nz quite often, ( i.e. nz(,0)) to return a zero...
5
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.