SQL Server cursore nidificata problema

Ho uno strano problema con il mio cursori nidificati e non ho idea di cosa si sta parlando.

Ecco il mio codice 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='[email protected]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

È per me un successivo messaggio di errore:

Sintassi non corretta in prossimità di ‘@str’.
[SQLSTATE 42000] (Errore 102)

Sembra che il problema è nel dare la variabile come parametro alla funzione opendatasource. Ma perché? E come evitare questo problema?

  • cursore nidificata : che problema c’è!!!!
  • Penso che un cursore nidificata in modo sostenibile solo di fare quello che l’OP vuole fare (ciclo attraverso tutti i database in un insieme di server, i cui nomi sono contenuti in una tabella)
InformationsquelleAutor stee1rat | 2010-12-08

 

2 Replies
  1. 3

    È giusto che le variabili non possono essere passati a OPENDATASOURCE. Invece, È necessario utilizzare un valore letterale, invece. Per quanto ci scoraggiare l’utilizzo di SQL dinamico, ci sono alcuni casi che è inevitabile. Provare qualcosa di simile a questo:

    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='[email protected]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
    • Vi ringrazio molto! Non ho pensato in quel modo! 🙂 e ‘ davvero aiutato!
    • Siete i benvenuti!
  2. 1

    Se è necessario utilizzare cursori nidificati, si sta facendo qualcosa di sbagliato. Ci sono molte ragioni per l’utilizzo di un cursore invece di un altro set-il funzionamento e l’utilizzo di un cursore all’interno di un cursore è come il finale di SQL Server anti-pattern.

    Per il tuo cursore interno, si potrebbe cambiare per utilizzare documenti sp_msforeachdb funzione (che a quanto pare crea un cursore dietro le quinte):

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

    Potrebbe essere necessario racchiudere l’ ? negli apici e la fuga di loro, come:

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

    • ehm, immagino che non hai guardato la definizione di sp_msforeachdb poi? (Si utilizza un cursore!)
    • lol non sapevo che è gestito internamente. Grazie per le info.
    • Credo che ci potrebbe essere un argomento per il suo utilizzo ancora, però (+1). Ci vuole un po di complessità del po di codice e non di ulteriori controlli che il database è accessibile.
    • Mi piace che si utilizza sp_msforeachdb, ma non sono sicuro di come questo rende le connessioni ai server remoti.
    • Buon punto, ho erroneamente lasciato fuori. Corretto.
    • Sei sicuro?

Lascia un commento