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 5 2512
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
(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
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
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.
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! This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by Daniel |
last post: by
|
1 post
views
Thread by Eugene Anthony |
last post: by
|
5 posts
views
Thread by Eugene Anthony |
last post: by
|
2 posts
views
Thread by Josh Strickland |
last post: by
|
12 posts
views
Thread by Scott |
last post: by
|
3 posts
views
Thread by Suresh |
last post: by
|
5 posts
views
Thread by Wael |
last post: by
|
4 posts
views
Thread by stjulian |
last post: by
|
6 posts
views
Thread by SethM |
last post: by
| | | | | | | | | | |