CODEWITHSUNDEEP
sql-servercoldfusion
E-Madd
E-Madd

Reputation: 4572

Query Results Differ In SQL Mgmt and CFQuery

executing the following query in SQL management studio provides results, whereas it does not via cfquery...

select distinct locationid, locationname, locationaliasname
from vwLocationsWithAlias
where 1 = 0
or (LocationName = N'the' or LocationAliasName = N'the')
or (LocationName = N'the republic' or LocationAliasName = N'the republic')

The results expected are returned from SQL Mgmt Studio, but nothing is returned from CFQuery. WTF!?

Upvotes: 3

Views: 531

Answers (7)

HLGEM
HLGEM

Reputation: 96552

Run Profiler while you send the query from Coldfusion and see if it is sending what you expected.

Upvotes: 2

Woody Zenfell III
Woody Zenfell III

Reputation: 1955

Is it possible that SSMS and CFQuery have different 'SET' or other per-connection options? Some of those can affect results.

If you can catch connections open when you start a SQL Profiler trace, you can see a bunch of the connection options when you highlight the "Existing Connection" row. (Even if you don't catch them open, I have to assume you'd be able to see them when a connection is established and shortly thereafter . . .) Or, you may be able to get cfquery to SELECT @@OPTIONS or SELECT SESSIONATTRIBUTE(...).

I confess I haven't carefully considered your query in light of all possible connection options to have a strong hypothesis - it's just a possible lead.

Upvotes: 0

crosenblum
crosenblum

Reputation: 1927

Unless your fields are nvarchar vs varchar, why not just take out the N'the' and just use 'the' or whatever the data you want in your where clause.

You can also check permissions on your view, to make sure coldfusion user that you use via datasources, is setup to select from that view.

That is the other difference between a coldfusion query and a query analyzer query, it could use different user credentials.

Good Luck.

Upvotes: 0

ColdFusion
ColdFusion

Reputation: 1

Can I also recommend NOT using distinct as it's a lot slower than using "group by" on large datasets

Upvotes: 0

Casuzen
Casuzen

Reputation: 169

try using preserveSingleQuotes around the parts that have the apostrophes..

Upvotes: 0

Yisroel
Yisroel

Reputation: 8174

are you sure its hitting the correct database (not a development db) :)

Upvotes: 0

Edward M Smith
Edward M Smith

Reputation: 10627

I assume its the TransactSQL Unicode indicator ("N") you have on your string constraints. I suspect the SQL parser in CF doesn't understand that.

Does the CF throw an error, or just not return any rows?

There is a setting in the Datasource control for sql server which tells that the DB is in unicode:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html

Upvotes: 0

Related Questions