Last query in MSSQL stored procedure not passing to ColdFusion

I have a MSSQL stored procedure that runs fine in the db, but I'm having trouble when I try and use it in my coldfusion code. All the select statements work individually, but no matter what one I have as the last select statement it won't work. I have moved the select statements around, and which ever one is the last one ColdFusion says that it doesn't exist. In debugging, I can see that the SP is being run. What am I missing?

Below is the SP code and the CFML code. I've also attached the error message and the parameters being passed.


USE [healthsmartDev]
/****** Object:  StoredProcedure [dbo].[sp_GetPacingGuide]    Script Date: 10/02/2013 11:23:49 ******/
ALTER PROCEDURE [dbo].[sp_GetPacingGuide]

@intArrayHBO nvarchar(500),
@intArraySkill nvarchar(50),
@intArrayHBOCat nvarchar(50) output,
@gradeID int,
@HBOCatToRemove int



	LessonID int,
	LessonNumber int,
	LessonLabel varchar(100),
	IsAlwaysRecommended bit,
	LessonCategoryLabel varchar(100),
	HBOID int,
	HBOFullCode varchar(16),
	HBOLabel varchar(250),
	SkillID int,
	SkillCode int,
	SkillLabel varchar(50),
	Notes varchar(500),
	LessonDescription Varchar(1000),
	GradeID int,
	HBoCategoryID int,
	HBOCategoryLabel varchar(100),
	CCStandardID int, /*Added 9/18/13 - KJ */
	CCStandardFullCode varchar(100),/*Added 9/18/13 - KJ */
	MuraContentID char(35));

			(LessonID, LessonNumber, LessonLabel,IsAlwaysRecommended, LessonCategoryLabel, HBOID, HBOFullCode, HBOLabel, SkillID, SkillCode, SkillLabel, Notes, LessonDescription, GradeID, HBOCategoryID, HBOCategoryLabel, CCStandardID, CCStandardFullCode, MuraContentID)
/* this query finds all lessons where hbo and skill 1 are combined, and any lesson with the skills selected */ 

 SELECT l.LessonID, l.LessonNumber, l.LessonLabel, l.IsAlwaysRecommended, l.LessonCategoryLabel, l.HBOID, l.HBOFullCode, l.HBOLabel, 
l.SkillID, l.SkillCode, l.SkillLabel, l.Notes, l.LessonDescription, l.GradeID, l.HBOCategoryID,l.HBOCategoryLabel, l.CCStandardID, l.CCStandardFullCode, l.MuraContentID
FROM          dbo.ListFullDetails AS l
WHERE      (
			(HBOID IN (SELECT * FROM dbo.ConvertListToTable(@intArrayHBO, ',')) and SkillID = 1)OR
            (SkillID IN (SELECT * FROM dbo.ConvertListToTable(@intArraySkill, ',')))
           AND GradeID = @gradeID 
           AND HBOCategoryID IN (SELECT * FROM dbo.ConvertListToTable(@intArrayHBOCat, ','))
           AND HBOCategoryID <> @HBOCatToRemove                      

*** 1  getLessons
SELECT	DISTINCT t.LessonID, t.LessonNumber, t.LessonLabel,t.IsAlwaysRecommended, t.LessonCategoryLabel, t.Notes, t.LessonDescription, t.MuraContentID
FROM #Temp AS t
ORDER BY t.LessonCategoryLabel, t.LessonNumber

*** 2  getLessonHBOs
FROM #Temp AS t

*** 3  getLessonSkills
SELECT	DISTINCT t.LessonID, t.SkillCode 
FROM #Temp AS t

*** 4  getLessonCCStandards
***   Added 9/18/13 - KJ
SELECT	DISTINCT t.LessonID, t.CCStandardFullCode
FROM #Temp AS t

*** 5  listHBOs
SELECT	DISTINCT  h.HBOID, h.HBOFullCode, h.HBOcode, h.HBOLabel, h.HBOCategoryCode
FROM dbo.ListHBOs as h
WHERE HBOID IN (SELECT * FROM dbo.ConvertListToTable(@intArrayHBO, ',')) 
	and HBOCategoryID <> @HBOCatToRemove
	and HBOCategoryID IN (SELECT * FROM dbo.ConvertListToTable(@intArrayHBOCat, ','))
ORDER BY h.HBOCategoryCode, h.HBOCode

*** 6  listHBOCategories
SELECT	DISTINCT  c.HBOCategoryID, c.HBOCategoryLabel
FROM dbo.HBOCategories as c
WHERE HBOCategoryID IN (SELECT * FROM dbo.ConvertListToTable(@intArrayHBOCat, ',')) 
ORDER BY c.HBOCategoryLabel

*** 7  GetLessonHBOCategories
SELECT	DISTINCT t.LessonID, t.HBOCategoryLabel 
FROM #Temp AS t

*** 8  listSkills
SELECT	DISTINCT   s.SkillID, s.SkillCode, s.SkillLabel
FROM dbo.Skills as s
WHERE SkillID IN (SELECT * FROM dbo.ConvertListToTable(@intArraySkill, ','))
ORDER BY s.SkillCode



Open in new window


<cfparam name="form.HBOID" default="0" />
<cfparam name="form.SkillID" default="0" />
<cfparam name="form.HBOCategoryID" default="0" />
<cfparam name="form.GradeID" default="0" />
<cfif form.HBOCatToRemove eq "">
	<cfset form.HBOCatToRemove ="0" />

<cfstoredproc procedure="sp_getPacingGuide" datasource="#application.dsn#" username="#application.username#" password="#application.password#">
	<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" maxlength="500" value="#form.HBOID#" />
    <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" maxlength="50" value="#form.SkillID#" />
    <cfprocparam type="Inout" cfsqltype="CF_SQL_VARCHAR" maxlength="50" value="#form.HBOCategoryID#" />
    <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER"  value="#form.GradeID#" />
    <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER"  value="#form.HBOCatToRemove#" />

    <cfprocresult name="getLessons" resultset="1" />
    <cfprocresult name="getLessonHBOs" resultset="2" />
    <cfprocresult name="getLessonSkills" resultset="3" />
    <cfprocresult name="getLessonCCStandards" resultset="4" />
    <cfprocresult name="listHBOs" resultset="5" />
    <cfprocresult name="listHBOCategories" resultset="6" />
    <cfprocresult name="getLessonHBOCategories" resultset="7" />
     <cfprocresult name="listSkills" resultset="8" />

<cfdump var="#listSkills#" />

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
Maybe I am missing something..

Looking at your SQL/SPROC..  I don't see: listskills..

Hence your error:
"Variable listskills is undefined"
eblankeAuthor Commented:
It's there, see line 119 in the SP and line 26 in CFML
eblankeAuthor Commented:
Also, below is an example of it running in MSSQL (see attached file for results).

USE [healthsmartDev]

DECLARE	@return_value int,
		@intArrayHBOCat nvarchar(50)

SELECT	@intArrayHBOCat = N'1,2'

EXEC	@return_value = [dbo].[sp_GetPacingGuide]
		@intArrayHBO = N'10,11',
		@intArraySkill = N'3,6,7',
		@intArrayHBOCat = @intArrayHBOCat OUTPUT,
		@gradeID = 3,
		@HBOCatToRemove = 0

SELECT	@intArrayHBOCat as N'@intArrayHBOCat'

SELECT	'Return Value' = @return_value


Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Quick guess...Do you have "MultipleActiveResultSets=True" set up in your datasource advanced setting connection string? If not you won't be able to return multiple results.

If that's missing, my guess is that the error may be CF's best guess and is just erroring on the last in the proc call.
eblankeAuthor Commented:
Hi SidFishes, I'm not sure where to set that. is that part of ColdFusion Administrator DSN setup (couldn't see it there), or is it part of MSSQL?  I googled it and it looks like the connection string might not relate to CF, as we set up DSN's within the administrator setup.

Also, another data point; this was all working fine until I added one more select statement into the SP. So I think that whatever settings are needed for multiple results sets is working.
It would be accessible in cfadmin >datasources > yourdatasource >advanced settings button

but if it was working then it was working. guess I'll have to look at your code now ;P
Do you get an error when you run it only with

<cfprocresult name="listSkills" resultset="8" />

eblankeAuthor Commented:
Ok. Total Rookie move. When I look at the datasource it's pointing to the production database, not the dev database. Doh!  sorry for bothering you all.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eblankeAuthor Commented:
I found the problem; my dsn was pointing to the wrong database.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.