Sql-server – How to email query results formatted in html from MSSQL 2000 server

database-mailsql-server-2000

The only system SP I have is master.dbo.xp_smtp_sendmail and I can't make it work (or I just don't know how). I cannot upgrade the DB at this time. I'm trying something like this:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN 
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered 
DESC

exec master.dbo.xp_smtp_sendmail
@server = 'server',
@from = 'administrator@mydomain.com', 
@to = 'me@mydomain.com', 
@subject = 'HTML Testing...', 
@type = 'text/html', 
@message = @body_text

I can do this easily in 2005 and up using "msdb.dbo.sp_send_dbmail" but I am stuck on 2000 due to the size of the DB for now.

Any help or suggestions would be greatly appreciated.

Thanks

Best Answer

It looks like you're missing an apostrophe from the end of the @body_text parameter. This fixes that problem:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN 
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered 
DESC';

exec master.dbo.xp_smtp_sendmail
    @server = 'server',
    @from = 'administrator@mydomain.com', 
    @to = 'me@mydomain.com', 
    @subject = 'HTML Testing...', 
    @type = 'text/html', 
    @message = @body_text;

Also, You might consider using xp_sendmail from SQL Server 2000. The syntax is:

EXEC xp_sendmail @recipients = 'recipients' 
      , @message = 'message'  
      , @query = 'query'  
      , @attachments = 'attachments  ;...n '  
      , @copy_recipients = 'copy_recipients  ;...n '
      , @blind_copy_recipients = 'blind_copy_recipients  ;...n '
      , @subject = 'subject' 
      , @type = 'type'  
      , @attach_results = 'attach_value' 
      , @no_output = 'output_value'  
      , @no_header = 'header_value'  
      , @width = width  
      , @separator = 'separator'  
      , @echo_error = 'echo_value'  
      , @set_user = 'user'  
      , @dbuse = 'database'

Of course, xp_sendmail requires an Outlook profile be setup on the server, which might be prohibitive.

Related Question