Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Agent

Posted on 2004-11-01
15
Medium Priority
?
535 Views
Last Modified: 2012-06-27
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
Comment
Question by:vbtiger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12462245
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
 

Author Comment

by:vbtiger
ID: 12462524
>>did you try to run : net send ... from ms-dos?
Yes I did and it works fine.
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12462535
Have you tried netsending directly from the SERVER in ms-dos?  It may be down to permissioning on the server side.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:vbtiger
ID: 12462604
How do I do that?  BTW, the SQL server is on my PC.
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12462656
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
 
LVL 7

Expert Comment

by:wael_tahon
ID: 12465388
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12468945
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
 

Author Comment

by:vbtiger
ID: 12469974
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
 

Author Comment

by:vbtiger
ID: 12469986
EugeneZ,

Email works fine.  Only net send doesn't
0
 
LVL 34

Expert Comment

by:arbert
ID: 12470163
Do you have the Messenger Service (not to be confused with MSN Messenger) running?  That's a requirement to net send....
0
 

Author Comment

by:vbtiger
ID: 12470378
Yes I do and it is enabled.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12498281
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
 

Author Comment

by:vbtiger
ID: 12511877
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
 

Author Comment

by:vbtiger
ID: 12511883
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 375 total points
ID: 12527554
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question