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)
Thanks for any help!!!
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.
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.
You might also use SQL Profiler to view the query that is being excuted.
"SQL Server Profiler Step by Step"
"Getting ColdFusion SQL Statements from SQL Server Trace"
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#".