Post reply

Sending mails from sql 2005

  • November 4, 2008 at 7:35 am

    #383299

    We are facing some issue with Sql Server 2005 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time.

    As per MSDN help; this sp executes after some interval for checking queue data; for sending/receiving email information. And as per MSDN link "http://support.microsoft.com/kb/931175" the code mentioned in the sp_readrequest sp also incorrect.

    "The incorrect code causes the sp_readrequest stored procedure to end without executing the END CONVERSATION statement. Therefore, SQL Server 2005 cannot process queued messages and cannot write the Database Mail log."

    As per above link , Microsoft has suggested to do change in the system sp "sp_readrequest", I did the same thing but still its not working. Again this "sp_readrequest" sp gets into suspended mode when it is internally executed by Sql Server.

    There is nothing being logged in sql logs or event viewer. Can any one help in understanding how to track what is going wrong and where?

    Cheers

    Siddarth

  • November 4, 2008 at 7:41 am

    #893624

    do not cross post the same question in different forums.

    follow the thread here:

    http://www.sqlservercentral.com/Forums/Topic596429-146-1.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 4, 2008 at 7:42 am

    #893626

    Just would like to give more information about the issue…whenever we are calling sp_send_dbmail for sending an email with attachment, under our application transaction….sometimes msdb database moves into suspect mode. And the table named "sysmail_attachments_transfer" gets locked while inserting data into sysmail_attachments_transfer table by sp_send_dbmail sp.

    We are generating 3 word document at runtime using SQL Clr functionality; and generated file we are sending to users using sp_send_dbmail systems stored procedure.

    Whenever this sp gets executed sql server creates another db session which is executing sp_readrequest sp; if this db session is working fine then msdb is working fine. And if session of execution of sp_readrequest goes into suspended mode and application database is trying to execute again "sp_send_dbmail" sp; database server creates another db session for msdb which goes into suspended mode with the command "(@P1 nchar(72),@P2 nvarchar(90),@P3 int,@P4 image)INSERT sysmail_attachments_transfer(uid, filename, filesize, attachment) VALUES((@P1), (@P2), (@P3), (@P4))"

    This happenes only while sending 3 times email with attachment to user in a single transaction.

    Here we are using while loop for sending email to user based on the conditions).

  • November 4, 2008 at 7:42 am

    #893628

    Fine, I will take care onwards.

    Cheers

    Siddarth

Viewing 4 posts - 1 through 3 (of 3 total)

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