SP works in QA but not ASP/ADO...

Question

SP works in QA but not ASP/ADO...

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
February 14, 2007 at 5:34 am

#153433

I'm running out of ideas for this one. I have an SP that does multiple selects into table variables and one into a local variable. The last select actually returns the data. It works fine in QA, but I do still get a message "1 rows affected" despite having "set nocount on" in the SP. When I invoke it in my ASP page it returns no data.
Any ideas?
Here's the SP:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go

CREATE
PROCEDURE [dbo].[GetLeaders]
(
@poolid
int,
@rid
int
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @seqrank TABLE (Points int, Rank int)

INSERT INTO @seqrank
SELECT A.Points, dbo.seqrank(1, A.Points) AS Rank
FROM (SELECT TOP 100 PERCENT PlaceNumber, MAX(TotalPoints) AS Points, COUNT(PlayerID) AS Ties
FROM dbo.playerStandings
WHERE PoolID=@poolid
GROUP BY PlaceNumber
ORDER BY PlaceNumber) as A

DECLARE @ranks TABLE (rid int identity(1,1), Playerid varchar(10), Name varchar(100), Location varchar(100), Score int, Rank int)

INSERT INTO @ranks
SELECT TOP 100 PERCENT
dbo.playerStandings.PlayerID,
LEFT(NCAAIPD.dbo.Visited.visited_fname, 1)+ '. ' + NCAAIPD.dbo.Visited.visited_lname AS Name,
NCAAIPD.dbo.Visited.visited_city + ', ' + NCAAIPD.dbo.Visited.visited_state AS Location,
MAX(dbo.playerStandings.TotalPoints) AS Score, R.Rank
FROM dbo.playerStandings LEFT OUTER JOIN
NCAAIPD
.dbo.Visited ON dbo.playerStandings.PlayerID = NCAAIPD.dbo.Visited.visited_id LEFT OUTER JOIN
@seqrank R
ON dbo.playerStandings.TotalPoints = R.Points
WHERE dbo.playerStandings.Poolid=@poolid
GROUP BY R.Rank, dbo.playerStandings.PlayerID,
LEFT(NCAAIPD.dbo.Visited.visited_fname, 1)+ '. ' + NCAAIPD.dbo.Visited.visited_lname, NCAAIPD.dbo.Visited.visited_city + ', ' + NCAAIPD.dbo.Visited.visited_state
ORDER BY R.Rank

if @rid is null select @rid=max(rid)-25 from @ranks

SELECT TOP 25 * FROM @ranks where rid>@rid

END

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

oldgoat SSCrazy Points: 2344 More actions · Answer 1

When you say it returns no data via asp, I assume you are using some error checking to make sure the returned data isn't actually an error (which would look the same as no data)?

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

I check the Return Code, the Recordcount and I dump the Parameters collection:

Paging records returned=-1 This is rs.recordcount

RC: : No Return Code (no "return" in SP)

Parms:3: Number of Parms (correct)

0=RETURN_VALUE-><-3 Parm 0=Return Code (empty) 3=integer

11=@poolid->1<-3 Parm 1=@poolid= Integer value=1

02=@rid->0<-3 Parm 2=@rid = Integer value=0

Is there anything else I can check?

oldgoat SSCrazy Points: 2344 More actions · Answer 3

My starting point on these is always to use (classic ASP this)

on error resume next

...

if err.number 0 then response.write err.description

I'd be inclined to be more explicit on the final 'select' and try with just one of the columns to make sure nothing is fouling and preventing the rest of the output.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

Tried both. Err.number=0 so there's no message. Changed the Select to return only the first column -- same results.

ANy idea why I'm still getting "(1 row(s) affected)" in QA when I run the SP even though I have SET NOCOUNT ON; in the SP?

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

The message above is from the

SELECT 'Return Value' = @return_value

in QA and has nothing to do with the actual execution of the SP. If I comment that out in QA and run the SP I get back "Command(s) completed successfully."

More mysterious...

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 6

Hi,

I noticed that in your original code you don't have the keyword RETURN. This is one of the sprocs templates I use frequently. Used for populating individual items in .NET. For populating a dataset to a datagrid or datalist you don't need the keywords OUTPUT;the SQL is SELECT field1, etc.. but still using the RETURN keyword

CREATE PROCEDURE Stored procedure name

(

@userid INT, -- INPUT PARAMETER

@UName VARCHAR(50) OUTPUT -- OUTPUT PARAMETER

)

AS

/***********************************************************************

************************************************************************

** Copyright Notice

**

** Procedure Name: Stored procedure name

** Description: Stored procedure definition **

**

** Return values:

** Input Params:

** Output Params:

** Author: <>

** Date: <>

************************************************************************

** Change History

************************************************************************

** Date: Author: Description:

** -------- --------

---------------------------------------

**

************************************************************************

********************************************************/

DECLARE @Error INT

SET NOCOUNT ON

BEGIN

-- Populates individual text items in asp.net OUTPUT keyword is required

SELECT @UName = uname,

FROM user_information_V WHERE loginID = @user-id

-- Populates a grid or datalist in asp.net OUTPUT keyword is not required

SELECT uname,fname,lname FROM user_information_V

END

RETURN

SET @Error = @@ERROR

IF @Error <> 0

BEGIN

RETURN @Error

END

RETURN @Error

These are two sprocs. Hope this helps

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 7

I added a RETURN at the end of my SP with no change. Thanks anyway!

David McFarland SSChampion Points: 11815 More actions · Answer 8

Is there a reason you have the semi-colon after SET NOCOUNT ON? If not, remove that and give it a shot. If that doesn't work, tell us how many recordsets ADO thinks it has after this call.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 9

Removing the semi-colon didn't matter, but my code was depending on RecordCount to proceed. I changed it to just ignore RecordCount and read until EOF and I got back my records.

The RecordCount must be off because of the Cursor. Since I'm using:

dim ttconn, pcmd, rs1 Set ttconn = Server.CreateObject("ADODB.Connection")
tconnect="Provider=SQLOLEDB.1;Password=ncaaipd;Persist Security Info=True;User ID=NCAAIPD;Initial Catalog=TurboTourneyLife;Data Source=sqlphtsc\htsc"
ttconn.Open tconnect
set pcmd=server.CreateObject("ADODB.Command") pcmd.ActiveConnection=ttconn pcmd.CommandType=adCmdStoredProc pcmd.CommandText="dbo.GetLeaders" pcmd.Parameters.append pcmd.CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue) pcmd.Parameters.append pcmd.CreateParameter("@poolid",adInteger,adParamInput,0,1)
pcmd.Parameters.append pcmd.CreateParameter("@rid",adInteger,adParamInput,0,0)
set rs1=pcmd.Execute

There's no way to set the cursor attributes (that I know of). Displaying them yields:

Type=ForwardOnly
Location=Server
LockType=ReadOnly

I'm OK with the Lock Type unless it also affects RecordCount.

Anyone know how to set Cursor and Lock values in the above code usage??

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 10

The following code will set the cursor and lock types and allow correct access to the RecordCount and Return Code (Parameters(0)):

set pcmd=server.CreateObject("ADODB.Command")
set rs1=server.CreateObject("ADODB.RecordSet")
pcmd.ActiveConnection=ttconn
pcmd.CommandType=adCmdStoredProc
pcmd.CommandText="dbo.GetLeaders"
pcmd.Parameters.append pcmd.CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue)
pcmd.Parameters.append pcmd.CreateParameter("@poolid",adInteger,adParamInput,0,1)
pcmd.Parameters.append pcmd.CreateParameter("@rid",adInteger,adParamInput,0,0)
rs1.CursorLocation=adUseClient
rs1.CursorType=adOpenStatic
rs1.CursorType=adLockReadOnly
rs1.Open pcmd