Solved

Calling Stored Procedure Problem

Posted on 2002-03-13
14
251 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
Comment Utility
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
Comment Utility
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
Comment Utility
Does the IUSR account have access to call the stored procedure?

Fritz the Blank
0
 
LVL 6

Expert Comment

by:ebosscher
Comment Utility
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
Comment Utility
Good point. What is the logon info in the connection string?

Fritz the Blank
0
 

Author Comment

by:andyknott
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Accepted Solution

by:
ruperts earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Ruperts,

Thanks! That fixed it.

>> set noCount on

What does it mean / do?

Thanks again.

Andrew
0
 
LVL 4

Expert Comment

by:ruperts
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'll post my next Q in the SQL area of EE
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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/…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

10 Experts available now in Live!

Get 1:1 Help Now