After my presentation Tuesday in Detroit, there were a couple of questions about Database Mail.
The first was about how to setup profiles. That answer is relatively easy, so I'll take a little time and address it. First open SQL Server Surface Area Config and turn on Database Mail. You can also do this from sp_configure.
sp_configure 'Database Mail XPs', 1
Next open SSMS and navigate to the Management Folder. Right Click on Database Mail. Follow the wizard to create a profile specifying a name for the sender and reply to accounts and point to the appropriate SMTP server. I found this to be really straightforward and not worth belabouring in this post. If you have questions, feel free to email. I'll walk you through it.
The other was a question that was really insightful (my apologies for not having your name to give you credit). He asked if it would be possible to use SQL Server 2005 Database Mail to email query results from SQL Server 2000 instance over a linked server. I could not come up with a reason why this would not work, but I thought I'd test it just to be sure.
Before we do that, you might be saying, why would you want to do that. Well, if you want to avoid using a MAPI client on your server, and you want to have the feature to attach query results to an email, this was fairly difficult in SQL 2000. You could use Gert Drapers' xp_smtp_sendmail (http://www.sqldev.net/xp/xpsmtp.htm) and pick up a result file from a disk to attach and email, but that was cumbersome. The native xp_sendmail has that nice feature just to include the results in the body of an email or as an attachment, which many I like very much.
Below is the result of that test as well as some sample queries and descriptions about the setup.
ServerA = SQL Server 2005 (RTM)
ServerB = SQL Server 2000 (SP3a+818 hotfix)
First, setup Linked Server on ServerA pointing to ServerB
EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
GO
Next, Setup Security for the linked server. For my test I just set all users to be impersonated as SA. Of course, don't do this in production. You'll want to setup an account for this, or allow the service account for ServerA access on ServerB.
Finally, execute the stored procedure and specify the query and to attach results in a file
EXEC
msdb.dbo.sp_send_dbmail
@recipients
=N'jon@evergreencco.com',
@body
=N'Test', @query =N'select * from ServerB.dba_repository.dbo.dba_servers',
@attach_query_result_as_file
= 1, @profile_name = 'test'
;
This absolutely yielded the results I'd hoped for! I received an email with the query results attached.
So you can begin eliminating MAPI from your servers right away, just by putting up one instance of SQL Server 2005 and using linked servers and database mail. Thank you again to the very bright attenders at the Detroit launch event for your suggestions on this one. Great idea!
Jon