Link to home
Start Free TrialLog in
Avatar of Michael Purdham
Michael PurdhamFlag for United Kingdom of Great Britain and Northern Ireland

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_file = 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;
ASKER CERTIFIED SOLUTION
Avatar of Michael Purdham
Michael Purdham
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial