g.britton (11/22/2014)

---

Lynn Pettis (11/22/2014)

---

g.britton (11/21/2014)

---

Lynn Pettis (11/21/2014)

---

Here is an alternative that does not use a cursor or while loop. You will have to test it yourself to be sure it does what you expect.

create table dbo.Tbl_ServerList(

server_name sysname

);

insert into dbo.Tbl_ServerList

values ('MyServer1'),('MyServer2');

go

declare @SQLCmd nvarchar(max);

select @SQLCmd = stuff((select N'union all select Loginame,HostName,DbName,cmd from openrowset(''sqlncli'',''server=''' + server_name + ';Trusted_Connection=yes;'',''exec sp_who'')' + nchar(13) + nchar(10)

from dbo.Tbl_ServerList

order by server_name

for xml path(''),TYPE).value('.','nvarchar(max)'),1,10,'');

print @SQLCmd;

INSERT INTO Capture_Logins

exec (@SQLCmd);

go

drop table dbo.Tbl_ServerList;

go

Ah yes! The third way. Not often spoken of but viable if not as scalable. For smallish cardinalities, though, a good alternative.

Not sure what you mean by "not as scalable." I use this quite often in place of cursors (which I have also written for some tasks).

I mean, should the string to be passed to sp_executesql need to exceed the NVARCHAR(max) size. I know, not too likely when dealing with typical admin tasks. Conceivable though with large databases. You could solve that problem by doing it in segments, of course, but that adds complexity that could make maintenance less pleasant.

BTW have you done any benchmarks with this approach vs cursors? If so, any general conclusions? I wonder too about error handling. Do you take special steps to ensure you can pin errors down to a particular statement in the batch?