February 27, 2013 at 8:52 am
We recently upgraded from SQL 2005 to SQL 200R2. I used DB Mail on the SQL 2005 Box and am doing the same on the 2008R2 box. I have created a profile and account on DB Mail on the 2008R2 server that is identical to the profile & account on the 2005 server with the exception that anywhere I had 'SS2005' on the acount or profile on my 2005 box I now have set to 2008R2 on the 2008R2 box. I am using the exact same (unchanged) email address on each and am using teh same mail server IP and port.
I have taken screenshots and checked and double checked and I can find nothing different between the 2 and yet an email from each comes into OUtlook with very different FROM values. Using the exact same Call msdb.dbo.sp_send_dbmail on each box (with the diference being the subject line )...
On my 2005 box the FROM is shown as SQL04\2005.DBA and the same call to this SP on the 2008R2 server produces an email with my name (format is FirstName Lastname) in the FROM field.
Does anyone knwo if something changed with DB Mail between 2005 & 2008R2 that coudl cause this? Its important because this tag line in the FROM field lets me easily detremine which serevrt an email is coming from as we have a number of SQL Servers that use the sp_send_dbmail SP to send info throughout the day.night.
Thoughts?
If a screenshot is necessary I can do it but my IT guy tells me I have to use fake values for the vairous fields for security reasons and thats why I haven;t included it in the post. If its necessary I can do it though.
Kindest Regards,
Just say No to Facebook!February 27, 2013 at 9:49 am
YSL i put this together in a different post, it scripts out all your database mail settings;
for me this is handy when i want to winmerge and look for subtle differences;
this might help identify something you didn't expect, like maybe a replyto field being populated but not the same?
USE msdb
GO
Declare @TheResults varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
--CREATE Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + p.name + ''',
@description = ''' + ISNULL(p.description,'') + ''';
END --IF EXISTS profile
'
+
'
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
--CREATE Account [' + a.name + ']
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
@email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
@display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
@replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
@description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
@mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
@mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
@port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
@username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
@password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
'
+ '
IF NOT EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
-- Associate Account [' + a.name + '] to Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ''' + p.name + ''',
@account_name = ''' + a.name + ''',
@sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
END
*/
'
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ItemSplit(
ItemOrder,
Item
) as (
SELECT N,
SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
FROM Tally
WHERE N < DATALENGTH(@vbCrLf + @TheResults)
--WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from ItemSplit
Lowell
March 1, 2013 at 10:01 am
Thanks Lowell.
I ran the script you provioded aginast both SQL Servers, saved them each to a file and then using UltraCompare I compared the 2 and theyr are indetical with the exceptio that everywhere it reads SS2005 on the file created from my SQL 2005 server shows up as S2008R2 on my new SQL 2008R2 server. There is nothing in eitehr file that is identical to the the text that appears in the FROM Line of email msgs in OL.
The test email from my SQL2005 SERVER has the following in the FROM field:
SQL04\SS2005.DBA
The test email from my SQL 208R2 server has the following in the FROM field which is firstName SPACE LastName:
John Smith
Could there be something at the mail server that is contrllling this? Both DB Mail cobnfigs use the same email account on the same email server.
Thoughts
Thanks
Kindest Regards,
Just say No to Facebook!March 1, 2013 at 10:40 am
If the why isn't as important as the what in this case you could always use the
@From_Address =
March 1, 2013 at 11:08 am
Erin Ramsay (3/1/2013)
If the why isn't as important as the what in this case you could always use the
@From_Address =
Thanks for replying but that just over rides the from email address and not the Name. The DB Mail account uses my email adress but lists in the Acount name the SQL Server Name & Instance. I do this so I know from what server and instance an email alert is from.
Kindest Regards,
Just say No to Facebook!March 1, 2013 at 11:26 am
I'm not sure I understand. If I use
@From_Address = 'ErinRamsay<some@replyaddress.com>' both the name and reply address are overridden. Which Name field are you trying to change?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply