Oct 27 2020 06:07 AM
Oct 27 2020 06:07 AM
We are exporting some data from SQL to a CSV file which is then emailed. This whole process works great. However, the CSV file has ------------------ on Line 2 of the CSV file. Is there a way to remove Line 2 and all the --------------?
Oct 28 2020 05:45 AM
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 = 'email@example.com;firstname.lastname@example.org', --@from_address = 'email@example.com', --@reply_to = 'firstname.lastname@example.org', @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 = 'email@example.com;firstname.lastname@example.org', @BODY = 'No Invoices Found', @subject = 'Pilot SQL Job' End End;
Oct 28 2020 06:40 AM
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
Oct 28 2020 07:44 PM
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.