Search code examples
sqlsql-servert-sqlbcpxp-cmdshell

xp_cmdshell semi-colon separation

I am using xp_cmdshell and i want the output to the text file to be semi-colon separated. I have tested the following:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;   -- 1 for at enable
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

-- Extracting information from the database
EXEC xp_cmdshell 'bcp "SELECT TcpIpAddress FROM [SIT-DVH].[dbo].[Preb_Idera]" queryout "C:\Output\Ip_outputSemi.txt" -T -c -t;'


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0;   -- 0 for at disable
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

I have seen several places saying that setting -t; should make the output to be semi-colon separated, however the output is still:

xxxx yyyy zzzz

Solution

  • I found the solution, since the output is as followed:

    xxx
zzz
yyy

    i needed to change -t; to -r; since i only have one field per row and [-r row_term], the output is as followed:

    xxx;zzz;yyy