Solved

Calling Stored Procedure Problem

Posted on 2002-03-13
14
252 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:andyknott
  • 6
  • 3
  • 3
  • +1
14 Comments
 

Author Comment

by:andyknott
ID: 6862001
Oops I mean:

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

Expert Comment

by:ebosscher
ID: 6862029
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6862047
Does the IUSR account have access to call the stored procedure?

Fritz the Blank
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6862058
it shouldn't be the IUSR account, unless you are not logging into SQL Server when you do the database connection
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 6862071
Good point. What is the logon info in the connection string?

Fritz the Blank
0
 

Author Comment

by:andyknott
ID: 6862095
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
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6862114
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)

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Accepted Solution

by:
ruperts earned 100 total points
ID: 6863070
try this..

Alter PROCEDURE countBookings

@startDay datetime,
@endDay dateTime,
@recordsToGet int
AS
--NEW LINE
set noCount on
---REST OF SP...
0
 

Author Comment

by:andyknott
ID: 6863119
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?
0
 

Author Comment

by:andyknott
ID: 6863122
Hi Ruperts,

Thanks! That fixed it.

>> set noCount on

What does it mean / do?

Thanks again.

Andrew
0
 
LVL 4

Expert Comment

by:ruperts
ID: 6864385
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

0
 
LVL 4

Expert Comment

by:ruperts
ID: 6864398
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.
0
 

Author Comment

by:andyknott
ID: 6864563
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
0
 

Author Comment

by:andyknott
ID: 6864573
I'll post my next Q in the SQL area of EE
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now