Database Mail - No Message-ID in Header?

Question

Post reply

Database Mail - No Message-ID in Header?

PaulSp

SSCrazy

Points: 2499
More actions
August 1, 2011 at 8:11 am

#255025

Hi
I have been using SQL Mail in SQL 2000 for years, with great success. We are now moving to SQL 2008 and I need to upgrade my code accordingly.
I currently send data to a Provider organisation, which uses it according to the Header info included. The Provider can only use it if the Header includes a unique Message-ID. Always there in 2000 SQL Mail.
I can send mail fine using Database Mail in 2008, but the Provider says it does not include a Message-ID in the Header.
Can anybody advise how I can send with a Message-ID in the Header from Database Mail in 2008.
I've included below the code I use to send in both 2000 and 2008, and the return from the Provider to illustrate that there in no Message-ID included in 2008.
If anybody can help, I would be very grateful
Many thanks
Paul
------------------------------------------
declare @mb varchar(50)
declare @SQLQUERYSMS VARCHAR(1000)
set @mb = '07111111111' --Mobile Number
declare @fn varchar(50)
set @fn = 'this is a failure from Database Mail. No Message-ID in Header'
--------------------------------------------
--DATABASE MAIL FAILURE - Sent on SQL 2008. No Message-ID in Header
SET @SQLQUERYSMS = '
use msdb
EXEC sp_send_dbmail
@profile_name=''OurProfile'',
@recipients = ''sms1@ProviderAddress.co.uk'',
@body = ''number=44: ' + @mb + '
identifier=OurID@loucoll.ac.uk
costcentre=Default Cost Centre
body=Hi, ' + @fn + '=endbody'',
@subject = ''==EMAILAPI=='''
EXEC (@SQLQUERYSMS)
----------------------------------------------
--SQL MAIL SUCCESS - Sent on SQL 2000. Message-ID in Header
SET @SQLQUERYSMS = '
use master
EXEC xp_sendmail @recipients = ''sms1@ProviderAddress.co.uk'',
@message = ''number=44: ' + @mb + '
identifier=OurID@loucoll.ac.uk
costcentre=Default Cost Centre
body=Hi, ' + @fn + '=endbody'',
@subject = ''==EMAILAPI=='''
EXEC (@SQLQUERYSMS)
----------------------------------------------
--Returns from Provider
Fail Using Database Mail:
Return-path: <sqlservice@OurAddress.co.uk>
Received: from icritical-1.OurAddress.co.uk (193.61.111.48) by ProviderAddress.co.uk
29 Jul 2011 12:20:41 +0100
Received: (qmail 520 invoked from network); 29 Jul 2011 11:15:16 -0000
Received: from localhost (127.0.0.1)
by icritical-1.OurAddress.co.uk with SMTP; 29 Jul 2011 11:15:16 -0000
Received: from icritical-1.OurAddress.co.uk ([127.0.0.1])
by localhost (icritical-1.OurAddress.co.uk [127.0.0.1]) (amavisd-new, port 10024)
with SMTP id 32634-01 for <sms1@ProviderAddress.co.uk>;
Fri, 29 Jul 2011 12:15:15 +0100 (BST)
Received: (qmail 489 invoked by uid 599); 29 Jul 2011 11:15:14 -0000
Received: from unknown (HELO OurServer) (192.168.192.120)
by icritical-1.OurAddress.co.uk (qpsmtpd/0.28) with ESMTP; Fri, 29 Jul 2011 12:15:14 +0100
Sensitivity: Normal
Importance: Normal
MIME-Version: 1.0
From: sqlservice <sqlservice@OurAddress.co.uk>
To: sms1@ProviderAddress.co.uk
Reply-To: sqlservice@OurAddress.co.uk
Date: 29 Jul 2011 12:15:15 +0100
Subject: ==EMAILAPI==
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: base64
X-Virus-Scanned: by iCritical at icritical-1.OurAddress.co.uk
bnVtYmVyPTQ0OiAwNzg1Nzg2ODg0MQ0KaWRlbnRpZmllcj1QYXVsLlN1dGhlcmxhbmRAbG91
Y29sbC5hYy51aw0KY29zdGNlbnRyZT1EZWZhdWx0IENvc3QgQ2VudHJlDQpib2R5PUhpLiBG
LkEuTy4gTWFyay4gIFRoaXMgaXMgdGhlIHNlbmQgYW5kIFVOU1VDQ0VTU0ZVTCBTTVMgcmV0
dXJuIGZyb20gREFUQUJBU0UgTUFJTC4gRG9lcyBhbnl0aGluZyBsb29rIGRpZmZlcmVudCAt
IEkgY2FuIGFtZW5kIGFzIHJlcXVpcmVkLj1lbmRib2R5
Success Using SQL Mail:
X-Envelope-To: <sms1@ProviderAddress.co.uk>
Return-path: <sqlService@OurAddress.co.uk>
Received: from icritical-1.OurAddress.co.uk (193.61.111.48) by ProviderAddress.co.uk
29 Jul 2011 12:20:33 +0100
Received: (qmail 357 invoked from network); 29 Jul 2011 11:15:08 -0000
Received: from localhost (127.0.0.1)
by icritical-1.OurAddress.co.uk with SMTP; 29 Jul 2011 11:15:08 -0000
Received: from icritical-1.OurAddress.co.uk ([127.0.0.1])
by localhost (icritical-1.OurAddress.co.uk [127.0.0.1]) (amavisd-new, port 10024)
with SMTP id 50242-05 for <sms1@ProviderAddress.co.uk>;
Fri, 29 Jul 2011 12:15:07 +0100 (BST)
Received: (qmail 344 invoked by uid 599); 29 Jul 2011 11:15:06 -0000
Received: from unknown (HELO OurMailServer.OurAddress.co.uk) (192.168.192.174)
by icritical-1.OurAddress.co.uk (qpsmtpd/0.28) with ESMTP; Fri, 29 Jul 2011 12:15:06 +0100
Received: from OURMAILSERVER.OurAddress.co.uk ([::1]) by OurMailServer.OurAddress.co.uk
([::1]) with mapi id 11.01.0270.001; Fri, 29 Jul 2011 12:15:06 +0100
From: sqlService <sqlService@OurAddress.co.uk>
To: "'sms1@ProviderAddress.co.uk'" <sms1@ProviderAddress.co.uk>
Subject: ==EMAILAPI==
Thread-Topic: ==EMAILAPI==
Thread-Index: AcxN4MTh5eprqzaxTeeVgeY0TLVQDA==
Date: Fri, 29 Jul 2011 11:15:05 +0000
Message-ID: <292D2F7B3269A24D8688EA54656C503C105D0AEE@OurMailServer.OurAddress.co.uk>Accept-Language: en-GB, en-US
Content-Language: en-US
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
x-originating-ip: [OurIPAddress]
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
MIME-Version: 1.0
X-Virus-Scanned: by iCritical at icritical-1.OurAddress.co.uk
X-PMFLAGS: 34078848 0 1 YX0H2WEH.CNM
bnVtYmVyPTQ0OiAwNzg1Nzg2ODg0MQ0KaWRlbnRpZmllcj1QYXVsLlN1dGhlcmxhbmRAbG91Y29s
bC5hYy51aw0KY29zdGNlbnRyZT1EZWZhdWx0IENvc3QgQ2VudHJlDQpib2R5PUhpLiBGLkEuTy4g
TWFyay4gIFRoaXMgaXMgdGhlIHN1Y2Nlc3NmdWwgc2VuZCBhbmQgU01TIHJldHVybiB3aXRoIFNR
TCBNYWlsPWVuZGJvZHkNCkxvdWdoYm9yb3VnaCBDb2xsZWdl4oCZcyBuZXh0IE9wZW4gRGF5IGlz
IG9uIFNhdHVyZGF5IDIwdGggQXVndXN0IDIwMTEsIDExYW0gLSAzcG0uIENvbWUgYWxvbmcgdG8g
ZmluZCBvdXQgYWJvdXQgYWxsIG9mIHRoZSBjb3Vyc2VzIHdlIG9mZmVyLiBBbGwgb2Ygb3VyIHN1
YmplY3QgYXJlYXMgd2lsbCBiZSB0aGVyZSBvbiB0aGUgZGF5IGNhcnJ5aW5nIG91dCBsb3RzIG9m
IGFjdGl2aXRpZXMsIGdpdmluZyB5b3UgdGhlIGNoYW5jZSB0byBzZWUgd2hhdCBpdCB3b3VsZCBi
ZSBsaWtlIHRvIHN0dWR5IGF0IExvdWdoYm9yb3VnaCBDb2xsZWdlLiBGb3IgbW9yZSBpbmZvcm1h
dGlvbiBnbyB0byB3d3cubG91Y29sbC5hYy51ayAuDQo= "

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

Paul you just have to add body_format = @variable.

[ @body_format= ] 'body_format'

Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

TEXT

HTML

Defaults to TEXT.

Alex S

PaulSp SSCrazy Points: 2499 More actions · Answer 2

Hi Alex

Thanks so much for your reply - much appreciated.

I have added the extra line as you describe, and sent both with 'Text' and 'HTML':

@body_format= 'TEXT'.

However, the Provider still doesn't appear to be receiving the unique 'Message-ID'. When they receive the SQL Mail, I receive immediate notification that it has arrived and been processed. This is still not happening with Datbase Mail even with the new line.

Should the Provider be receiving the Message-ID now - or could there be a further issue..? It is incumbent upon us to provide the agreed Header data - specifically the Message-ID. If they need to re-programme, we will incur heavy costs...

Many thanks again for you help

Paul

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

PaulSp (8/2/2011)
...
Should the Provider be receiving the Message-ID now - or could there be a further issue..? It is incumbent upon us to provide the agreed Header data - specifically the Message-ID. If they need to re-programme, we will incur heavy costs...
...

Have you signed the agreement with Microsoft that they will never change the way SQLServer sends emails? I guess not 😉

According to http://www.faqs.org/rfcs/rfc2822.html MessageId must be unique but it considered as an optional field. The good news is: it's not generated by SQLServer as far as I know. It's upto mail server how it's getting generated. So check your mail server settings.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

PaulSp SSCrazy Points: 2499 More actions · Answer 4

Thanks very much for this.

As I am new to Database Mail (as you can obviously tell) - can you suggest which settings I need to amend...

As far as I understand now, unlike SQL Mail - where there was a copy of Exchange on the server - Database mail is purely a SQL Server process?? Please feel free to correct me on this...

Thanks again

Paul

Lowell SSC Guru Points: 323518 More actions · Answer 5

PaulSp (8/2/2011)
Thanks very much for this.
As I am new to Database Mail (as you can obviously tell) - can you suggest which settings I need to amend...
As far as I understand now, unlike SQL Mail - where there was a copy of Exchange on the server - Database mail is purely a SQL Server process?? Please feel free to correct me on this...
Thanks again
Paul

no not a pure SQL process....it's just a handy way to pass info to a real SMTP mail server.

the previous post was aluding to the fact that your service on your email server...mail.whatevercompany.com, is the one that creates the unique message Id...and since it's optional, someone may need to make a settings change on that service to make sure to generate the message id;

the issue you are encountering is on that mail server,a nd not in SQL's mail functions.

you could try using Google Gmail as an example, i know that mail server returns the messageId as a unique value:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!