2

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 = '[email protected]', 
@to = '[email protected]', 
@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

2
  • Hi Justin, please take the tour. Also, are you getting an error?
    – Hannah Vernon
    Commented May 10, 2018 at 18:54
  • 1
    You are running a very primitive version of sql server that is totally out of support. Highly recommend you to upgrade to latest version.
    – Kin Shah
    Commented May 10, 2018 at 20:11

1 Answer 1

3

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 = '[email protected]', 
    @to = '[email protected]', 
    @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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.