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.

STORED PROCEDURE

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

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


AS 

BEGIN
SET NOCOUNT ON;

CREATE TABLE #Temp
(
	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));
	

INSERT INTO #Temp 
			(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
************************/
SELECT	DISTINCT t.LessonID, t.HBOFullCode
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



DROP TABLE #Temp

END;

Open in new window


CFML CODE:

<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" />
</cfif>



<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" />
</cfstoredproc>

<cfdump var="#listSkills#" />

Open in new window

DebuggingInfo.png
errormessage.png
eblankeAsked:
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"
0
eblankeAuthor Commented:
It's there, see line 119 in the SP and line 26 in CFML
0
eblankeAuthor Commented:
Also, below is an example of it running in MSSQL (see attached file for results).

USE [healthsmartDev]
GO

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

GO

Open in new window

resultsSetWhenRunInMSSQL.png
0
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.

SidFishesCommented:
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.
0
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.
0
SidFishesCommented:
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
0
SidFishesCommented:
Do you get an error when you run it only with

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

?
0
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.
0

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.
0
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.