SQL Server nested cursor problem

Question

I have a strange problem with my nested cursors and I have no idea what it's all about.

Here's my T-SQL code:

declare @dbname varchar(50)
declare @servername varchar(50)
declare srv cursor for select servername from test.dbo.servers
declare @str varchar(200)

truncate table test.dbo.temp

open srv
fetch next from srv into @servername
while @@fetch_status = 0   
begin   
   set @str = 'Data Source='+@servername+';Integrated Security=SSPI'   
   declare db cursor for select name from opendatasource('SQLNCLI', @str).master.dbo.sysdatabases
   open db
   fetch next from db into @dbname
   while @@fetch_status = 0
   begin
      insert test.dbo.temp (dbname, servername) values (@dbname, @servername)
      fetch next from db into @dbname
   end
   fetch next from srv into @servername
   close db
   deallocate db
end   
close srv
deallocate srv

It gives me next error message:

Incorrect syntax near '@str'. [SQLSTATE 42000] (Error 102)

Looks like the problem is in giving the variable as a parameter to opendatasource function. But why? And how to avoid this problem?

@marc_s I think that a nested cursor is the only viable way of doing what the OP wants to do (loop through all databases in a collection of servers, the names of which are contained in a table) — Martin Smith
– Martin Smith, Commented Dec 8, 2010 at 14:06

Phil Hunt · Accepted Answer · 2010-12-08 12:48:35Z

3

You are correct that variables cannot be passed to OPENDATASOURCE. Instead You must use a literal instead. As much as we discourage using dynamic SQL, there are some cases that it is unavoidable. Try something like this:

declare @dbname varchar(50)
declare @servername varchar(50)
declare srv cursor for select servername from test.dbo.servers
declare @str varchar(200)
declare @sql nvarchar(MAX)

truncate table test.dbo.temp

open srv
fetch next from srv into @servername
while @@fetch_status = 0   
begin
   SET @sql = N'
   declare db cursor for select name from opendatasource(''SQLNCLI'', ''Data Source='+@servername+';Integrated Security=SSPI'').master.dbo.sysdatabases
   open db
   fetch next from db into @dbname
   while @@fetch_status = 0
   begin
      insert test.dbo.temp (dbname, servername) values (@dbname, @servername)
      fetch next from db into @dbname
   end
   close db
   deallocate db
   '
   EXEC sp_executesql
    @sql,
    N'@dbname     varchar(50),
      @servername varchar(50)',
    @dbname,
    @servername

   fetch next from srv into @servername
end   
close srv
deallocate srv

edited Dec 8, 2010 at 12:48

answered Dec 8, 2010 at 12:19

Phil Hunt

8,5711 gold badge33 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stee1rat Over a year ago

Thank you very much! I didn't thought that way! :) it's really helped!

JNK · Accepted Answer · 2010-12-08 14:54:24Z

1

If you need to use nested cursors, you are doing something wrong. There are very few reasons to use a cursor instead of some other set-based operation, and using a cursor within a cursor is like the ultimate SQL Server anti-pattern.

For your inner cursor, you could change it to use the undocumented sp_msforeachdb function (which apparently creates a cursor behind the scenes):

open srv
fetch next from srv into @servername
while @@fetch_status = 0   
begin
 EXEC sp_msforeachdb '
 Data Source='+@servername+';Integrated Security=SSPI
 insert test.dbo.temp (dbname, servername) values (?, @Servername)'
 fetch next from srv into @servername
end   
close srv
deallocate srv

You may need to enclose the ? in single quotes and escape them, like:

EXEC sp_msforeachdb 'insert test.dbo.temp (dbname, servername) values (''?'', @Servername)

edited Dec 8, 2010 at 14:54

answered Dec 8, 2010 at 13:38

JNK

65.5k15 gold badges127 silver badges138 bronze badges

5 Comments

Martin Smith Over a year ago

er, I take it that you haven't looked at the definition of sp_msforeachdb then? (It uses a cursor!)

JNK Over a year ago

@Martin - lol I didn't know that's how it operated internally. Thanks for the info.

Martin Smith Over a year ago

@JNK - I guess there may be an argument for using it still though (+1). It takes some of the complexity out of the OP's code and does some additional checks that the database is accessible.

Phil Hunt Over a year ago

I like that you use sp_msforeachdb, but I'm not sure how this makes the connections to the remote servers.

JNK Over a year ago

@Phil - Good point, I erroneously left that out. Corrected above.

Collectives™ on Stack Overflow

SQL Server nested cursor problem

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related