Operation is not allowed when the object is closed error when using exec(@sql)

[mwhite]

I'm trying to return a dynamically created recordset from a SQL Server stored proc using exec (@sql). I'm getting the object is closed error. It runs fine in Query Analyzer and my ADO code ran fine with a different stored proc before changes necessitated dynamic SQL. There are no Print statements in the proc and I don't insert any records so SET NOCOUNT is not the solution (tried it anyway). Is there something else I'm not seeing? I also tried exec Sp_executesql @strSQL but got an error saying that my variable is not a varchar, which it is.

TIA,

Mike

Here's the code:

---------------stored proc------------------
alter proc pr_rptProjectNotesTEST (

@PPNo smallint,
@ProjectNo varchar(10) = null,
@ProjType varchar(5) = null,
@ProjName varchar(30) = null

)
as

--set nocount on

declare @strSQL varchar(400)
declare @strWhere varchar(200)

set @strSQL = 'select username, ProjectNo, LaborHours, Note, DayDate
from tblTCUsers u
inner join tbltimeCards c on u.EmpID = c.EmpID
inner join tbltimeEntry t on c.TimecardID = t.TimecardID
inner join tblDays d on t.DayID = d.DayID '


set @strWhere = 'where '

if @ProjectNo is not null
set @strWhere = @strWhere + 't.ProjectNo = ''' + @ProjectNo + ''' and '

if @ProjType is not null begin
set @strWhere = @strWhere + 't.ProjectType = ''' + @ProjType +
''' and t.ProjNameNo = ''' + @ProjName + ''' and '
end

set @strWhere = @strWhere + 'len(note) > 0
and c.PPNo = ' + cast(@PPNo as varchar(3)) +
' order by username, DayDate'

set @strSQL = @strSQL + @strWhere

exec (@strSQL)


------------------VB ADO--------------------------

Function RunProjectNotesRpt(intPPno As Integer, Optional strProjNo As String, Optional strProjType As String, Optional strProjName As String)
Dim db As Database
Dim rsADO As ADODB.Recordset
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strCnn As String

Set db = CurrentDb
db.Execute "delete from tblProjectNotesRpt_local"

Set rsADO = New ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
strCnn = SQLConn()
cnn.Open strCnn

cmd.ActiveConnection = cnn
cmd.CommandText = "pr_rptProjectNotesTEST"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@ProjectNo", adVarChar, adParamInput, 10, strProjNo)
cmd.Parameters.Append cmd.CreateParameter("@ProjType", adVarChar, adParamInput, 5, strProjType)
cmd.Parameters.Append cmd.CreateParameter("@ProjName", adVarChar, adParamInput, 30, strProjName)
rsADO.Open cmd

Do Until rsADO.EOF '----ERROR OCCURRING HERE------------------
strSQL = "Insert into tblProjectNotesRpt_local (UserName, ProjectNo, LaborHours, Notes, DayDate) " & _
"values ('" & rsADO("Username") & "', '" & rsADO("ProjectNo") & "', " & rsADO("LaborHours") & ", '" & RemoveSingleQuotes(rsADO("Note")) & "', #" & rsADO("DayDate") & "#)"
db.Execute strSQL
rsADO.MoveNext
Loop

rsADO.Close
cnn.Close
Set rsADO = Nothing
Set cnn = Nothing
End Function

[Spudhead]

Hmm. Weird. If you google EOF "Operation is not allowed" (http://www.google.co.uk/search?q=EOF+%22Operation+is+not+allowed%22), a few suggestions turn up.

- use the connection to create the recordset instead of declaring it explicitly - ie: Set rsADO = cnn.execute cmd

- trap the error by wrapping your EOF check in a recordset.state check:
If rsADO.State = adStateOpen Then
If Not rsADO.EOF Then

Either of those help?

[mwhite]

Thanks for your reply. I tried the execute method but it requires a string where you have cmd, like the name of the proc. If I do that the parameters would not get passed.
I can trap the error but since the proc should be returning records(it does if I run it in SQL Analyzer) I need figure out why SQL Server is not returning the recordset when I use exec(@SQL).

[Whatever Jr.]

Hi,

can't run your code at the moment, but maybe this'll help.

- You don't need the hashes in strSQL around the date.
- Maybe your variable strWhere exceeds 200 chars.

HTH, Tom

[mwhite]

The thing is the procedure works fine run within Analyzer and the vb worked fine with a different stored proc that returned a recordset just using a select statement, not the exec(@strSQL). So I think the issue is with using exec(@strSQL). This seems similar to having a Print statement within a stored proc. It just won't return the recordset to an ADO call.