SQL Server sqlcmd with a while loop being run with xp_cmdshell

Question

Good day,

I have a script where I am pulling server names into a variable which is put into a while loop to form a sqlcmd statement in order to connect to a list of servers and run the query. If I simply Print the statement and run it in a cmd window it returns as expected, but when I run in the SSMS window the only thing that is returned is Output NULL. If I try to insert into a defined table I get the ever famous column names or number of columns do not match.

Here is an example of what I am doing:

Table created up here server list inserted into #ServerList here

DECLARE BakInfoCur CURSOR FOR
    SELECT [Server Name] FROM #ServerList
OPEN BakInfoCur
FETCH NEXT FROM BakInfoCur INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @Command ='sqlcmd -S ' + @ServerName+' -E -W -h-1 -s "," -Q"SET NOCOUNT ON; Select '''+@ServerName+''' AS InstanceName ,d.[name] and more complex query stuff"'
    PRINT @Command
    INSERT INTO #ServerBackupInfo 
    (InstanceName,
    Database_Name,
    [Type],
    [Recovery Model],
    [Status],
    Last_Backup,
    [BackupSize(MB)],
    Device_Type,
    Physical_Path) 
    EXEC xp_cmdshell @Command
    FETCH NEXT FROM BakInfoCur into @ServerName
END
CLOSE BakInfoCur
DEALLOCATE BakInfoCur

I would need to see the entire script. With your partial script it appears that the select columns do not match the insert columns. — Ricardo C, May 27 '16 at 21:35
Enabling xp_cmdshell is a security risk and not necessary for what you're trying to achieve. A better approach is to configure a Central Management Server and then use this to query information from a group of SQL Servers. — Nathan Griffiths, May 28 '16 at 07:39

Alex · Answer 1 · 2016-05-27T23:37:17.117

I think you are doing it wrong. From MSDN:

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt,...

This utility does not return a recordset, so you cannot use the output of the sqlcmd to pass data to other SQL Server statements.

What you are looking for is OPENROWSET

SET @Command =
'SELECT a.*
FROM OPENROWSET(''SQLNCLI'', ''Server=' + @ServerName + ';Trusted_Connection=yes;'',
     ''Select '''+@ServerName+''' AS InstanceName ,d.[name] and more complex query stuff'') AS a'
INSERT INTO #ServerBackupInfo 
    (InstanceName,
    Database_Name,
    [Type],
    [Recovery Model],
    [Status],
    Last_Backup,
    [BackupSize(MB)],
    Device_Type,
    Physical_Path) 
EXEC sp_executesql @Command, N''

Command variable should be declared as NVARCHAR

SQL Server sqlcmd with a while loop being run with xp_cmdshell

1 Answers1