Post reply

xp_sendmail in a stored procedure.....

  • July 13, 2005 at 3:11 pm

    #186595

    I was able to successfully test the following from a dos command prompt:

    1> xp_sendmail @recipients = 'brad@rbrsystems.us',

    2> @message = 'Are Your There',

    3> @subject = 'RBR Systems - SQL Server Auto Mailer System'

    4> go

    Mail sent.

     

    My question/comment:  I know how to go to Enterprise Manager and create a stored procedure, however I do not know what the syntax should be....I have attached a copy of what I tried to create, however I get error msg #156 Incorrect syntax near the keyword EXEC...

    Any help would be greatly appreciated.

    thanks

     

  • July 13, 2005 at 3:16 pm

    #574163

    Can you post the code of the proc?

  • July 13, 2005 at 3:17 pm

    #574165

    thanks for your quick response...here is the code

     

    CREATE PROCEDURE emailgas

    EXEC xp_sendmail

    @recipients = 'brad@domain.com',

    @subject = 'SQL Server - Updated Gas Transaction',

    @message = 'This is a test message, at some point the results will be in the

    body of this message'

  • July 13, 2005 at 3:23 pm

    #574170

    I think you need a space between the procedure name and the 'as'

    So it would be CREATE PROCEDURE emailg as . . . .

    Except that you may want the procedure name to be 'emailgas' in which case it should be CREATE PROCEDURE emailgas as . . .

  • July 13, 2005 at 3:23 pm

    #574169

    as he said .

  • July 13, 2005 at 3:23 pm

    #574168

    CREATE PROCEDURE dbo.emailgas --might wanna add the paramers in here

    AS

    EXEC master..xp_sendmail

    @recipients = 'brad@domain.com',

    @subject = 'SQL Server - Updated Gas Transaction',

    @message = 'This is a test message, at some point the results will be in the

    body of this message'

    GO

  • July 13, 2005 at 3:34 pm

    #574172

    Thanks...the following syntax works just fine...

    CREATE PROCEDURE dbo.emailgas 

    AS

    EXEC master..xp_sendmail

    @recipients = 'brad@rbrsystems.us',

    @subject = 'SQL Server - Updated Gas Transaction',

    @message = 'This is a test message, at some point the results will be in the

    body of this message'

    GO

     

    HOWEVER there is a problem, how do I change the permission level on the object xp_sendmail'  I placed the sp in my web-page and when I pull up the following page:

    http://test.rbrsystems.us/entergas.asp it comes up with the following error:  (using Firefox because it shows more of the error meanings than IE)

     

    Microsoft OLE DB Provider for ODBC Drivers error '80040e09' 

    [Microsoft][ODBC SQL Server Driver][SQL Server][Execute permission denied on object 'xp_sendmail',database 'master', owner 'dbo'.

    /entergas.asp line 134....

     

    1. does this mean that the extended stored procedure needs to be moved to the appropriate database? 

    2. or do I just go change the permission on the extended stored procedure?

    thanks

  • July 13, 2005 at 3:38 pm

    #574174

    I just answered my own question!!!  thanks I changed the permission on the extended stored procedure on the master database....

    I do have one other question though.....if you go to this link:

    http://test.rbrsystems.us/entergas.asp

    you will see that there are 6 datafields there, how would I capture those fields and have xp_sendmail send those results in an email format?

    Does that make sense?

    thanks

  • July 13, 2005 at 3:40 pm

    #574175

    I'm not the expert on permission about mails.. However I know it's a security breach to give normal users access to that proc. It's best to have a job ran by an admin that sends the mail. It also stops that security context error.

  • July 13, 2005 at 3:41 pm

    #574176

    "I do have one other question though.....if you go to this link:

    http://test.rbrsystems.us/entergas.asp"

    you're not validating any parameters.. this can be dangerous.

    Read this :

    The Curse and Blessings of Dynamic SQL

  • July 13, 2005 at 3:52 pm

    #574180

    You could validate the input on the form action page.  That would work.

     

    If you are just trying to send an email, you can use a mailer script.  Now if you want to capture the data posted back by the user into a database, you'll need to use some type of web application code to do that. 

    You would (approximately) set up a stored procedure that would insert the data into the database and then call the xp_sendmail xproc to notify.

    Once the sproc is created, you would call it using some form of ADO, after populating the input parameters to the procedure.

    It is more complicated than what I could explain in this forum, but not so complicated that it can't be done quickly.  Google for

    asp stored procedure

    You'll find much information.

     

     

     

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply