Stored Procedure Not Returning Recordset in ASP

[rhungund]

Hi All. My question is this. I have a complex stored procedure in SQL
Server which works fine when I run it in Query Analyzer. However, when
I call it within my ASP script, it returns nothing, and sometimes locks
up. If I change my script to call other existing stored procedures it
works fine. It's just with this particular stored proc. I have tried
various DB calls in ASP, such as opening the recordset through an ADO
connection and through the Command object but to no avail. Here is my
SQL:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE dbo.sp_getProfessionalsByTypeID

@typeID int

AS
declare @catID int
declare @userID int
declare @strUserName varchar(100)
declare @strFirstName varchar(100)
declare @strLastName varchar(100)
declare @strTypeName varchar(100)
declare @strCategoryName varchar(100)
declare @strPictureURL varchar(100)
declare @sql varchar(1000)
declare @a varchar(100)
declare @b varchar(100)
declare @c varchar(100)
declare @d varchar(100)

begin

set @a=''
set @b=''
set @c=''
set @d=''

--Create Temp Table
CREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,
strUserName varchar(100), strFirstName varchar(100), strLastName
varchar(100), strTypeName varchar(100), strCategoryName
varchar(100),strPictureURL varchar(100))
--Search Query
begin
INSERT #QueryResult
SELECT
dbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,
dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,
dbo.tbl_users.strLastName,
dbo.tbl_users.strFirstName,
dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,
dbo.tbl_professionals.strPictureURL
FROM
dbo.tbl_musician_type INNER JOIN
dbo.tbl_category ON dbo.tbl_musician_type.nCatID
= dbo.tbl_category.nCategoryID INNER JOIN
dbo.tbl_profile ON
dbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOIN
dbo.tbl_users ON dbo.tbl_profile.nUserID =
dbo.tbl_users.nUserID LEFT OUTER JOIN
dbo.tbl_professionals ON dbo.tbl_users.nUserID
= dbo.tbl_professionals.nUserID
WHERE
dbo.tbl_musician_type.nTypeID = @typeID
end
--Create Temp Table
CREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,
nUserID int, strUserName varchar(100), strFirstName varchar(100),
strLastName varchar(100), strTypeName varchar(100), strCategoryName
varchar(100),strPictureURL varchar(100), strArtist varchar(100),
strAlbumTitle varchar(100), strRecordLabel varchar(100), strYear
varchar(100))
--Now Declare the Cursor for Speakers

declare cur_musicians CURSOR FOR
--Combined Results Grouped
select distinct nCatID, nTypeID, nUserID, strUserName, strLastName,
strFirstName, strTypeName, strCategoryName, strPictureURL
From #QueryResult

open cur_musicians

fetch next from cur_musicians INTO @catID, @typeID, @userID,
@strUserName, @strLastName, @strFirstName, @strTypeName,
@strCategoryName, @strPictureURL

--Loop Through Cursor

while @@FETCH_STATUS = 0
begin
SELECT TOP 1 @a = strArtist, @b=strAlbumTitle,
@c=strRecordLabel, @d=strYear
FROM dbo.tbl_profile_discog
where nTypeID = @typeID AND nCategoryID = @catID AND nUserID =
@userID

insert #QueryResult2
select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,
@strUserName as strUserName, @strLastName as strLastName, @strFirstName
strFirstName, @strTypeName as strTypeName, @strCategoryName as
strCategoryName, @strPictureURL as strPictureURL, @a as
highlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,
@d as highlightYear
fetch next from cur_musicians INTO @catID, @typeID, @userID,
@strUserName, @strLastName, @strFirstName, @strTypeName,
@strCategoryName, @strPictureURL
set @a = ''
set @b=''
set @c=''
set @d=''
end
select * from #QueryResult2 TI

--Clean Up
close cur_musicians
deallocate cur_musicians
drop table #QueryResult
drop table #QueryResult2

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Dan Guzman]

Try adding SET NOCOUNT ON to the beginning of your proc. This will suppress
DONE_IN_PROC messages that are returned as closed/empty recordsets to ADO.

Also, consider rewriting to use set-based processing instead of the cursor
and temp tables. This will likely provide better performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rh******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi All. My question is this. I have a complex stored procedure in SQL
Server which works fine when I run it in Query Analyzer. However, when
I call it within my ASP script, it returns nothing, and sometimes locks
up. If I change my script to call other existing stored procedures it
works fine. It's just with this particular stored proc. I have tried
various DB calls in ASP, such as opening the recordset through an ADO
connection and through the Command object but to no avail. Here is my
SQL:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE dbo.sp_getProfessionalsByTypeID

@typeID int

AS
declare @catID int
declare @userID int
declare @strUserName varchar(100)
declare @strFirstName varchar(100)
declare @strLastName varchar(100)
declare @strTypeName varchar(100)
declare @strCategoryName varchar(100)
declare @strPictureURL varchar(100)
declare @sql varchar(1000)
declare @a varchar(100)
declare @b varchar(100)
declare @c varchar(100)
declare @d varchar(100)

begin

set @a=''
set @b=''
set @c=''
set @d=''

--Create Temp Table
CREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,
strUserName varchar(100), strFirstName varchar(100), strLastName
varchar(100), strTypeName varchar(100), strCategoryName
varchar(100),strPictureURL varchar(100))
--Search Query
begin
INSERT #QueryResult
SELECT
dbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,
dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,
dbo.tbl_users.strLastName,
dbo.tbl_users.strFirstName,
dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,
dbo.tbl_professionals.strPictureURL
FROM
dbo.tbl_musician_type INNER JOIN
dbo.tbl_category ON dbo.tbl_musician_type.nCatID
= dbo.tbl_category.nCategoryID INNER JOIN
dbo.tbl_profile ON
dbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOIN
dbo.tbl_users ON dbo.tbl_profile.nUserID =
dbo.tbl_users.nUserID LEFT OUTER JOIN
dbo.tbl_professionals ON dbo.tbl_users.nUserID
= dbo.tbl_professionals.nUserID
WHERE
dbo.tbl_musician_type.nTypeID = @typeID
end
--Create Temp Table
CREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,
nUserID int, strUserName varchar(100), strFirstName varchar(100),
strLastName varchar(100), strTypeName varchar(100), strCategoryName
varchar(100),strPictureURL varchar(100), strArtist varchar(100),
strAlbumTitle varchar(100), strRecordLabel varchar(100), strYear
varchar(100))
--Now Declare the Cursor for Speakers

declare cur_musicians CURSOR FOR
--Combined Results Grouped
select distinct nCatID, nTypeID, nUserID, strUserName, strLastName,
strFirstName, strTypeName, strCategoryName, strPictureURL
From #QueryResult

open cur_musicians

fetch next from cur_musicians INTO @catID, @typeID, @userID,
@strUserName, @strLastName, @strFirstName, @strTypeName,
@strCategoryName, @strPictureURL

--Loop Through Cursor

while @@FETCH_STATUS = 0
begin
SELECT TOP 1 @a = strArtist, @b=strAlbumTitle,
@c=strRecordLabel, @d=strYear
FROM dbo.tbl_profile_discog
where nTypeID = @typeID AND nCategoryID = @catID AND nUserID =
@userID

insert #QueryResult2
select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,
@strUserName as strUserName, @strLastName as strLastName, @strFirstName
strFirstName, @strTypeName as strTypeName, @strCategoryName as
strCategoryName, @strPictureURL as strPictureURL, @a as
highlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,
@d as highlightYear
fetch next from cur_musicians INTO @catID, @typeID, @userID,
@strUserName, @strLastName, @strFirstName, @strTypeName,
@strCategoryName, @strPictureURL
set @a = ''
set @b=''
set @c=''
set @d=''
end
select * from #QueryResult2 TI

--Clean Up
close cur_musicians
deallocate cur_musicians
drop table #QueryResult
drop table #QueryResult2

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Erland Sommarskog]

(rh******@gmail.com) writes:

Hi All. My question is this. I have a complex stored procedure in SQL
Server which works fine when I run it in Query Analyzer. However, when
I call it within my ASP script, it returns nothing, and sometimes locks
up.
Make sure that you have SET NOCOUNT ON. Also, it's always a good idea
to loop over .NextRecordset to get all recordsets. (And beware that some
recordsets may be closed.)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Any particular reason that you run with these settings off? Run with
ANSI_NULLS or QUOTED_IDENTIFIER on, unless you have a legacy application.
Some functionality in SQL Server is not available when these settings
are off.
ALTER PROCEDURE dbo.sp_getProfessionalsByTypeID
The sp_ prefix is reserved for system storec procedures, and SQL Server
first looks in master for these. Don't use this prefix for your own
procedures.
SELECT TOP 1 @a = strArtist, @b=strAlbumTitle,
@c=strRecordLabel, @d=strYear
FROM dbo.tbl_profile_discog
where nTypeID = @typeID AND nCategoryID = @catID AND
nUserID =
@userID

This looks a little funny. You want row from this table that matches the
WHERE clause, but you don't care which row? If you have some requirement,
you need to add an ORDER BY clause to enforce your will.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rhungund]

hmmm...i tried setting nocount = on but still the recordset won't
appear in my asp script. the procedure works when i run it in query
analyzer though.

here's my asp code if this will help:

dim objRS
dim objConn
Set objRS = Server.CreateObject("ADODB.Recordset")

Set objConn = Server.CreateObject ("ADODB.connection")
objConn.ConnectionString = dbConn
objConn.open

Set objRS.ActiveConnection = objConn
objRS.CursorLocation = 3
objRS.Open "sp_getProfessionalsByTypeID" & nTypeID

set executeSQL = objRS
set objConn = nothing

[rhungund]

One other thing...i just did a test, and took out SQL code from that
procedure until I got it working. Looks like as soon as I create my
first temp table is when my asp script stops working.

any ideas??

thanks.

[rhungund]

ok...actually the set nocount on did work. i had it in the wrong
location.

oh, and the reason i don't care which row i select is because I just
need to display any 1 entry from that recordset.

thanks for your help everyone!