Solved

Calling Stored Procedure Problem

Posted on 2002-03-13
14
254 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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