Here are some thing to try.
1. You can use the CFDUMP tag to output the values of variables to assist you in troubleshooting. If you use CFDUMP output the value of getFeaturedWineries_qry on the line before the CFLOOP do the contents appear as expected?
2. Does a CFDUMP of #REQUEST.CFMPath#includes/displayFeaturedWineries.cfm point to a file that exists on your system?
3. Same for #datasets#.
4. In regards to Apache vs. IIS, could there be an issue with a path or other value being case sensitive in Apache?
5. In regards to SQL Server does a SQL profile/trace actually show any queries/stored procedures being executed?
It might be helpful to have more of your code to look at as well as more details about how your enviroment is setup.
CFDUMP tag documentation
The cfdump shows me exactly what I thought, its looking for the stored proc usp_Featured Wineries. After looking through more pages on the site, almost all pages have some kind of validation error for tag cloop around stored procs, BUT, not all. The user that logs into the database is a dbo so I can't see what the problem is there.
I installed IIS7 and got that working fine, so its not an Apache / IIS thing either. I might try and trace the database server to see if there's a problem there, but I'm guessing its a setting problems either on the Database itself or ColdFusion. None of it makes any sense otherwise (why would it run some procs and not others???).
Any other thoughts?
It is would be easier to advise you if you would post additional source code and error messages, if any, you receive.
I'm not sure what code apart from the above is going to show you what you could possibly need to solve this, but here goes. The following is only snippets of the code, but they are the relavant snippets. Other stored procs work. The cfdump for getFeaturedWineries_qry shows 'usp_getFeaturedWineries'. I know its there because I've been unable to run "EXEC usp_getFeaturedWineries" after copying it straight out of the code. As previously stated, there are other stored procs which are running fine.
<cfargument name="storedProcedure" required="Yes" type="string">
<cfstoredproc procedure = "#ARGUMENTS.storedProcedure#"
datasource = "#REQUEST.DSN_NAME#"
username = "#REQUEST.DSN_USER#"
password = "#REQUEST.DSN_PASS#"
returncode = "Yes">
<cfprocresult name="storedProcedure" resultset="1">
<!--- DATA SOURCE VARIABLES --->
<cfset DSN_NAME = "boutiqueWineriesTest">
<cfset DSN_USER = "coldfusion">
<cfset DSN_PASS = "coldfusion">
<cfobject component="#COMP_REF#.datasets" name="datasets">
<cfinvoke component="#datasets#" method="storedProcedure" returnvariable="getFeaturedWineries_qry">
<cfinvokeargument name="storedProcedure" value="usp_getFeaturedWineries">
<cfinclude template = "#REQUEST.CFMPath#includes/displayFeaturedWineries.cfm">
<cfloop query="getFeaturedWineries_qry" startrow="1" endrow="2">
Attribute validation error for tag cfloop.The value of the attribute query, which is currently getFeaturedWineries_qry, is invalid. The error occurred in C:\apps\iishome\includes\displayFeaturedWineries.cfm: line 33
Called from C:\apps\iishome\index.cfm: line 399
Called from C:\apps\iishome\index.cfm: line 79
Called from C:\apps\iishome\index.cfm: line 1
31 : <tr> 32 : <td width="626" colspan="3" class="productCenter_bg2"> 33 : <cfloop query="getFeaturedWineries_qry" startrow="1" endrow="2"> 34 : <div class="productCennews"> 35 : <table border="0" cellspacing="0" cellpadding="0">
You wouldn't believe this, but I backed out ColdFusion 9, installed ColdFusion 8 and it worked a treat.
What has changed between CF 8 and 9 to make something like this to happen (as well as other problems). Unreal........never seen something so problematic with backward compatibility.
Just when I think it's fixed....it's not.
It now works fine in CF 8 on the index page only. Still getting the error on all others pages......any suggestions?
The cfdump shows me exactly what I thought, its looking for the stored proc usp_Featured
What do you mean it's "looking for" the proc? What is the output of the dump? Did you put the dump in the function or the calling code? What does your proc return if its query returns no rows? Does it return a null? If so, the thing is behaving exactly as one would expect: CF - all versions - will report a variable as undefined if it's set to null. It also seems a bit odd that you're reusing the storedproc arg as the return value for the function. This shouldn't cause your situation, but it's not great code.
Apologies for that being one big run-on paragraph. Am posting this via my phone and the main editor doesn't work on the iPhone's version of Safari, so need to use the HTML view. And I forgot to put <br /> tags in.
It sounds like a bit of a tirade when presented like that, but it wasn't intended to!
If I do a <cfdump var="getFeaturedWineries_qry"> I get: getFeaturedWineries_qry
If I do a <cfdump var="#getFeaturedWineries_qry"> I get the dump of the query. So we're ok there on the index.cfm page (which has the invoke statements).
If I open a page that isn't index.cfm, the <cfdump var="#getFeaturedWineries_qry#"> from displayFeaturedWineries.cfm says Variable not defined.
Excuse me for being somewhat new, but that says to me that the variable is not being kept in memory after leaving the index page. So how does it do it on the production environment (hosted environment) but not on my local environment??
Maybe mappings?? No idea....I'm a newbie
If I open a page that isn't index.cfm, the <cfdump var="#getFeaturedWineries_qry#"> from displayFeaturedWineries.cfm says Variable not defined
That's because the variable is being created on index.cfm, the page that runs the stored procedure.
I'm seriously going to run and hide and bang my head against the wall several times until my brain clicks back into gear.
Apologies for wasting your time gentlemen. I've found the problem. It's called the operator!