How to Convert SQL Server Results to Text File for PowerShell

powershellsql serversql-server-2008sql-server-2008-r2sql-server-2012

The query below is used to create a text file with a list of server names.

The problem I have is that the text file gets created like

Server, Server, Server etc.

but I need it as follows

Server
Server
Server

The query:

BEGIN
    CREATE TABLE [TempPSWmiObjectQuery]
                (QueryText nvarchar(max))
END

INSERT INTO [TempPSWmiObjectQuery]

SELECT STUFF((SELECT char(13)+char(10), 
             SUBSTRING(ServerName, 1, 
             CASE WHEN CHARINDEX(',', ServerName) > 0 
                  THEN CHARINDEX(',', ServerName)-1
                  ELSE LEN(ServerName)
             END)
FROM MasterList
FOR XML PATH('')), 1, 1, '')

EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"'

EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE

DROP TABLE TempPSWmiObjectQuery

The PowerShell Script:

        $StagingServer = "ServerName\InstanceName"
        $StagingServerDatabase = "SQLEnv" 

    foreach($line in 
    [System.IO.File]::ReadLines("\\ServerName\Data\temp\testlist.txt"))
    {
        $line
        Get-WmiObject win32_Service -Computer $line  | where {$_.DisplayName 
    -match "SQL Server"} | select SystemName, DisplayName, Name, State, 
    Status, StartMode, StartName | ConvertTo-DbaDataTable | Write- 
    DbaDataTable -SqlInstance $StagingServer -Database 
    $StagingServerDatabase -Table _SQLServicesInfo -AutoCreateTable -Confirm

    }

Best Answer

This answer assumes multiple comma's are in a single row of the ServerName column, and these need to be removed & Line feed + Carriage returns added instead of each comma.

There could be better ways to get your endresult, but to write to a text file without the comma's, you could use the REPLACE() function.

CHARINDEX() will only give you the location of the first value found.

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' '+char(13)+char(10) )  -- Replace comma with space, line feed and carriage return.
FROM MasterList;


EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"';

EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;

DROP TABLE TempPSWmiObjectQuery;

Test

INSERT INTO MasterList(ServerName)
VALUES('Server1,Server2,Server3')

Result enter image description here

Change SELECT REPLACE(ServerName,',',+ ' '+char(13)+char(10) ) to SELECT REPLACE(ServerName,',',+ ' ' ) if you don't want new lines.

Related Question