Queries not working in CF......

[djkhalif]

Good morning all,

I have this query that I can run in SQL Server Management Studio but it will not run in ColdFusion. Do I need to put in a stored procedure to run the below code?

<cffunction name="itmChk" returntype="query">

    <cfargument name="searchPO" required="yes">

    <cfquery name="itemCheck" datasource="#REQUEST.datasource#">

    select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate

        from recvTemp t

        where t.ordno = '#arguments.searchPO#'  and not exists(select *

                                         from recvLogTest r

                                         where t.ordno = r.ordno and t.itnbr = r.itnbr)

    </cfquery>

    <cfreturn itemCheck>

    </cffunction>

Thanks for any help!!!

DJkhalif

[Adam Cameron.]

What do you mean "will not run"?

Recommend you read this:

http://forums.adobe.com/thread/607238

--

Adam

[djkhalif]

Adam,

Thanks for the tip on how to post. What I mean by will not run, the query does not return any values using a dump or with cfoutput. There are no errors and when executed in SSMS, there are values. I have another query doing the same thing. I've restarted the CF9 Application Service, I've run them in dumps and I've remote the Application.cfc #REQUEST.datasource# and hardcoded the db name. I've even taken one of them out of the cfc and placed directly in the page, nothing happens. Tags: cfquery, coldfusion9,cfc.

[jbreslow]

I would check to see what value is being passed to the query and even email yourself a dump of the query after it runs (which will include the actual query it is running). Then copy and paste that into SSMS and see if it returns records.

[JR__Bob__Dobbs · Accepted solution]

You might also use SQL Profiler to view the query that is being excuted.

"SQL Server Profiler Step by Step"

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

"Getting ColdFusion SQL Statements from SQL Server Trace"

http://www.petefreitag.com/item/674.cfm

[djkhalif]

JR,

After running the Trace, I noticed that my cfinvokeargument value was incorrect. The value was passing a string instead of a variable. value="URL.name" instead of  value="#URL.name#".

Thanks,

djkhalif