Stored Procedure - What can it do?

[MrGTI]

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.

[Serge]

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.

[MrGTI]

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

[Serge]

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

[MrGTI]

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.

[MrGTI]

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:

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.

[skraheem]

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

[Negative0]

Use master..xp_sendmail instead of just xp_sendmail.