Re: Problem opening stored procedure that contains cursor

Pier-Paolo wrote:
SET NOCOUNT ON

did not function !

In order to reproduce the error I think that is enough to create a
stored procedure that use a SQL CURSOR to create a temporary table.

a part of code is below:

DECLARE aux_cursor CURSOR FOR <sql statement>


CREATE TABLE #CrossTabBam(NumOrder CHAR(35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL, ItemID INT NOT NULL,
CrossBamText VARCHAR(4000))
ALTER TABLE #CrossTabBam ADD CONSTRAINT PK_CrossTabBam PRIMARY KEY
CLUSTERED (NumOrder, ItemID)

Is this really your code? I don't see an "OPEN aux_cursor" statement ...



FETCH NEXT FROM aux_cursor

<snip>

Here is how I've attempted to reproduce your problem (to no avail - it
works fine):

CREATE TABLE dbo.tbltest
(
col1 char(1) NOT NULL,
CONSTRAINT PK_tbltest
PRIMARY KEY CLUSTERED (col1)
go
INSERT INTO dbo.tbltest ( col1 )
VALUES ( 'd' )
go
INSERT INTO dbo.tbltest ( col1 )
VALUES ( 'e' )
go

CREATE PROCEDURE dbo.Test
AS
BEGIN
set nocount on
create table #t (col1 varchar(5))
declare cur cursor for select col1 from tbltest
declare @col1 varchar(5)
open cur
fetch next from cur into @col1
WHILE (@@FETCH_STATUS <> -1)
BEGIN
if @@FETCH_STATUS <> -2
BEGIN
insert into #t values(@col1 + 'tst')
END
fetch next from cur into @col1
END
close cur
deallocate cur
select col1 from #t
END
go

Private Sub Form_Load()
Set myCmd = New ADODB.Command
Set myCmd.ActiveConnection = CurrentProject.Connection
myCmd.CommandType = adCmdStoredProc
myCmd.CommandText = "Test"
Dim rRec As ADODB.Recordset
Set rRec = New ADODB.Recordset
rRec.CursorLocation = adUseClient
rRec.Open myCmd
Set Me.subfrm_Item.Form.Recordset = rRec
End Sub


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.