sp_executesql Operation is not allowed when the object is closed

[cliverama]

help! fried brains....

asp calling a sqlserver7 stored proc which dynamically builds a
sqlstatement & passes it to sp_executesql
asp page gives the operation not allowed when object is closed error
this is the asp code:

Set connInc= server.CreateOb ject("ADODB.Con nection")
connInc.Open "DSN=db_databas e;User ID=userid;Passw ord=xxxxxx"
Set rsInc= server.CreateOb ject("ADODB.Rec ordset")
strSQL = "usp_incmpwfilt er_rs 'M9S','2','1',' A,B'" '-these are
string variables passed dependant on asp form selections.
rsInc.Open strSQL, connInc
if not rsInc.eof then ^falls over here

usp_incmpfilter _rs builds a dynamic sql statement which works in query
analyser and passes it to sp_executesql:

CREATE PROCEDURE usp_incmpwfilte r_rs
(
@strPeriodID varchar ,
@intLevelDetail ID varchar,
@intLevelReport ID varchar,
@strFilters varchar
)
AS

set nocount on

declare @strSQL nvarchar

set @strSQL = " SELECT rather complex dynamically built but working in
query analyser SQL statement containing @params goes here: can post if
requested"

execute sp_executesql @strSQL

what am i missing? i have had right nitemare getting this far!!!! any
help much appreciated..

[Aaron Bertrand [MVP]]

> Set connInc= server.CreateOb ject("ADODB.Con nection")

connInc.Open "DSN=db_databas e;User ID=userid;Passw ord=xxxxxx"
Set rsInc= server.CreateOb ject("ADODB.Rec ordset")
strSQL = "usp_incmpwfilt er_rs 'M9S','2','1',' A,B'" '-these are
string variables passed dependant on asp form selections.
rsInc.Open strSQL, connInc
if not rsInc.eof then ^falls over here
You might have better luck trapping EOF by using the connection object
instead of an explicit ADODB.Recordset .

Set connInc = CreateObject("A DODB.Connection ")
connInc.Open "DSN=db_databas e;User ID=userid;Passw ord=xxxxxx"
' consider dropping the DSN! See www.aspfaq.com/2126
strSQL = "usp_incmpwfilt er_rs 'M9S','2','1',' A,B'"
set rsInc = connInc.execute (strSQL)
if not rsInc.EOF then
response.write "there are rows"
else
response.write "there are no rows"
end if
set @strSQL = " SELECT rather complex dynamically built but working in
query analyser SQL statement containing @params goes here: can post if
requested"

As complex is it might be, we're generally pretty smart people, and can
figure it out. Also, could you

PRINT @strSQL

And show us that? (The result, I would think, is far more important than
the method.) It might be that your SQL is malformed, etc.

Also, does this return 1 resultset, 5 resultsets, is it ever possible that
it could be EOF?

microsoft.publi c.inetserver.as p.general dropped from followups.