locked
Databasemail error in clustered environment RRS feed

  • Question

  • Hi.

    I'm having trouble getting DBmail to work on a SQL 2005 SP3 STD. (64-bit) installation.

    I've setup database mail with the wizard.

    I've tested sending an e-mail using telnet -> OK!

     

    But when I try to send a test e-mail from the sql server management studio I the message is queued, and the Windows Event Viewer logs an error like this:

    ***************************************

    Event Type: Error
    Event Source: DatabaseMail
    Event Category: None
    Event ID: 0
    Date:  27-08-2010
    Time:  10:01:23
    User:  N/A
    Computer: <My_servername>
    Description:


    1) Exception Information
    ===================
    Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
    Message: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters: Server Name: <My_servername>\<My_named_instance>, Database Name: msdb
    Data: System.Collections.ListDictionaryInternal
    TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo)
    HelpLink: NULL
    Source: DatabaseMailEngine

    StackTrace Information
    ===================
       at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
       at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password)
       at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)

    2) Exception Information
    ===================
    Exception Type: System.Data.SqlClient.SqlException
    Errors: System.Data.SqlClient.SqlErrorCollection
    Class: 20
    LineNumber: 0
    Number: -1
    Procedure: NULL
    Server:
    State: 0
    Source: .Net SqlClient Data Provider
    ErrorCode: -2146232060
    Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
    Data: System.Collections.ListDictionaryInternal
    TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
    HelpLink: NULL

    StackTrace Information
    ===================
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

    ***************************************

     

    Does anybody have a hint as what to do?

     

    Thanks in advance.

     

    /k_dueholm

    Friday, August 27, 2010 8:11 AM

Answers

  • Hi,

     

    Based on your description and the error message, the main reason causing such issue might be the Database Mail configure file couldn’t connect to msdb database. You could try these following steps to resolve this issue:

     

    1.    Create a text file named DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance with the following content :

        <configuration>

        <appSettings>

             <add key="DatabaseServerName" value="VirtualServerName\InstanceName" />

             <add key="DatabaseName" value ="msdb" />

         </appSettings>

         </configuration>

     

    2.    Modify the DatabaseMail configuration to tell SQL Server to use the new file by running the following code in SQL Server Management Studio:

    USE msdb;

    GO

    INSERT INTO [msdb].[dbo].[sysmail_configuration]

       (

        [paramname]

       ,[paramvalue]

       ,[description]

       )

    VALUES

       (

        N'ReadFromConfigurationFile'

       ,N'1'

       ,N'Send mail from mail server in configuration file'

       );

    GO

     

    3.    Restart the DataBase Mail with the following SQL command:

     

    USE msdb;

    GO

    EXEC dbo.sysmail_start_sp;

    GO

     

    Note: In a cluster environment, we should use the VirtualServerName rather than the NodeServerName. And you should copy that new file on every node of your cluster.

     

    Please feel free to ask if you have any questions.

     

    Thanks,

    WeiLin Qiao

    • Marked as answer by Jerry Nee Tuesday, September 7, 2010 6:17 AM
    Wednesday, September 1, 2010 8:47 AM

All replies

  • In the error messages, does it show for <MyServerName> the virtual SQL instance name or a nodename of a machine in the cluster?

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Friday, August 27, 2010 1:32 PM
  • Hi k_d

    Check the registry to ensure mail is configured and enabled on both the nodes,

     

    /* Check the registry to ensure mail is configured and enabled */

    DECLARE @returnvalue INT

       ,@path NVARCHAR(4000)

    DECLARE @returnvalue2 INT

       ,@path2 NVARCHAR(4000)

    EXEC @returnvalue = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

        N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',

        @path OUTPUT, 'no_output'

    EXEC @returnvalue2 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

        N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',

        @path2 OUTPUT, 'no_output'

    PRINT @path

    PRINT @path2

    GO

     

    If not

     

    -- THIS IS FOR SETTING THE MAIL PROFILE

    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

        N'Software\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',

        N'REG_SZ', N'Mail Profile'

    GO

    --ENABLING SQL TO USE MAIL

    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',

        N'REG_DWORD', 1

    GO

     

    Friday, August 27, 2010 11:13 PM
  • Hi Tom.

    First of all thank you for spending time on this.

    The error message writes the <virtual_server_name>\<named_instance>.

    BR, k_dueholm

    Monday, August 30, 2010 10:02 AM
  • Hi xs2varun.

    Many thanks to you aswell for spending time on this issue.

     

    The registry did not have the settings above.

    So what I did was that I enabled "Alert System" in the [Properties] og "SQL Server Agent". This modified the registry to the settings you suggest.

    However when I try to send a test e-mail I still get the same error.

    (This is how I send the test e-mail: right-click [virtual_servername\named_instance] -> [Management] -> [Database Mail] -> "Send Test E-Mail")

     

    Please if you have further suggestions I would appreciate them.

     

    BR, k_dueholm

    Monday, August 30, 2010 11:29 AM
  • Can you try this

    EXECUTE

     

    msdb.dbo.sp_send_dbmail

    @recipients

    =N'test@test.com',

    @subject

    ='test ',

    @body

    = 'Hi',

    @profile_name

    ='profile_name';

    go

    --Also the below query would give you insight as to why your mail is failing

    SELECT

     

    SUBSTRING(a.subject,1,25) AS 'Subject',

    a

    .mailitem_id,

    b

    .description

    FROM

     

    msdb.dbo.sysmail_event_log b

    join

     

    msdb.dbo.sysmail_faileditems a

    ON

     

    a.mailitem_id = b.mailitem_id

    WHERE

     

    event_type = 'error';

    go

    Check the below lonk which can be useful as well.

    http://www.sqlhacks.com/index.php/Administration/Database-Mail-Troubleshooting

     

    Tuesday, August 31, 2010 2:51 AM
  • Hi,

     

    Based on your description and the error message, the main reason causing such issue might be the Database Mail configure file couldn’t connect to msdb database. You could try these following steps to resolve this issue:

     

    1.    Create a text file named DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance with the following content :

        <configuration>

        <appSettings>

             <add key="DatabaseServerName" value="VirtualServerName\InstanceName" />

             <add key="DatabaseName" value ="msdb" />

         </appSettings>

         </configuration>

     

    2.    Modify the DatabaseMail configuration to tell SQL Server to use the new file by running the following code in SQL Server Management Studio:

    USE msdb;

    GO

    INSERT INTO [msdb].[dbo].[sysmail_configuration]

       (

        [paramname]

       ,[paramvalue]

       ,[description]

       )

    VALUES

       (

        N'ReadFromConfigurationFile'

       ,N'1'

       ,N'Send mail from mail server in configuration file'

       );

    GO

     

    3.    Restart the DataBase Mail with the following SQL command:

     

    USE msdb;

    GO

    EXEC dbo.sysmail_start_sp;

    GO

     

    Note: In a cluster environment, we should use the VirtualServerName rather than the NodeServerName. And you should copy that new file on every node of your cluster.

     

    Please feel free to ask if you have any questions.

     

    Thanks,

    WeiLin Qiao

    • Marked as answer by Jerry Nee Tuesday, September 7, 2010 6:17 AM
    Wednesday, September 1, 2010 8:47 AM