SQL Server curseur imbriqué problème

J'ai un problème étrange avec mon curseurs imbriqués et je n'ai aucune idée de quoi il s'agit.

Voici mon code T-SQL:

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

Il me donne le message d'erreur suivant:

Syntaxe incorrecte près de '@str'.
[SQLSTATE 42000] (Erreur 102)

Dirait que le problème est dans le don de la variable comme paramètre à la fonction opendatasource. Mais pourquoi? Et comment éviter ce problème?

  • curseur imbriqué : c'est votre problème là!!
  • Je pense qu'un curseur imbriqué est le seul moyen viable de faire ce que l'OP veut faire (en boucle par toutes les bases de données en un ensemble de serveurs, dont les noms sont contenus dans un tableau)
InformationsquelleAutor stee1rat | 2010-12-08
  1. 3

    Il est exact que les variables ne peuvent pas être passés à OPENDATASOURCE. Au lieu de cela, Vous devez utiliser un littéral à la place. Autant que nous déconseillons l'aide de SQL dynamique, il y a certains cas qu'il est inévitable. Essayez quelque chose comme ceci:

    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
    • Merci beaucoup! Je n'ai pas pensé de cette façon! 🙂 c'est vraiment aidé!
    • Vous êtes les bienvenus!
    InformationsquelleAutor Phil Hunt
  2. 1

    Si vous devez utiliser des curseurs imbriqués, vous faites quelque chose de mal. Il y a très peu de raisons d'utiliser un curseur au lieu d'un ensemble de base de fonctionnement, et à l'aide d'un curseur à l'intérieur d'un curseur, c'est comme l'ultime SQL Server anti-modèle.

    Pour l'intérieur de votre curseur, vous pouvez le changer pour utiliser le sans-papiers sp_msforeachdb fonction (qui, apparemment, crée un curseur en coulisses):

    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

    Vous pouvez avoir besoin de joindre l' ? dans des guillemets simples et leur échapper, comme:

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

    • re, j'en déduis que vous n'avez pas regardé la définition de sp_msforeachdb alors? (Il utilise un curseur!)
    • lol je ne sais pas c'est comment elle a géré en interne. Merci pour l'info.
    • Je pense qu'il y a peut être un argument pour l'utiliser encore bien (+1). Il prend une partie de la complexité de l'OP du code et fait quelques vérifications supplémentaires de la base de données est accessible.
    • J'aime que vous utilisez sp_msforeachdb, mais je ne suis pas sûr de savoir comment cela rend les connexions aux serveurs distants.
    • Bon point, j'ai tort, n'. Corrigé ci-dessus.
    • Êtes-vous sûr?

    InformationsquelleAutor JNK