xp_sendmail hangs

[DuncFair]

Wrote some stored procedures that utilize xp_sendmail for a client. Testing via query analyzer executed for 20 minutes before I cancelled.

Tried a simple:

exec xp_sendmail 'me@myemail.com' 'test'

This hung (executed) for 10 minutes before I cancelled. The email address works fine and the 'test' from sqlmail works fine as well.

What could be the problem?

TIA,
Dunc

 

[MeanGreen]

The only thing I see right away is you need a comma between your variables:

exec xp_sendmail 'me@myemail.com', 'test'

Hope this helps.

 

[DuncFair]

Sorry, but that was just bad typing on my part. The comma is there in the real test.

Any other ideas?

 

[MeanGreen]

I don't know. I ran the query and it worked perfectly. Do you have other users sending mail on the server? I got this information from BOL:

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).


Maybe some of this will help identify the problem.

Good Luck.

 

[MeanGreen]

Can you post the stored procedure so I can look at it?