Link to home
Start Free TrialLog in
Avatar of andyknott
andyknott

asked on

Calling Stored Procedure Problem

Hi,

I have a Stored Procedure that creates a temporary table from another recordset using a cursor. After returning a recordset from a query on the temp table, the temp table is then dropped.

Using hard coded input parameters in the SP I can successfully get the returned recordset in Query Analyser. The problem is when I call the procedure from an ASP page I get errors. I think it maybe a permissions problem but am not sure.

When I comment out the line that drops the temp table I can see that my attempts at calling the SP from ASP do populate the table.

I've tried the following methods to call the SP:

1.)

Set rs = cn.Execute("call {countBookings('18/02/02','10/03/02','50')}" )
ERROR : Syntax error or access violation

2.)

Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = cn
Cmd.CommandText = "countBookings()"
Cmd.CommandType = 4

Cmd.Parameters.Append = Cmd.CreateParameter("@startDay",7,1,20,CDate("18/02/02")) '7 is Date
Cmd.Parameters.Append = Cmd.CreateParameter("@endDay",7,1,20,CDate("10/03/02")) '7 is Date
Cmd.Parameters.Append = Cmd.CreateParameter("@recordsToGet",16,1,4,CInt(100)) '16 is tinyInt

Set rs = cmd.Execute

ERROR: Operation is not allowed when the object is closed.
 on line While not rs.EOF

3.)

Set rs = cn.Execute("countBooking ('18/02/02','10/03/02','50')" )

ERROR: Operation is not allowed when the object is closed.
 on line While not rs.EOF

The SP code is: (various test lines commented out)

Alter PROCEDURE countBookings

@startDay datetime,
@endDay dateTime,
@recordsToGet int

AS

DECLARE @bookingCount as int
DECLARE @tempCount as int
DECLARE @loopCount as int
DECLARE @userID as int
DECLARE @name as nvarchar(50)
DECLARE @guests as int

--DECLARE @startDay datetime
--DECLARE @endDay dateTime
--DECLARE @recordsToGet int

DECLARE bookingCursor CURSOR  For
SELECT [User_Id], [Name], No_Of_Guests
FROM minchinhamptonBookings
ORDER BY Name
FOR Read Only

--SET @startDay = DATEADD(dd,-10,getDate())
--SET @endDay = getdate()
--SET @recordsToGet = 5

     CREATE TABLE [tempBookingCount] (
          [User_Id] [int] NULL ,
          [Name] [varchar] (50)  NULL ,
          [Booking_Count] [int] NULL ,
     )


OPEN bookingCursor
WHILE (0 = 0) Begin
    FETCH NEXT
    FROM bookingCursor
    INTO @userID, @name, @guests
    IF (@@Fetch_Status <> 0) BREAK
    IF EXISTS (SELECT [User_Id] FROM tempBookingCount WHERE [User_Id] = @userId) begin
        SET @tempCount = (SELECT Booking_Count FROM tempBookingCount WHERE [User_Id] = @userId)
     --SET @tempUserCount = (SELECT Booking_Count FROM tempBookingCount WHERE [User_Id] = @userId)
        UPDATE tempBookingCount SET Booking_Count = @tempCount  + 1 WHERE [User_Id] = @userId
    END
    ELSE Begin
        INSERT INTO tempBookingCount ([User_ID],[Name],Booking_Count) values(@userID,@name,1)
        SET @loopCount = @loopCount + 1
    END
    IF @loopCount > @recordsToGet BREAK
END
CLOSE bookingCursor
DEAllOCATE bookingCursor
SELECT * FROM tempBookingCount ORDER By Booking_Count DESC
DROP TABLE tempBookingCount

Any Ideas how why there are errors? The ASP code is ok to get a recordset from standard query and the SP has the right results in Query analyser and the temp table.

Thank in advance,

Andrew
Avatar of andyknott
andyknott

ASKER

Oops I mean:

Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = cn
Cmd.CommandText = "countBookings"
Cmd.CommandType = 4
how I do it:

If Not DatabaseOpen Then
    blnOpenedDatabase     = true
    Call OpenDatabase()
End If
     
Set i_objRecordset     = ExecuteSQL("Execute " & strProcedure)

If blnOpenedDatabase
    Call CloseDatabase()
End If

Function ExecuteSQL(i_strSQL)
     On Error Resume Next
     
     if i_g_objConnection.State = adStateOpen then
          ' open the recordset based on the SQL call
          if i_strSQL <> "" then
               ' attempt to open the recordset based on the
               ' SQL statement passed in
               Set i_g_objRecordset = i_g_objConnection.Execute(i_strSQL, ,adCmdText)
          else
               I_WriteError "There was no request to retrieve information"
          end if

          ' check and see if there were any database errors
          Call I_CheckDBErrors(i_strSQL)
     else
          I_WriteError "Database connection not opened"
     end if
     
     Set ExecuteSQL = i_g_objRecordset
End Function


if yours looks similer then you might have access violations with the user id you log into the database as from the web (that you may not have in Query Analyser [assuming you're sa in QA])

Hope this helps,

Evan
Avatar of fritz_the_blank
Does the IUSR account have access to call the stored procedure?

Fritz the Blank
it shouldn't be the IUSR account, unless you are not logging into SQL Server when you do the database connection
Good point. What is the logon info in the connection string?

Fritz the Blank
Hi,

I'm logging on as 'sa from asp page aswell as query analyser.

connectionStr = "Provider=SQLOLEDB.1;Initial Catalog=teeTime;Data Source=kx1; User Id=sa; PASSWORD=*******; "

Seems odd doesn't it.

Thanks
have you tried running my code (specific to your situation) against the database?

Set i_g_objRecordset = i_g_objConnection.Execute("EXECUTE countBookings '18/02/02','10/03/02','50'", ,adCmdText)

ASKER CERTIFIED SOLUTION
Avatar of ruperts
ruperts

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

ebosscher:

I Tried
Set rs = cn.Execute("EXECUTE countBookings '18/02/02','10/03/02','50'", ,&H0001 ) 'adCmdText = &H0001

and got Error : Operation is not allowed when the object is closed. On the line where the recordset is first used.

I've not tried your code exactly, but it is only error handling around the above call right?


Andrew
Thanks anyway. Any other suggestions?
Hi Ruperts,

Thanks! That fixed it.

>> set noCount on

What does it mean / do?

Thanks again.

Andrew
Say you run the SP in QA (query analyser) you'll get output such as..

x Record(s) affected.

y Record(s) affected.

idField strValue
------- --------
1       Apple
2       Pear


you don't need to know how many records are affected in each of the statements, so you can supress these messages by using "set nocount on" - and if you want these messages use "set nocount off" - see more in "MS books online".

Alternatively if you know how many "dummy" recordsets there are before your data, you should be able to use (i.e for the above example:

set rs = conn.execute(SQL)
'moveto next recordset
set rs = rs.nextRecordSet
'moveto next recordset
set rs = rs.nextRecordSet
do until rs.eof
 response.write rs.fields("strValue").value
 rs.movenext
loop

Secondly your temp table should be prefixed with #

i.e.

CREATE TABLE [#tempBookingCount] (
         [User_Id] [int] NULL ,
         [Name] [varchar] (50)  NULL ,
         [Booking_Count] [int] NULL, etc...etc..
   

select * from #tempBookingCount

Why?

Because say 2 people run the SP at the same time then the second SP which tries to create the table will raise an error. When prefixed with "#" - a new copy of the table is created for each connection to the db.
Hi Ruperts,

Thanks for the extra effort. I'm very grateful.

I get what your saying - funny, I justed manually added an extra rs to give me the record count when it was there all along!

I'll add the '#'.

I have another question which I'll post in abit - it invloves making the above SP work with a table name parameter passed in.

Thanks,

Andrew
I'll post my next Q in the SQL area of EE