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


The query:

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

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)
FROM MasterList
FOR XML PATH('')), 1, 1, '')

EXEC sp_configure 'xp_cmdshell', 1;  

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

EXEC sp_configure 'xp_cmdshell', 0;  

DROP TABLE TempPSWmiObjectQuery

The PowerShell Script:

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

    foreach($line in 
        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

  • Does your ServerName column have multiple comma's in one row? Charindex will only return the location of the first one found. You could use REPLACE(ServerName,',','') – Randi Vertongen Feb 28 at 11:06
  • 1
    Why not do the select from PowerShell? It has superior string manipulation capabilities and calling bcp via xp_cmdshell is a bit of a hack. – vonPryz Feb 28 at 11:18

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;  

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

EXEC sp_configure 'xp_cmdshell', 0;  

DROP TABLE TempPSWmiObjectQuery;


INSERT INTO MasterList(ServerName)

Result enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.