4 added 20 characters in body
source | link

This answer assumes multiple comma's are in a single row of the ServerNameServerName 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.

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')

enter image description here

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

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.

3 added 138 characters in body
source | link

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')

enter image description here


 

If you don't need a new line for each ServerName Change SELECT REPLACE(ServerName,',',+ ' '+char(13)+char(10) ) to SELECT REPLACE(ServerName,',',+ ' ' ) if you don't want new lines.

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' ')  
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;

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.

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')

enter image description here


 

If you don't need a new line for each ServerName

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' ')  
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;

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')

enter image description here

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

2 added 138 characters in body
source | link

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.

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')

enter image description here


If you don't need a new line for each ServerName

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' ')  
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;

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.

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')

enter image description here


If you don't need a new line for each ServerName

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' ')  
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;

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.

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')

enter image description here


If you don't need a new line for each ServerName

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' ')  
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;
1
source | link