Michael Purdham
asked on
SQL Mail Attachment in ANSI format
I have a query that sends a csv file as an attachment to a customer with stock levels but his system is rejecting the file.
In Notepad this file looks perfect but when opening in Total Commander and viewing as Binary it shows 2 strange chars at the start of the file before the first value.
If the file is saved in Notepad it shows encoding UNICODE and if saved a ANSI then works correctly without the strange chars.
How do I change the query so that the csv file is encoded as ANSI.
My Query
DECLARE @subject NVARCHAR(500) = '0096090827_bestand'
DECLARE @body NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
DECLARE @sendto VARCHAR(MAX) = '';
DECLARE @copyto VARCHAR(max) = '';
DECLARE @blindcopyto VARCHAR(max) = 'customer@fred.com';
DECLARE @Filename VARCHAR(max)= '0096090827_bestand.csv';
DECLARE @separator CHAR(1) = char(59);
SET @query = '
SET NOCOUNT ON;
SELECT *
FROM xyz
WHERE Location = "ABC"
GROUP BY Item, [Qty on SO],[Lead Time Calculation]
ORDER BY Item;
';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NAVSQL',
@importance = 'High',
@body_format = 'Text',
@recipients = @sendto,
@copy_recipients = @copyto,
@blind_copy_recipients = @blindcopyto,
@subject = @subject,
@body = @body,
@query = @query,
@query_attachment_filename = @Filename,
@attach_query_result_as_fi le = 1,
@query_result_header = 0,
@exclude_query_output = 1,
@query_result_width = 256,
@append_query_error = 1,
@query_result_no_padding = 1,
@query_result_separator = @separator;
In Notepad this file looks perfect but when opening in Total Commander and viewing as Binary it shows 2 strange chars at the start of the file before the first value.
If the file is saved in Notepad it shows encoding UNICODE and if saved a ANSI then works correctly without the strange chars.
How do I change the query so that the csv file is encoded as ANSI.
My Query
DECLARE @subject NVARCHAR(500) = '0096090827_bestand'
DECLARE @body NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);
DECLARE @sendto VARCHAR(MAX) = '';
DECLARE @copyto VARCHAR(max) = '';
DECLARE @blindcopyto VARCHAR(max) = 'customer@fred.com';
DECLARE @Filename VARCHAR(max)= '0096090827_bestand.csv';
DECLARE @separator CHAR(1) = char(59);
SET @query = '
SET NOCOUNT ON;
SELECT *
FROM xyz
WHERE Location = "ABC"
GROUP BY Item, [Qty on SO],[Lead Time Calculation]
ORDER BY Item;
';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NAVSQL',
@importance = 'High',
@body_format = 'Text',
@recipients = @sendto,
@copy_recipients = @copyto,
@blind_copy_recipients = @blindcopyto,
@subject = @subject,
@body = @body,
@query = @query,
@query_attachment_filename
@attach_query_result_as_fi
@query_result_header = 0,
@exclude_query_output = 1,
@query_result_width = 256,
@append_query_error = 1,
@query_result_no_padding = 1,
@query_result_separator = @separator;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.