ansaurus

Question

Query Results Differ In SQL Mgmt and CFQuery

Answer 1

A:

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

Edward M Smith 2010-04-20 17:42:44

My cfquery is actually using cfqueryparam cfsqltype="cf_sql_varchar" but no matter how I code the sql in cfquery, I get back nothing... I always get back results from sql manager.

E-Madd 2010-04-20 17:48:37

And yes, I have unicode support enabled...

E-Madd 2010-04-20 17:51:04

show us the actual query you are using in cfquery. also, are you getting any errors at all or just no records returned?

Ryan Guill 2010-04-20 17:54:19

<cfquery datasource="#variables.dsn#" name="qryLocations"> select distinct locationid, locationname, locationaliasname from vwLocationsWithAlias where <cfset row = 1/> <cfloop array="#locationPhrases#" index="m"> <cfif row gt 1>or</cfif> ( LocationName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#m#" /> or LocationAliasName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#m#" /> ) <cfset row = row + 1/> </cfloop> </cfquery>

E-Madd 2010-04-20 17:58:20

that cfquery is not the same query as posted above. what do you get if you execute the above query exactly from cfquery?

Yisroel 2010-04-20 18:13:17

If you add a <cfdump var="#qryLocations#"><cfabort> right after the query, it'll show the SQL (without the bind values, tho) - does it look like you expect?

Edward M Smith 2010-04-20 18:20:04

Have you enabled unicode on your datasource @ CF administrator?

Henry 2010-04-20 18:47:08

I've dumped the query and the SQL is correct. With or without cfqueryparam, the query returns nothing.

E-Madd 2010-04-20 19:03:38

and if you run the query from the dump in SQL management studio, it works?

Yisroel 2010-04-20 19:56:52

Yessir. I've narrowed this down to a problem with the use of the array. What's strange is that if the array is dynamically created it doesn't work. If the array is manually created, it works.

E-Madd 2010-04-21 01:11:59

Answer 2

A:

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

Yisroel 2010-04-20 19:11:31

I've verified the correct datasource is being hit by outputting the variable.dsn variable as a comment in my sql, which is dumped on output

E-Madd 2010-04-20 19:16:41

Answer 3

A:

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

Casuzen 2010-04-20 20:20:55

Thanks, but there are no apostrophes in my query params

E-Madd 2010-04-21 01:10:18

Answer 4

A:

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

ColdFusion 2010-04-21 00:49:58

Yeah, that might help performance-wise- but it doesn't change the problem.

E-Madd 2010-04-21 01:10:52

Answer 5

A:

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.

crosenblum 2010-04-21 15:11:34

this has nothing to do with permissions or datatypes. I would certainly get an exception if it was either of those things... not a lack of results

E-Madd 2010-04-23 03:37:29

What about users' default schema?

Woody Zenfell III 2010-06-10 15:20:46

Answer 6

A:

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.

Woody Zenfell III 2010-06-10 15:20:25

Answer 7

+2 A:

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

HLGEM 2010-06-10 15:23:37

ansaurus

tags:

views:

answers:

Query Results Differ In SQL Mgmt and CFQuery

related questions