Forum Discussion
Exporting to CSV file
We are using the Database Mail feature with a Stored Procedure to automatically email the data in a CSV file. So it is not just exporting the CSV file. The CSV file is sent in an email directly from SQL. The stored procedure works perfectly and emails with no issue. The only problem I have is that Line 2.
Here is our Stored Procedure
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;
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
bake13Microsoft
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.