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("@star tDay",7,1, 20,CDate(" 18/02/02") ) '7 is Date
Cmd.Parameters.Append = Cmd.CreateParameter("@endD ay",7,1,20 ,CDate("10 /03/02")) '7 is Date
Cmd.Parameters.Append = Cmd.CreateParameter("@reco rdsToGet", 16,1,4,CIn t(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
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',
ERROR : Syntax error or access violation
2.)
Set Cmd = Server.CreateObject("ADODB
Cmd.ActiveConnection = cn
Cmd.CommandText = "countBookings()"
Cmd.CommandType = 4
Cmd.Parameters.Append = Cmd.CreateParameter("@star
Cmd.Parameters.Append = Cmd.CreateParameter("@endD
Cmd.Parameters.Append = Cmd.CreateParameter("@reco
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_
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
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
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(
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
Does the IUSR account have access to call the stored procedure?
Fritz the Blank
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
Fritz the Blank
ASKER
Hi,
I'm logging on as 'sa from asp page aswell as query analyser.
connectionStr = "Provider=SQLOLEDB.1;Initi al Catalog=teeTime;Data Source=kx1; User Id=sa; PASSWORD=*******; "
Seems odd doesn't it.
Thanks
I'm logging on as 'sa from asp page aswell as query analyser.
connectionStr = "Provider=SQLOLEDB.1;Initi
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)
Set i_g_objRecordset = i_g_objConnection.Execute(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ebosscher:
I Tried
Set rs = cn.Execute("EXECUTE countBookings '18/02/02','10/03/02','50'
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?
ASKER
Hi Ruperts,
Thanks! That fixed it.
>> set noCount on
What does it mean / do?
Thanks again.
Andrew
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").valu e
rs.movenext
loop
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").valu
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.
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.
ASKER
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
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
ASKER
I'll post my next Q in the SQL area of EE
ASKER
Set Cmd = Server.CreateObject("ADODB
Cmd.ActiveConnection = cn
Cmd.CommandText = "countBookings"
Cmd.CommandType = 4