Exporting to CSV file

Select @recordCount = ISNULL(count(*),0) from invoiceheader where ivh_billto = 'PILKNO' and ivh_printdate >= dateadd(day,-7, getdate()) IF (@recordCount > 0) begin DECLARE @FileName varchar(35) SELECT @FileName = 'Wynne-' + convert(nvarchar(MAX), dateadd(day,-7, getdate()), 10) + ' thru ' + convert(nvarchar(MAX), dateadd(day,-1, getdate()), 10) + '.csv' EXEC msdb..sp_send_dbmail @profile_name = 'Pilot', @recipients = 'xxxx@xxx.com;xxx@xxx.com', --@from_address = 'xxx@xxx.com', --@reply_to = 'xxx@xxx.com', @subject = 'Freight Invoices', --@body = 'Test CSV output', @query = 'EXEC [dbo].[wynne_pilotinvoice]', @execute_query_database = 'TMWSUITE', @attach_query_result_as_file = 1, @query_result_separator =' ', @query_result_header =1, @query_result_width=32767, @query_attachment_filename = @FileName end else begin EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Pilot', @recipients = 'xxx@xxx.com;xxxx@xxx.com', @BODY = 'No Invoices Found', @subject = 'Pilot SQL Job' End End;

Tim Hunter

Iron Contributor

Oct 28, 2020

olafhelper

Here is the store procedure that builds the CSV file.

USE [TMWSUITE]
GO
/****** Object:  StoredProcedure [dbo].[wynne_pilotinvoice]    Script Date: 10/28/2020 8:31:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[wynne_pilotinvoice]
AS
BEGIN



select 
i.ivh_invoicenumber as [Carrier Invoice #]
,convert(nvarchar(MAX),i.ivh_billdate , 1) as [Date of Invoice]
,i.ivh_ref_number as [Pilot Company Order#]
,c.cmp_name as [BILL To: Business Entity]
,c.cmp_address1 as [BILL TO: Business Entity Address]
,ct.cty_name as [Terminal City]
,c1.cmp_address1 as [Terminal Address]
,f.fgt_supplier as [Lifted Supplier]
,( select top 1 ref_number 
	from referencenumber 
	where ref_type = 'BL#'
	and i.ord_hdrnumber = ord_hdrnumber) as [BOL#]
,i.ivh_totalvolume as [Lifted Quanity]
,convert(nvarchar(MAX),i.ivh_shipdate , 1) as [Lift Date]
,RIGHT(c2.cmp_name,3) as [Pilot Retail Location Number]
,c2.cmp_name as [Pilot Retail Location Name]
,c2.cmp_address1 as [Pilot Location Address]
,i.ivh_quantity as [Delivered Quanity]
,convert(nvarchar(MAX),i.ivh_deliverydate , 1) as [Delivery Date]
, i.ivh_splitbill_flag as [Split Load]
,f.fgt_description as [Lifted Product]
,i.ivh_rate as [Carrier Base Freight Rate]
,(	select SUM(ivd_charge) 
	from invoicedetail	
	where i.ivh_hdrnumber = ivh_hdrnumber 
	and cht_itemcode = 'FSCP') as [Fuel Surcharge]
,(	select SUM(ivd_charge) 
	from invoicedetail	
	where i.ivh_hdrnumber = ivh_hdrnumber 
	and cht_itemcode NOT IN ( 'FSCP','LHV')) as [Accesorial Charges Total]
,i.ivh_totalcharge as [Total Invoice]
from invoiceheader i
LEFT JOIN company c on i.ivh_billto = c.cmp_id
LEFT JOIN company c1 on i.ivh_shipper = c1.cmp_id 
LEFT JOIN company c2 on i.ivh_consignee = c2.cmp_id
LEFT JOIN city ct on i.ivh_origincity = ct.cty_code
LEFT JOIN city ct1 on i.ivh_destcity = ct1.cty_code
Left JOIN legheader l on i.ord_hdrnumber = l.ord_hdrnumber
LEFT JOIN freightdetail f on l.stp_number_rstart = f.stp_number
where i.ivh_billto = 'PILKNO' 
and i.ivh_printdate >= dateadd(day,-7, getdate()) 
order by i.ivh_billdate, i.ivh_invoicenumber

end

bake13
Microsoft
Oct 29, 2020
Hi Tim Hunter --

One method for creating the csv with a result header with no lines is to generate the column names in the query section of sp_send_dbmail. So for instance:

@query = N' SET NOCOUNT ON SELECT ''RegionID'' AS RegionID, ''RegionDescription'' AS RegionDescription UNION ALL SELECT CAST(RegionID AS varchar(10)),RegionDescription FROM TestDatabase01..mailtest'

Also, set the @query_result_header = 0. The contents of the CSV should appear similar to this:

One recommendation I might make is that since you are using an existing stored procedure, considering sending the results of the stored procedure to a temp table, the writing a SELECT statement similar to the one shown above to produce and send the CSV. Take care.

Forum Discussion

Resources