Solved

SQL Server Agent

Posted on 2004-11-01
518 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
Question by:vbtiger
    15 Comments
     
    LVL 42

    Expert Comment

    by:EugeneZ
    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
    >>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
    Have you tried netsending directly from the SERVER in ms-dos?  It may be down to permissioning on the server side.
    0
     

    Author Comment

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

    Expert Comment

    by:SQL_Stu
    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
    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 42

    Expert Comment

    by:EugeneZ
    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
    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
    EugeneZ,

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

    Expert Comment

    by:arbert
    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
    Yes I do and it is enabled.
    0
     
    LVL 42

    Expert Comment

    by:EugeneZ
    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
    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
    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 42

    Accepted Solution

    by:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    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.

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now