[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

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,
Adi
0
vbtiger
Asked:
vbtiger
  • 7
  • 4
  • 2
  • +2
1 Solution
 
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
or
run as step - for example -  on Failure  exec master..xp_cmdshell 'net send username "test please ignore"'..
0
 
vbtigerAuthor Commented:
>>did you try to run : net send ... from ms-dos?
Yes I did and it works fine.
0
 
SQL_StuCommented:
Have you tried netsending directly from the SERVER in ms-dos?  It may be down to permissioning on the server side.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
vbtigerAuthor Commented:
How do I do that?  BTW, the SQL server is on my PC.
0
 
SQL_StuCommented:
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.


Stu
0
 
wael_tahonCommented:
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

CREATE PROCEDURE sp_SMTPemail
(
@From as nvarchar(50),
@To as nvarchar(50),
@Subject as nvarchar(255),
@Body as text
)
AS

-- 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
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END


GO
0
 
Eugene ZCommented:
vbtiget:
did you try rest of my post?

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

I didn't get what you're saying.

SQL_Stu,

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

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

try
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...
0
 
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.

Thanks
0
 
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.
0
 
Eugene ZCommented:
yes, it is execute permission to xp_cmdshell ..

try my solution from :
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21132476.html
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now