0

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

PuchuKing33
  • 381
  • 3
  • 7
  • 19
  • are you sure you are checking the correct file in the correct location? Delete the file first, just to be sure. other than adding an empty space between, ie `-t ; `, not sure what else the issue could be. – Greg Nov 12 '15 at 16:01
  • I have deleted the old file, i have tried with space and without space between -t still same result. – PuchuKing33 Nov 12 '15 at 17:00
  • ok, the reason nothing is delimited is because you are selecting a single column. What you have in your repro does not match your output. – Greg Nov 12 '15 at 18:59
  • Okay, so its not possible to semi-colon Becuase its from one column? – PuchuKing33 Nov 12 '15 at 19:00
  • You can have column delimeters and row delimiters, what do you want? – Greg Nov 12 '15 at 20:03
  • add more than one column, then come back to us with results – Greg Nov 12 '15 at 21:10

1 Answers1

0

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

PuchuKing33
  • 381
  • 3
  • 7
  • 19