SQL 2000 Sql Mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL 2000 Sql Mail

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-11-02 : 16:13:48

Trying to setup SQL mail on SQL server 2000.

Getting the following error when running the script below. I do not see a sp_send_dbmail stored procedure in teh msdb database. Is there some way I can create it??

Could not find stored procedure 'msdb.dbo.sp_send_dbmail


EXEC msdb.dbo.sp_send_dbmail
    @recipients='mail@mail.com',
    -@copy_recipients = 'ltilma@unist.com',
    @body='Query Results Are Attached', 
    @subject ='Comm Amts off',
    @profile_name ='ErinMail',
    @query ='use data
SELECT ord_no, slspsn_comm_amt, comm_amt ,
FROM oeordhdr_sql,
where slspsn_comm_amt = 0 and comm_amt > 0 and ord_no >00116258', 
    @attach_query_result_as_file = 1,
    @query_attachment_filename ='CommAmts.txt'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-02 : 16:31:07

That stored procedure is for Database Mail which is only available in 2005 and 2008. You need to use xp_sendmail, which is the SQL Mail stored procedure to send email (assuming it's been setup properly already).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-11-02 : 16:41:49

I just figured that part out. The syntax must be different. I can't seem to find how the syntax should be.

I replaced EXEC msdb.dbo.sp_send_dbmail with xp_sendmail and now I get this:
xp_sendmail: Invalid parameter '@body'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-02 : 16:49:42

Yes the syntax is very different. I googled it for you: http://msdn.microsoft.com/en-us/library/aa260697(SQL.80).aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-02 : 16:50:00

It's also available in 2000 BOL as well as 2005 and 2008 (SQL Mail is still around).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources