0

I am running this code; it only return data, how can I get headers in my file?

EXECUTE master.dbo.xp_cmdshell 
 'bcp "SELECT * FROM table1 " queryout G:\Backup\Test.txt -t"|" -c -T ' 
5
  • Why are you calling a command-line app from TSQL only for it to call back into TSQL? Why not just call it from a command-line in the first place? Dec 6, 2023 at 9:05
  • @Charlieface Perhaps this is a scheduled process and they have preference of using SQL Agent Jobs. That would be one reason I could think of.
    – J.D.
    Dec 6, 2023 at 13:37
  • 1
    @J.D. Then use CmdExec jobs learn.microsoft.com/en-us/sql/ssms/agent/… xp_cmdshell just has no place in any decent code. Dec 6, 2023 at 13:43
  • @J.D. To be honest I don't think bcp.exe should be used either. Powershell has better tools, such as Invoke-SqlCmd or Invoke-DbaQuery which you can feed directly to Export-Csv. You can write SQL Agent jobs in Powershell as well. Dec 6, 2023 at 14:03
  • Related: stackoverflow.com/q/1355876/10592356
    – Doug Deden
    Dec 6, 2023 at 15:48

1 Answer 1

0

I am running this code:

[typical BCP query]

only return data,how can I get headers in my file?

There's a few solutions to this problem. One simple way is to hard-code them with a UNION ALL query like so:

EXECUTE master.dbo.xp_cmdshell
'bcp 
"
SELECT 
    CONVERT(VARCHAR(MAX), ''ColumnABC'') AS Column1, 
    CONVERT(VARCHAR(MAX), ''ColumnDEF'') AS Column2, 
    CONVERT(VARCHAR(MAX), ''ColumnGHI'') AS Column3 

UNION ALL 

SELECT 
    ColumnABC, 
    ColumnDEF, 
    ColumnGHI 
FROM table1
" queryout G:\Backup\Test.txt -t"|" -c -T '
5
  • EXECUTE master.dbo.xp_cmdshell'bcp "SELECT CONVERT(VARCHAR(MAX), "Database" AS Database, CONVERT(VARCHAR(MAX), "Customer/Account Number" AS Customer/Account Number, CONVERT(VARCHAR(MAX), "Site Number" AS Site Number UNION ALL SELECT [Database], [Customer/Account Number], [Site Number] FROM trux_ww_comp.dbo.CustomerInformation " queryout G:\Backup\BankInfo.txt -t"|" -c -T '
    – Yulichka
    Dec 6, 2023 at 14:03
  • @Yulichka You're using double quotes: "Database" when you should be using two single quotes instead ''Database''.
    – J.D.
    Dec 6, 2023 at 14:42
  • I already tried that and it is not working, any other sugestions? Thank you so much
    – Yulichka
    Dec 6, 2023 at 14:44
  • EXECUTE master.dbo.xp_cmdshell'bcp "SELECT CONVERT(VARCHAR(MAX), ''State'') AS State, CONVERT(VARCHAR(MAX), ''Zip'') AS Zip UNION ALL SELECT [State], [Zip] FROM trux_ww_comp.dbo.CustomerInformation " queryout G:\Test.txt -t"|" -c -T '
    – Yulichka
    Dec 6, 2023 at 14:47
  • @Yulichka No problem. What error are you getting?
    – J.D.
    Dec 6, 2023 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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