Post reply

Linked Server Query Timeout

  • January 6, 2011 at 12:49 pm

    #231839

    Hi,

    I am running a script to bring in data from our SourceServer (SS2008) to our DestServer (SS2005). SourceServer was SS2005, we had no issues even with timeout set to 600. With SS2008, the same script is timing out well before 600 timeout setting. To be on safer side, I changed the timeout setting from 600 (10 minutes) to 0 (unlimited). Following is the error message:

    OLE DB provider "SQLNCLI" for linked server "SourceServer" returned message "Query timeout expired".

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "SourceServer".

    Any Idea??

    Thanks for your help.

    Shriji

  • January 6, 2011 at 1:52 pm

    #1270314

    how are you importing the data?

    using SSIS or what?

    Regards
    Sushant Kumar
    MCTS,MCP

  • January 6, 2011 at 1:56 pm

    #1270318

    you changed the timeout setting on whcih server- source or the destination?

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • January 6, 2011 at 2:24 pm

    #1270335

    Thank you guys. it is fixed!!!

    We are importing data as "Select * into....." through sql script which we run in SSIS. For remote server properties, connection timeout and query time out was set to 0 by default. I did google found out that connection timeout 0 means 20 secs (i dont know it is true or not) but I followed it and changed it to 3600 and query time to 7200. it solved the issue.

    I came to know one more thing that Remote Query Timeout setting a server level actually is for outgoing connection and it has NO impact on incoming connection. I was mistakenly tyring to configure the "SourceServer". Then after the research, I changed the settings under remote connection properties of the "DestinationServer".

    Thanks

    Shriji

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

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