mercredi 25 février 2015

Using loops inside Openrowset


I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in older ones. I'll illustrate this issue with a simple query:



SELECT CAST(a.ServerName AS varchar(50)), CAST(a.LoginName AS varchar(50))
FROM OPENROWSET('SQLNCLI', 'Server=2012ServerName;Trusted_Connection=yes;','
SET NOCOUNT ON
DECLARE @LoginName sysname
DECLARE @FinalResults TABLE (LoginName varchar(50))
DECLARE LoginsCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT sl.Name LoginName
FROM master.dbo.syslogins sl WITH (NOLOCK)
OPEN LoginsCursor
FETCH NEXT FROM LoginsCursor INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @FinalResults
SELECT @LoginName
FETCH NEXT FROM LoginsCursor INTO @LoginName
END
CLOSE LoginsCursor
DEALLOCATE LoginsCursor
SELECT SERVERPROPERTY(''ServerName'') ServerName , LoginName FROM @FinalResults
') AS a


Result is success, but if I'll change provider to SQLNCLI10(I'm running this query from SQL Server 2012) It will fail. Also if I'll change server name to server with previous SQL version this query will fail. Is there an update in native client 11 that makes this query sucessful and is there a way to overcome this problem?





Aucun commentaire:

Enregistrer un commentaire