Search code examples
sql-serverbcp

bcp Issue with Query SQL Server

I have been experimenting with bcp because its a very elegant way to create text files. However, for some reason my query isn't working. I am sure it's something terribly simple but I would appreciate if someone could point out where I am dumb.

As I am not terribly familiar with bcp, is it due to maybe the (, [, ] characters? Do I need to escape these characters with "?

declare @Result int

exec @Result = master.dbo.xp_cmdshell 
                     'bcp "SELECT ''H'', 1227, 1227, 1227, 1227, 
                                  [Check Num], GETDATE(), [Control Num], 
                                  [Clm Spec 1], [Clm Date], [Dlr Num], 
                                  UPPER([Dlr Name]), [Amt Claimed], 
                                  [Amt Appr], [Amt Paid], 
                                  Address1, Address2, City, State, Zip + ''|''     
                           FROM DBNAME.dbo.tblName" queryout "C:\MyTable.txt" -c -t"|" -S SERVERNAME -T'
Solution

  • Unlike SQL, cmd.exe doesn't cope with a command split over several lines. If you rewrite the command all on one line, you'll get a result.

    (cmd.exe does allow ^ as a line-continuation marker, but quotation marks break it)

    But if you have access to the server, basic bcp operations are probably easier to do straight from a command line. And PowerShell happily lets you write and edit multi-line quoted strings.