April 18, 2011 at 3:04 pm
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..
April 18, 2011 at 3:11 pm
Where is the job hosted on?
Can you also post the connection properties of the linked Server
Thanks
Jagan K
April 18, 2011 at 3:32 pm
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'
April 18, 2011 at 3:42 pm
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
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 9:23 am
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply