Stored Procedure - What can it do?

Can a stored procedure in SS2K send an email?

I understand how to make a stored procedure (aka SP), but how would i get the SP to send an email? All the examples i've seen around here don't do anything like that.

If it can't, how could i get the SP to return the recordset object that the SQL statement creates to VB? That SQL statement will contain all the records/info i want/need in order to create the email.

Check out extended system stored procedure xp_sendmail
Just bear in mind that you have to have an email account setup by lan admin, or whoever is responsible for the mail server.

You're gonna have to tell me where that is located in SS2K. I can't find it anywhere.

Open SQL Server Books Online and in the index type in xp_sendmail.....select transact-SQL version.

Thanks. I managed to get that installed and i was able to locate that example. That looks like exactly what i want it to do. Thanks.

I will try it out as soon as i can get my job to run. I made a simple Stored Procedure, and then made a job that run the SP, however the Job will not run. It's very odd. I have it set to run in 2 minutes, but after 4 minutes have elapsed, the Job still has a Last Run Status of "Unknown". Weird. Very weird.

While i am trying to get my Job to run (why it won't run, i have no idea), i decided to start building the email portion.

I created a Stored Procedure like so:

VB Code:

CREATE PROCEDURE [dbo].[sp_SendEmail] AS
 
EXEC xp_sendmail 'robertk', 'The master database is full.'
 
GO

When i try running that SP from within Query Analyzer, i get this error:

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'xp_sendmail'.

Something tells me i don't have the same things you have.

U can send mails in 2 ways in SQL SERVER.

1. Write 1 stored proc in which u can create reference to
vbs CDONTS object using sp_OAcreate sp.

Once u create the object, we need to set property and finally need to use send method of this object.

Properttoes can be set using sp_OAsetProperty ...


2. Use xp_sendmail to send mails directly.

When we run this extended stored procedure, SQL SERVER SERVICES should be run in Domain account instead of Local account.


Raheem

Use master..xp_sendmail instead of just xp_sendmail.