Variable in Cursor declaration

[balor]

How do I use a variable in the FOR expression for a cursor.

I want to do something like this:

SET @sSQL = 'SELECT *
FROM ' + @sLinkedDBName + '...Table1'

DECLARE TheCursor CURSOR
FOR @sSQL

OPEN TheCursor
.
.
.


How do I use the "@sLinkedDBName" - variable in the FOR expression? It does not like when i first set it in a variable and then use that variable in the expression.

//Balor

 

[jhall156]

Are you using a linked server? In any case have you tried using OPENQUERY()? JHall

 

[balor]

You can't use variables in OPENQUERY() either.

I'm using 21 allmost identical linked Access databases. Right now I have the declaration of the cursor and the "FOR"-SQL code written 21 times. The code is identical except for the linked server name. I should want to use a variable for the server name instead.

 

[jhall156]

I tried this and it works but you have to create global cursors

DECLARE @Server varchar(50)
DECLARE @SQLString varchar(2048)

SELECT @Server = 'Yourserver'
SELECT @SQLString = 'DECLARE TheCursor CURSOR GLOBAL FOR ' +
'SELECT yourfieldlist FROM OPENQUERY(' + @Server + ', ' + CHAR(39) + 'your select goes here' + CHAR(39) + ')'

EXEC(@SQLString)
OPEN TheCursor
do other stuff here
CLOSE TheCursor
DEALLOCATE TheCursor
JHall