Using bcp to export result of query to csv file

Transact-SQL
1

Attempting to run this query from bcp and export result set to csv file. Bcp always reports an issue with usage, but from what I've seen in examples, it should be correct. Not sure where this issue is...very frustrating!

USE CSI_HOLMATRO_HU_PROD;
GO

DECLARE @StartDate NVARCHAR(10) = '4/1/2023'
DECLARE @bcpCMD NVARCHAR(4000)
DECLARE @csvFilename NVARCHAR(200) = 'j:\MikeU\HU_Centix_Object00.csv'

-- Select column headers for resultant csv file

select @bcpCMD = 'bcp "Select ''Object00.AIDC'',''Object00.RevisionID.ID'',''Object00.ObjectOwnerID.ID'',''Object00.ObjectType.ID'',''Object00.ID'',''Object00.Descr'',''Object00.Descr2'',''Object00.BrandID.ID'',
''Object00.TypeID.ID'', ''Object00.SerialNo'',''Object00.IsUniqueItem'',''Object00.ObjectStatusID.ID'',''Object00.OwnerReference'',''Object00.ConstructionYear''

UNION

Select NULL, NULL, sq.cust_num, ''D'',sq.Unique_ID, sq.description, NULL, ''Holmatro'', sq.item, sq.ser_num, ''TRUE'', ''U'', NULL, sq.const_yr
FROM
(Select ph.pack_num, ph.cust_num, ph.co_num, pi.co_line, pi.item, i.description, sn.ser_num, CONCAT(REPLACE(pi.item,''.'',''''), sn.ser_num) AS ''Unique_ID'',
convert(varchar(10),year(ph.pack_date)) AS ''const_yr''
from dbo.pck_hdr_mst ph
join dbo.pckitem_mst pi
on pi.pack_num = ph.pack_num
join dbo.item_mst ic
on i.item = pi.item
join
(select s.ser_num, mt.ref_num, mt.ref_line_suf, mt.track_link
from dbo.matltrack_mst mt
join dbo.serial_mst s
on s.item = mt.item and s.stat = ''O''
join dbo.ser_track_mst st
on st.ser_num = s.ser_num and st.track_num = mt.track_num
where mt.track_type = ''I''
and mt.ref_type = ''O'') sn
on sn.ref_num = ph.co_num
and sn.ref_line_suf = pi.co_line
and sn.track_link = ph.pack_num
-- This where clause limits shipments equal or greater than date
where ph.pack_date >= ' + char(39) + @StartDate + char(39) + ') sq" queryout ' + @csvFilename + '-c -t; -T'

--print(@bcpCMD)

exec master.dbo.xp_cmdshell @bcpCMD

2

Let's start with the J:\ drive - is that a drive on the server that SQL Server has access to? It looks like a mapped drive on your workstation, which SQL Server would not be able to access.

Looking at your query - it isn't going to work the way you expect. In order to ensure your column headers are in fact the first row, you need to include an ordering column so you ensure the column headers are the first row. Additionally, you need to convert all columns returned in the query to a string to match the expected data type of the header row.

Also - you should be using UNION ALL instead of UNION. The UNION will force a sort on the results to remove duplicates and isn't necessary.

With all that said - it is much easier to export a delimited file with column headers using an external tool. I prefer Powershell for something like that - but SQLCMD also works.

PS> $result = Invoke-SqlCmd -ServerInstance "YourServerHere" -Database "YourDatabaseHere" -Query "YourQueryWithoutColumnHeaders";
PS> $result | Export-CSV -Path "full file path and name here" -UseQuotes AsNeeded;

This uses Powershell 7 - if you don't have that it is a simple download and install onto your workstation, then you can easily run these kinds of exports as needed.