SQL Server Agent

Hi all,

I've written an SP that scans a database and sends out alerts as an email and net send messages.  It works fine if I execute it through SQL Query Analyzer.  But if I create a job in SQL Agent as 'exec xsp_SP' and schedule it to run every 10 mins, I receive email alerts but not net send messages.  Why is that?  Also, how do I specify an operator's email address/net send address in my SP?  Can I send messages to multiple operators?

Thanks in advance,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
did you try to run : net send ... from ms-dos?
you can add username in SQL agent\Operators\specific oper -net send address
run as step - for example -  on Failure  exec master..xp_cmdshell 'net send username "test please ignore"'..
vbtigerAuthor Commented:
>>did you try to run : net send ... from ms-dos?
Yes I did and it works fine.
Have you tried netsending directly from the SERVER in ms-dos?  It may be down to permissioning on the server side.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vbtigerAuthor Commented:
How do I do that?  BTW, the SQL server is on my PC.
OK, are you saying that the physical database is on your PC?  I would assume not - unless your PC is a server itself!!

When you first start Enterprise Manager, expand the Console Root, then Microsoft SQL Servers, then SQL Server Group.  You will see that there will be an entry for "(local)" - this is YOUR PC.  Whichever node you pick when you look at the database in your original question above is the SERVER name.

The problem is the SQL Server Agent service account doesn't have the privilige to access the mail server

Any way I'm using a SP to send mail insted of xp_sendmail

@From as nvarchar(50),
@To as nvarchar(50),
@Subject as nvarchar(255),
@Body as text

-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object

-- Configuration Object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'

-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'

-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config

-- Errorhandler
IF @hr <> 0
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

Eugene ZCommented:
did you try rest of my post?

>you can add username in SQL agent\Operators\specific oper -net send address
>run as step  - for example -  on Failure  
>exec master..xp_cmdshell 'net send username "test please ignore"'..
vbtigerAuthor Commented:
Hi EugeneZ,

I didn't get what you're saying.


The database is on my PC.  After developing the program,  I will tranfer it to our server.
vbtigerAuthor Commented:

Email works fine.  Only net send doesn't
Do you have the Messenger Service (not to be confused with MSN Messenger) running?  That's a requirement to net send....
vbtigerAuthor Commented:
Yes I do and it is enabled.
Eugene ZCommented:
if you can run net send from Ms dos - you can run from sql by using xp_cmdshell....:

exec master..xp_cmdshell 'C:\WINNT\system32\net.exe send username "test please ignore"'..

If it did not give you message - please post error message...
vbtigerAuthor Commented:
Hi Eugene,

It works fine when I execute the statement through SQL Query Analyzer.

I think I've figured out what the problem is.  When I changed the 'owner' for the job to sa, the pop-up appears.  I guess it's because the user who I chose earlier didn't have rights to execute xp_cmdshell.  How do I grant permission to a user to access this SP?  Also,  I've created a user as dbowner.  What is the right kind of access to be given to a user of my program?  I'll give points to anyone who answers these questions.

vbtigerAuthor Commented:
This is how I figured out the problem.  When I used the user account to login through my VB program and then executed the same script,  I got the following error:

xpsql.cpp: Error 997 from GetProxyAccount on line 472

When I logged in as sa, it worked fine.  I am guessing that the problem is with permission to xp_cmdshell.  I could be wrong.
Eugene ZCommented:
yes, it is execute permission to xp_cmdshell ..

try my solution from :

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.