SQL Server嵌套游标问题

发布于 2024-10-06 07:30:59 字数 982 浏览 0 评论 0原文

我的嵌套游标有一个奇怪的问题，我不知道它是怎么回事。

这是我的 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

它给出了下一条错误消息：

“@str”附近的语法不正确。 [SQLSTATE 42000]（错误102）

看起来问题在于将变量作为 opendatasource 函数的参数。但为什么？以及如何避免这个问题呢？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

執念 2024-10-13 07:30:59

您是正确的，变量无法传递给OPENDATASOURCE。相反，您必须使用文字。尽管我们不鼓励使用动态 SQL，但在某些情况下这是不可避免的。尝试这样的事情：

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

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

回复收藏 0 原文

咆哮 2024-10-13 07:30:59

如果您需要使用嵌套游标，那么您就做错了。很少有理由使用游标而不是其他一些基于集合的操作，并且在游标中使用游标就像最终的 SQL Server 反模式。

对于内部光标，您可以将其更改为使用未记录的 sp_msforeachdb 函数（这显然在幕后创建了一个光标）：

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

您可能需要将 ?用单引号括起来并转义它们，例如：

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

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)

回复收藏 0 原文

~没有更多了~