xp_sendmail
-
Where do I find this SP? Is this included with a later version of Sql Server or is this an "add on" type SP? I'd love to try using this SP and learn more about how it works.
Thanks
-
The xp_sendmail SP is located in the master DB. For more information about this SP, enter "xp_sendmail" for the keyword in an index search within SQL Server Books Online.
Here are the remarks from Books Online:
Remarks
The SQL Mail session must be started prior to executing xp_sendmail. Sessions can be started either automatically or with xp_startmail. For more information about setting up a SQL Mail session automatically, see Configuring Mail Profiles. One SQL Mail session supports all users on the SQL Server, but only one user at a time can send a message. Other users sending mail messages automatically wait their turns until the first user's message is sent.
If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail.
Note query can be blocked by a lock held by the client connection issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for update that attempts to select the same updated row information as the query parameter, the SQL Mail connection is blocked by the exclusive lock held on row by the initial client connection.
xp_sendmail runs in SQL Server's security context, which is a local administrator account by default. A valid user of xp_sendmail can access files for attachment to a mail message in an administrator's security context. If nonsystem administrator users must access xp_sendmail and you want to guard against unsecured access to attachment files, the system administrator can create a stored procedure that calls xp_sendmail and provides the needed functionality but does not expose the attachments parameter. This stored procedure must be defined in the master database. The system administrator then grants execute permission on the stored procedure to the necessary users without granting permission to the underlying xp_sendmail procedure.
xp_sendmail sends a message and a query result set or an attachment to specified recipients, and uses a bound connection for the query parameter. The query connection made by SQL Mail is not blocked by locks held by the client that issues the xp_sendmail request. This makes xp_sendmail easier to use from within triggers. The query statement, however, cannot refer to the logical inserted and deleted tables that are only available within a trigger.
Note An access violation can result from an attempt to execute xp_sendmail when the post office and address book are on a file share that the MSSQLServer service cannot access due to inadequate permissions.
For more information about using a stored procedure for calling xp_sendmail, see How to use SQL Mail (Transact-SQL).
-
Great, thanks for the reply. I have one more question for you. Shouldn't I be able to go into Enterprise Manager, expand the Master db, view SP's and see the xp_Sendmail in the list? I don't??? There are just 3 or 4 SP's that start with xp_
Could it be because we're using an older version of Sql Server or something??
-
You would think it would be stored in the "Stored Procedures" portion of EM. However, the procedure is actually listed in "Extended Stored Procedures".
-
Ah Ha!
Thanks a bunch! -
No problem
Now if you can only send that email... 
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply