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 vwL开发者_运维技巧ocationsWithAlias
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!?

---

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

---

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

---

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

---

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

---

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

---

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.

---

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.