June 10, 2008 at 8:49 am
Hi,
I am trying to use sp_send_dbmail to both email and sms address. The email works fine both in HTML and text format but when it is sent to the sms address the message that gets received is either blank if using HTML or a series of garbled charcters if using text. Have spoken with sms service provider and they confirm they use no data reformatting and the garbled data is what we are sending to them. This all works fine on 2000 using xp_sendmail, but cant use outlook on the 2005 box.
If anyone can help, that would be great
Steve
June 10, 2008 at 9:33 am
Since SMS is purely text, the HTML version coming up blank makes sense. The phone wouldn't know what to do with what you sent. For the non-HTML message, make sure you are only sending text without any control characters.
June 10, 2008 at 9:37 am
Cheers for the response. Have tried it using 'Test' as the text body see below:
DECLARE @ServerName varchar (100)
declare @body1 nvarchar(100)
SET @ServerName = @@servername
set @body1 = N'test'
EXEC msdb.dbo.sp_send_dbmail @recipients = 'mobileno',
@subject = N'test',
@body = @body1,
@body_format = 'text' ,
June 10, 2008 at 9:48 am
steve.dowie (6/10/2008)
Cheers for the response. Have tried it using 'Test' as the text body see below:DECLARE @ServerName varchar (100)
declare @body1 nvarchar(100)
SET @ServerName = @@servername
set @body1 = N'test'
EXEC msdb.dbo.sp_send_dbmail @recipients = 'mobileno',
@subject = N'test',
@body = @body1,
@body_format = 'text' ,
Just a guess here but what if you change this to:
DECLARE @ServerName varchar (100)
--declare @body1 nvarchar(100)
SET @ServerName = @@servername
--set @body1 = 'test'
EXEC msdb.dbo.sp_send_dbmail @recipients = 'mobileno',
@subject = 'test',
@body = 'test',
@body_format = 'text'
June 10, 2008 at 9:55 am
Get the same thing sms says:
dGVzdA==
June 10, 2008 at 10:02 am
I just ran the following and got a successful message to my phone:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = '[my number here]@mms.att.net',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
Perhaps it is in your @recipients variable? Are you using a full email address when testing to your phone?
June 10, 2008 at 10:11 am
Just tried your script and get the same, only difference is the provider
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'Default',
@recipients = 'mobileno@esendex.net',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
May try a test with a different provider
June 10, 2008 at 10:14 am
would anyone be able to use the free trial at the address below to see if they get the same error. At least it may clear up if it is our provider or our SQL. Thanks
June 10, 2008 at 10:18 am
If you will send me a private message with your number, I'll try it from here.
June 10, 2008 at 10:26 am
Just sent you a private message.
Cheers
June 10, 2008 at 10:32 am
I would guess you should have it by now.
October 3, 2008 at 1:55 pm
I am having the same issue sending an SMS message from sp_send_dbmail through Nextel. The sender and subject are fine, but the body is a mish-mash of text.
Did you get this resolved?
Thanks.
October 6, 2008 at 10:18 am
Didn't manage to get this resolved. In the end as it was a small number of sms to be sent I created an outlook rule to forward to my mobile no.
However, you my have some joy with using xpsmtp.dll which a colleague has used successfully with 2005 http://www.sqldev.net/xp/xpsmtp.htm
Cheers
April 21, 2010 at 2:41 pm
Hi,
Actually, we are changing our phone provider (from movistar to nextel) , so we are doing tests with nextel and it's motorola devices (i290), here is the query i've used:
declare @mensaje varchar(255), @destinatarios varchar(255), @tema varchar(50)
select @mensaje = '
Prueba de envio de mensaje desde Sql Server 2005
Uso del comando sp_send_dbmail,
fecha y hora de envio : ' + convert (varchar(20), getdate(),113)
set @destinatarios = '[phone_number]@mensajes.nextel.com.pe;
[phone_number]movistar.com.pe'
set @tema = 'Mensaje de Texto enviado desde SQL 2005'
EXEC msdb.dbo.sp_send_dbmail
@recipients = @destinatarios,
@body = @mensaje,
@subject = @tema,
@body_format = 'TEXT' ;
and the result is:
with nextel provider the message is a series of garbled charcters
with movistar provider the message is OK
So, I guess, it depends of the provider.
Mario
April 22, 2010 at 3:29 am
We actually upgraded to SQL2008 and it's not a problem having kept the same phone provider. I checked against some of our SQL2005 boxes and they are fine also. I think the issue was with how exchange was handling these messages as nothing was changed from SQL end. I was running the folllowing:
USE MSDB
declare @EmailMessage varchar(250)
declare @EmailSubject varchar(50)
DECLARE @ServerName varchar (100)
SET @ServerName = @@servername
set @EmailMessage = @ServerName + ' Full backup has succeeded for ' + CONVERT(VARCHAR(11),GETDATE(),113) + '.' + char(13)
set @EmailSubject = 'Full Backup Success'
exec sp_send_dbmail
@recipients = 'mobileno',
@body = @EmailMessage,
@subject = @EmailSubject
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply