July 20, 2015 at 9:16 am
Hello,
We are sending some csv file to a third party using sp_send_dbmail. This is working fine but I have an issue with certain account which are using Cyrillic or uncommon characters (like Turkish).
The result of the query is correct but the csv file not (???? for Cyrillic and blank or space for Turkish) .
I modify sp_send_dbmail to allow ANSI export as explained in some posts and that seems to be ok with Turkish.
But I have stil the issue with Cyrillic
Any idea ? thanks !!!!!!
Here the call to the store proc
DECLARE @OrderNumber char(10)
DECLARE @sub VARCHAR(100)
DECLARE @qry VARCHAR(1000)
DECLARE @msg VARCHAR(250)
DECLARE @query NVARCHAR(max)
DECLARE @query_attachment_filename NVARCHAR(max)
Select @OrderNumber = Substring(SAMPLESBROCHURESID,5,8) from sysdba.SAMPLESBROCHURES where SAMPLESBROCHURESID= @SamplesBrochuresID
SELECT @sub = 'Order : ' + @OrderNumber
SELECT @msg = 'Please refer to the attached spread sheet for the report.'
SELECT @query = 'SET NOCOUNT ON; PRINT ''sep=;''; select * from CRM.dbo.Orders '
SELECT @query_attachment_filename = 'Order_' + @OrderNumber + '.csv'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '280',
@recipients = 'toto@gmail.com',
@body = @msg,
@subject = @sub,
@query = @query,
--@exclude_query_output = 1,
@query_attachment_filename = @query_attachment_filename,
@attach_query_result_as_file = 1,
@query_result_header = 0,
@query_result_width=32767,
@ansi_attachment = 1,
@query_result_separator = ';';
-- @query_result_no_padding =0;
July 20, 2015 at 1:14 pm
I'd start by making all my string variables NVARCHAR to make sure that isn't the problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 20, 2015 at 3:17 pm
Agreed with Jack... for Cyrillic you need to use unicode or you'll get garbage that looks like a bunch of question marks.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
July 21, 2015 at 5:09 am
Thanks for the answer.
The output parameters was in Unicode the other parameters were for others purposes.
I changed them just to be sure the result is the same...
Query output is correct but the generated txt file contains ???
Maybe some encoding need to be to specified for Russian in txt file ?
July 21, 2015 at 10:41 am
I believe the query to text file saves as an ANSI but for Russian(Cyrillic) you need to have it encode the text as Unicode(UTF-8).
I'm not sure how to do that though... sorry... maybe in SSIS you can specify an encoding for exporting files...
Maybe export the file as an Excel file might retain the unicode data...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
July 23, 2015 at 5:21 pm
Want a cool Sig (7/21/2015)
I believe the query to text file saves as an ANSI but for Russian(Cyrillic) you need to have it encode the text as Unicode(UTF-8).I'm not sure how to do that though... sorry... maybe in SSIS you can specify an encoding for exporting files...
Maybe export the file as an Excel file might retain the unicode data...
Indeed, you can. Go to your flat file connection manager properties and change CodePage
July 24, 2015 at 1:48 am
Ok great I will try that.
thanks everyone for your help !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply