‘default to local cursor’ causes errors in string-built Declares

[Judith Farber Abraham]

Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

[Kristine Greenlee]

You will need to declare a global cursor in your exec statement.

'Declare SICCursor cursor GLOBAL FOR...'

When you use the exec statement, it's as if you are running that command in a separate query analyzer window. And, since you have the 'default to local cursor' option turned on, that cursor is local to that connection, the exec connection not your main connection.

------------
Judith Farber Abraham at 9/20/00 11:31:50 AM

Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith