linked server connection SQL 2008 to SQL 2005 timeouts

Question

Post reply

linked server connection SQL 2008 to SQL 2005 timeouts

dba_mb

SSC-Addicted

Points: 431
More actions
April 18, 2011 at 3:04 pm

#237862

We have a problem with a linked server connection from SQL Server 2008 to SQL server 2005 using SQL Native client 10.0 as provider for the connection. There is a purge job running on the Integration server SQL 2008 executing against production - SQL 2005
The job was reporting success on completion while the app team reported that it actually was NOT. Also there were timeouts being experienced. The message being
--------
Message
Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider "SQLNCLI10" for linked server "PROD" returned message "Query timeout expired". [SQLSTATE 01000] (Message 7412). The step succeeded.
--------
When I checked the query timeout was at ‘0’. I didn't know if altering the timeout value would help. or was it a case of the native client 10.0 being the provider?
Please help me out..

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

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

jvkondapalli SSCommitted Points: 1587 More actions · Answer 1

Where is the job hosted on?

Can you also post the connection properties of the linked Server

Thanks
Jagan K

dba_mb SSC-Addicted Points: 431 More actions · Answer 2

the connection is made using

'be made using this security context' option. The login has database ownership on production

The other parameters of the linked server connection are -

It is configured for Data Access, RPC and RPC out.

Query timeout is at '0', Connection timeout is also at '0'

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

Install SQL Server 2005 native client and then create a DSN then create the linked Server with it and change the query time out value also it should be 250 to 300 default and there is also login time out also if this 0 then it must be 10 to 20

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com

dba_mb SSC-Addicted Points: 431 More actions · Answer 4

thank you,

Please correct me if I'm wrong, but I thought in linked server properties -> security options -> query timeout '0' meant unlimited.

I did notice another thing - on the server settings,

connections->remote server connections (is enabled of course and) had remote query timeout set at 600.

I was planning to increase the timeout limit for remote query timeout. The step in the job had executed for about 12 mins and finished as ‘successful’, yet with the message "timed out".

What has me a bit tangled however is that the server properties->connections->remote connections check box says allow remote connections to this server. And right below is the remote query timeout value at 600 which can be set to unlimited. So, I am at the prod server, assuming this would be the place to change the value, while the job runs from Integration server. But, when I look at documentation from MS, regarding remote query timeout value - “The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.”

So on the server properties it says "allow remote connections to this server" and has the value we can set, while the documentation says this value applies to outgoing connection initiated by the Database engine and has no effect on queries received by the database engine. Am I missing the point or is there a discrepancy?