Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Exclamation Unanswered: Send mail true Stored Procedure

    Hi all,

    I have a database filled with contracts, suppliers and administrators who administrates those contracts.

    I want to make a sproc that checks the difference between the expiration_date and the current date(in months). the sproc compares this output with a given period in the contracts-table. when the output <= the given period ---> send mail to the administrator with info about the contract. and that the contract will be expired in X months.

    Having a Sproc that only gets the info from the tables and compares this info is no problem, but to let the sproc send an email to the admin whose email-adress also comes from a table is a little bit to tricky for me.

    I have searched the internet but i can't figure it out.

    If anyone knows where I can get more info about this subject please be so kind and let me know, or anything that could help me around for the moment.

    PS. I'm using SQL server 2000

    Thnx in advance

    StylizIt
    Last edited by StylizIT; 01-30-04 at 06:55.
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Read up on SQL Mail ... xp_sendmail in the Holy Book (Books Online)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Unhappy

    Originally posted by Enigma
    Read up on SQL Mail ... xp_sendmail in the Holy Book (Books Online)
    enigma thnx for the help.

    The sproc works amost perfect, het gets all the needed info
    (I checked this by first printing the needed info and then begin the mail)
    but i get now the next error:

    Server: Msg 18030, Level 16, State 1, Line 0
    xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run outlook and set it as the default mail client

    If someone knows how i can solve this problem please HELP

    PS. I use Kerio mailserver 5.7.5 and it have set outlook as default client

    thnx in advance
    Last edited by StylizIT; 01-30-04 at 07:12.
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Dont know about Kerio mailserver ... I have run sql mail successfully with Exchange Server and a Linux POP server ... with client as Outlook. Dont have that much experience with Other mail client ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Be sure that you set up Outlook on the SQL Server while logged in usingthe same account SQL Server Agent runs under.

    Log into the server using this account, run Outlook, set up the mail account, and send some test messages. Then check out Outlook options. There is a check box that will set and maintain Outlook as the default mail client.

    Once that is working, you should be all set.
    -bpd

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I would recommend using CDO if you have an inhouse exchange, - less problems, more results

  7. #7
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Guys thnx for the help, I solved the problem with slapping my myself and restarting my computer.
    But there seems to be another problem:
    How can I set the MAPI profile name, the list is empty and I tried everything that's written about it in MSDN.


    If anyone knows how to solve this reveal me the answer my son......
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  8. #8
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Post

    I solved this by using a SQL MAIL based solution called XSMTP.

    It works good for me

    further info can be found at the next page: http://www.sqldev.net/xp/xpsmtp.htm
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    XSMTP is another good alternative. It does require an extended sp to be registered though, which is not the case with CDO.

  10. #10
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Hi Rdjabarov,

    I'm not familiar with CDO, do you know any good sites where I could get more info about it, I tried google but got to much info and couldn't filter the right info.....

    thnx in advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  11. #11
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    CDO stands for Collaboration Data Objects, and is a Microsoft COM class. You can access it via ActiveX scripting or through the sp_OAxx set of stored procedures.

    Try this:
    http://msdn.microsoft.com/library/de...iew_of_cdo.asp

    or:
    http://www.microsoft.com/technet/tre...s_ent_wbpa.asp

    or:
    many many others
    -bpd

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

  13. #13
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Thumbs up

    Originally posted by rdjabarov
    Check this out:

    SQL 2K SMTP mail on Windows 2K
    Thanx
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •