SQL Server Performance Forum – Threads Archive

email different languages/cultures

We are using the following sp to send emails. Works fine with english text however when we send email containing French/German characters the email text received has been changed. We are using nvarchar’s to store text. PrioritÃ© is changed to Priorit+//0. Any ideas? Kev
CREATE PROCEDURE [dbo].[NWsp_SendMail]
@From nvarchar(256) , @To nvarchar(256) , @Subject nvarchar(256)=" ", @Body nvarchar(4000) =" " /****************************************** This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site:http://msdn.microsoft.com/library/default.asp?url=/ library/en-us/cdosys/html/_cdosys_messaging.asp *******************************************/ AS Declare @iMsg int Declare @hr int Declare @source nvarchar(255) Declare @description nvarchar(500) Declare @output nvarchar(1000) –***** Create the CDO.Message Object ***** EXEC @hr = sp_OACreate ‘CDO.Message’, @iMsg OUT –*****Configuring the Message Object ***** — This is to configure a remote SMTP server.
—http://msdn.microsoft.com/library/d…ml/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing.asp").Value’,’2′ — This is to configure the Server Name or IP address.
— Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields("http://schemas. microsoft.com/cdo/configuration/smtpserver").Value’, ‘Telford2’ — Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, ‘Configuration.Fields.Update’, null
— Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, ‘To’, @To
EXEC @hr = sp_OASetProperty @iMsg, ‘From’, @From
EXEC @hr = sp_OASetProperty @iMsg, ‘Subject’, @Subject
— If you are using HTML e-mail, use ‘HTMLBody’ instead of ‘TextBody’.
EXEC @hr = sp_OASetProperty @iMsg, ‘TextBody’, @Body
EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL
— Sample error handling.
IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ‘ Source: ‘ + @source PRINT @output SELECT @output = ‘ Description: ‘ + @description PRINT @output END ELSE BEGIN PRINT ‘ sp_OAGetErrorInfo failed.’ RETURN END END
— Do some error handling after each step if you have to.
— Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

You can use varchar for French and German special characters, as they are in the lower ASCII range (<=255) – possibly not the Ringel-S, but that’s old spelling anyway.

The SP originally used varchar, I’ve amended it to use nvarchar in the hope it would solve problem.

]]>