ASP error with storedprocs w/ EXEC & sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP error with storedprocs w/ EXEC & sp_executesql

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-27 : 11:47:10

Hello. I have an ASP project that uses many stored procedures to return recordsets. Now, I need a recordset that comes from a dynamic SQL string. My first thought was I wanted to keep it in a stored procedure for a couple reasons. After asking/looking around, this seemed to be the best way to go.

I have tried a couple different forms of dynamic stored procedures. I have created stored procedures that make use of EXEC() and sp_executesql. I can get both to work in Query Analyzer. As soon as I try to call a stored procedure that makes use of either in ASP I get an error. By commenting and uncommenting some lines I get a couple different errors, but they all look like the recordset is closed.

I have used this same exact code to call other stored procedures. These other stored procedures also accepted parameters. At this point, I have made a static SQL string in my stored procedure (that uses EXEC or sp_executesql) and it still won't work so I know the error isn't created by my input parameters because I'm not using any more.

I feel like I'm missing something obvious. Like there's one little thing that will put me over the hump. I can show you my similified code, but I think I'm missing something in my transition from static stored procedures with a few inputs to a dynamic stored procedure that builds a SQL string.

I would appreciate any help or ideas you can provide since I've been hunting a solution for awhile now. Thanks again.

Huligan

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 12:39:59

Are you checking for the EOF and BOF... What is the error code returned

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 12:46:17

I am guessing you are using the command object to create the sp when you are dynamically building the sp... Have you tried placing a break point prior to the sp create statement and placing that text into Query Analyzer to validate the sp creation...

I would do this then run the sp from QA and see if you are returned results... The error messages may be more telling as they will be generated specifically from SQL Server and not interpreted by ASP error object which can be generalized...

Is this ASP or ASP.Net?

Surf On Dude!

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 13:12:11

what error messages are you getting? is an ado error or an sql error. Keep in mind sp_executesql is a stored procedure. So you would execute it the same way as a stored procedure.

In asp.net you would do something like this.

// tell command object to be of type stored procedure
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "sp_executesql";
Command.Parameters.Add("@Params",SqlDbType.Varchar,50).Value =
"@param1 int, @param2 int";

Command.Parameters.Add("@stmt", SqlDbType.Varchar, 1000).Value =
sqlStatement;

// here you would add all of your parameters to the command object

Its been ages since ive used classic asp.. But The same concepts apply.

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 13:15:02

Oh something just occured to me too. In asp.net the SqlCommand object uses sp_executesql behind the scenes. So its pointless to use it because it does it for you..

You still have to use parameters for it to work though such as...

sql = "select dbo. from dbo.table where dbo.table.field=@field"

also you have to fully qualify your table names or sp_executesql wont cache the execution plan.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 13:50:45

Don't you love when someone makes a post then disapears...

Surf On Dude!

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-27 : 13:57:37

To clarkbaker1964: Thanks for the help. I am checking for RS1.EOF. If I run the code below as is, I get an error at line 14 ("Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."). If I comment out line 14, I get an error at line 17 ("Operation is not allowed when the object is closed."). I get errors on lines that reference the recordset and it looks as if the recordset is closed.

As for your second set of questions, I am running this in ASP not ASP.net. I don't understand what you mean by "placing a break point prior to the sp create statement and placing that text into Query Analyzer to validate the sp creation..." I will tell you that I have both of my dynamic stored procedures are running in Query Analyzer. I have two stored procedures I'm playing with. I have a full-blown production dynamic storedproc with all of the trimmings and a testing stored procedure with a hard coded SQLString "Select OneField FROM tblTest" that is fired with EXEC or sp_executesql. Both work in Query Analyzer and neither work called from ASP.

----------------------------
ASP code:

1 'Build connection
2 Set THISONE = server.CreateObject ("ADODB.Connection")
3 THISONE.Open Application("THISONE_ConnectionString")
4
5 Set objCmd = Server.CreateObject("ADODB.Command")
6 objCmd.ActiveConnection = THISONE
7 objCmd.CommandType = adCmdStoredProc
8 objCmd.CommandText = "sp_test"
9
10 'Build recordset
11 Set RS1 = Server.CreateObject("ADODB.Recordset")
12 RS1.CursorLocation = adUseClient
13 RS1.Open objCmd,,adOpenForwardOnly,adLockBatchOptimistic
14 Set RS1.ActiveConnection = Nothing
15 Set objCmd = Nothing
16
17 IF RS1.EOF THEN
18 ELSE
19 vDataField = RS1.Fields("DataField").Value
20 END IF
21
22 'Close recordset
23 RS1.Close()
24 Set RS1 = Nothing
25
26 'Close connection
27 THISONE.Close()
28 Set THISONE = Nothing

Thanks again.

Huligan

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-27 : 13:58:42

Sorry for the slow reply. I wanted to make sure I was detailed in my response. Now I'm working on my reply to slacker.

Huligan

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:03:31

Should'nt you be setting RS1.AcitiveConnectino = THISONE

I usually open my rs with
adoCnn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=ClarkB;Initial Catalog=SomedbName;Data Source=SomeServerName"
adoCnn.Open adoCnn.ConnectionString, "MyName", "MyPassword"

Set rs = New ADODB.Recordset
rs.Open sSqlTables, adoCnn

don't forget to rs.close

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:06:53

Maybe I am seeing things but the RS Connection has not been opened prior to setting Nothing

Surf On Dude!

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 14:08:18

quote:

[i]


----------------------------
ASP code:

14  Set RS1.ActiveConnection = Nothing

Its telling you that you cannot change the connection property in your recordset. Even though you are setting to nothing you still need to handle it your command object.

Your problem is line 14 and 15. Where you set your command and connection objects to nothing.. this basically is closing your connection.. These objects you are assigning

THISONE
RS1.ActiveConnection
objCommand.ActiveConnection

if you set any one of these to nothing you are basically killing THISONE. Same with your objCmd. So basically.. remove line 14 and 15 and it should work.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:09:50

Try this
rs.Open objCmd, objCmd.ActiveConnection, adOpenForwardOnly, adLockBatchOptimistic

Surf On Dude!

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 14:13:11

quote:
Originally posted by clarkbaker1964

Try this
rs.Open objCmd, objCmd.ActiveConnection, adOpenForwardOnly, adLockBatchOptimistic

Surf On Dude!

when he does this...
15 Set objCmd = Nothing

its closing out the connection and command ( implied by default in iis5 now.. when you set to nothing.. Close() is called ) and hense.. the recordset is closing too. And since he is using a forward only cursor... no data is retrieved.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:13:30

I think the connection on the rs object is not opened prior to using the object... Its ok to close the connection after the recordset is populated... Best Practice, check to see if you have apples and oranges, the command object and the rs object on not interconnected objects.

Surf On Dude!

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 14:14:58

quote:
Originally posted by clarkbaker1964

I think the connection on the rs object is not opened prior to using the object...

line 13

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 14:15:30

quote:
Originally posted by clarkbaker1964

I think the connection on the rs object is not opened prior to using the object...

line 13... and its using a forward only cursor so nothing gets populated until he calls movenext

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:18:21

quote:
Originally posted by slacker

quote:
Originally posted by clarkbaker1964

Try this
rs.Open objCmd, objCmd.ActiveConnection, adOpenForwardOnly, adLockBatchOptimistic

Surf On Dude!

when he does this...
15 Set objCmd = Nothing

its closing out the connection and command ( implied by default in iis5 now.. when you set to nothing.. Close() is called ) and hense.. the recordset is closing too. And since he is using a forward only cursor... no data is retrieved.

I need more coffee I do not see:
Set objCmd = Nothing
Are you sure... The connection object is enstanciated independent of the command object. Closing the command object does not affect the Connection Object untill the procedure goes out of scope. Right???

Surf On Dude!

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-27 : 14:20:47

To slacker: Thanks for your help. Both errors I reported to clarkbaker1964 are ADO (ADODB.Recordset (0x800A0E7B)). My assumption is the same as yours. I'm using the same VBScript to call my storedproc as I have on all of my other pages. This one is different because the stored procedure is built differently because the SQL statement is assembled in the storedproc. My simplified stored procedure is below. This one uses EXEC(). I have also created one with sp_executesql. I would prefer to use sp_executesql but they are pretty similar in syntax so I just copied the last test storedproc I ran. My production stored procedure creates a long where statement.

------------------------
Stored procedure:

CREATE PROCEDURE [sp_test]
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1000)

-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = "SELECT OneField FROM tblTest"

-- Execute the SQL statement
EXEC(@SQLStatement)
GO

Thanks.

Huligan

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:21:04

If the command object is closed you can pass the same code using
rs.Open objCmd, THISONE, adOpenForwardOnly, adLockBatchOptimistic

Which is only good if you havnt closed the Connection Object.

Good points Slacker
I feel all funny inside when I reply to you as Slacker... [;-)]

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:21:32

quote:
Originally posted by clarkbaker1964

If the command object is closed you can pass the same code using
rs.Open objCmd, THISONE, adOpenForwardOnly, adLockBatchOptimistic

Which is only good if you havnt closed the Connection Object.

Good points Slacker
I feel all funny inside when I reply to you as Slacker...

Surf On Dude!

Surf On Dude!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 14:23:01

quote:
Originally posted by clarkbaker1964

[quote]Originally posted by clarkbaker1964

If the command object is closed you can pass the same code using
rs.Open objCmd, objCmd.ActiveConnection, adOpenForwardOnly, adLockBatchOptimistic

Surf On Dude!

rs.Open objCmd, THISONE, adOpenForwardOnly, adLockBatchOptimistic
Which is only good if you havnt closed the Connection Object.

Good points Slacker
I feel all funny inside when I reply to you as Slacker...

Surf On Dude!

Surf On Dude!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources