none
"login timeout expired" error with linked server query is used in sp_send_dbmail

    Question

  • Hi all,

    I have the following script which is used to send email based on linked server query running from WNRT2-PROD01:

     EXEC msdb.dbo.sp_send_dbmail
        @recipients=N'bveeramasu@nrtwebservices.com',
        @subject='test',
        @body='testing',
        @profile_name ='web-nrt-exch',  
        @query ='SELECT top 10 [name] FROM [WNRT1-WKS137\SQL2008R2DEV].[master].[sys].[objects]'

     

    environment config details for the above query:

    Server Name: WNRT2-PROD1

    SQL Version: 2008 R2 EE

    OS: Windows 2008 R2

    Linked server: WNRT1-WKS137\SQL2008R2DEV

     

    I am getting following error message:

    Msg 22050, Level 16, State 1, Line 0
    Error formatting query, probably invalid parameters
    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
    Query execution failed: OLE DB provider "SQLNCLI10" for linked server "WNRT1-WKS137\SQL2008R2DEV" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI10" for linked server "WNRT1-WKS137\SQL2008R2DEV" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance na
    me is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
    HResult 0xFFFF, Level 16, State 1
    SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

     

    strangely when I run the linked server other way around working fine. i.e,running the query from WNRT1-WKS137\SQL2008R2DEV by creating linked  server WNRT2-PROD1 on WNRT1-WKS137\SQL2008R2DEV

    Server Name: WNRT1-WKS137\SQL2008R2DEV

    SQL Version: 2008 R2 DEV

    OS: Windows 2008 R2

    Linked server: WNRT2-PROD1

     

    any idea...Thanks in advance

     

    PS:referred to the following link which is some what similar to this issue

    http://social.msdn.microsoft.com/Forums/eu/sqldatabaseengine/thread/885acdeb-a7fa-49c3-be59-79979a083bd1

     

     

     


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    Monday, January 30, 2012 8:40 PM

Answers

  • Hi Peja Tao,

     

    Thanks for suggestion. I did all you mentioned above. but no luck.

     

    Finally I am able to resolve the problem. I used SQL Login to create the linked server.

     

    Thanks,

    Brahma


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    • Edited by w2bsqldba Wednesday, February 01, 2012 6:23 PM uploaded image to show the security tab
    • Marked as answer by w2bsqldba Wednesday, February 01, 2012 6:23 PM
    Wednesday, February 01, 2012 5:16 PM

All replies

  • Hi Brahma,

    Please check the following setting on WNRT1-WKS137\SQL2008R2DEV:

    1. Remote connection on SQL Server

    2. Enable TCP/IP protocal in SQL Server Network Configuration

    3. Opening a Port (1433) in the Firewall

    4. Make both startup account (MSSQL SERVER & Agent) to the same account.

    More information,please refer to this online article.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, January 31, 2012 7:26 AM
  • Hi Peja Tao,

    Thanks for reply.!

    I want to mention one more thing here. I am having problem with WNRT2-PROD1 when i try to run the above query(in the 1st post). I am able to setup linked server( WNRT1-WKS137\SQL2008R2DEV) on WNRT2-PROD1 successfully and able to run the query from query analyzer.

    I am only getting problem with sp_send_dbmail with @query parameter.

    If i setup the linked server(WNRT2-PROD1) on WNRT1-WKS137\SQL2008R2DEV and able to run the above query (in 1st post) successfully.


     

    Please check the following setting on WNRT1-WKS137\SQL2008R2DEV:

    1. Remote connection on SQL Server yes

    2. Enable TCP/IP protocal in SQL Server Network ConfigurationYES

    3. Opening a Port (1433) in the Firewall YES (did it now)

    4. Make both startup account (MSSQL SERVER & Agent) to the same account. YES (both services running under local system account)

     

    Thanks,


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    Tuesday, January 31, 2012 2:21 PM
  • @Brahma,

    There are some suggestions you could follow:

    Please check whther the broker is enabled for msdb and the database you are running the query in. And comparing the sp_configure settings between the good and bad servers to check if they are identical for all mail related options is necessary.

    Have you tried to restart the SQL Agent on the servers where it fails? Please try to  execute the sp_send_dbmail proc using a domain account which has sa privileges .

    Apply the latest service packs.



     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 01, 2012 2:01 AM
  • Hi Peja Tao,

     

    Thanks for suggestion. I did all you mentioned above. but no luck.

     

    Finally I am able to resolve the problem. I used SQL Login to create the linked server.

     

    Thanks,

    Brahma


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    • Edited by w2bsqldba Wednesday, February 01, 2012 6:23 PM uploaded image to show the security tab
    • Marked as answer by w2bsqldba Wednesday, February 01, 2012 6:23 PM
    Wednesday, February 01, 2012 5:16 PM