Linked Server Hanging
in [Programming SQL]
|
Prev: When does a query/subquery return a NULL and when no value atall, not even a NULL?
Next: SELECT vs SET
From: Frank on 28 Apr 2010 11:06 Hi, I'm having a problem with an Oracle linked server that I'm attempting to connect to from SQL Server 2008 (64-bit Windows 2008). When attempting to use the linked server or even right-click it in SSMS and select 'Test Connection', SQL Server hangs completely and it requires a restart of the physical server that SQL Server is hosted on to bring it back. Some strange things though: A linked server to the same Oracle database with the same connection details and attributes works correctly through a Java application interface (using JDBC). A linked server to the same Oracle database with the same connection details and attributes works correctly from SQL Server 2005 running on a Windows 2003 server (32-bit). The linked server DID work correctly from our SQL Server 2008 instance (that it now fails on) for a few months up until last week. I've spoken to the Oracle DBA, who can see no problems at his end. The sys admin on the Windows 2008 server insists that no changes have happened in the past week. Under normal circumstances when clicking on a linked server where there is a connectivity issue, the linked server usually responds pretty much immediately with an error, so I can't work out what's it's actually trying to do. First of all, has anyone else come across a similar issue? Secondly, is there any way to get more information about the issue than we are currently seeing i.e. any log file that might contain a clue as to what the problem is? I've posted the code to create the linked server below for completeness, but I don't expect there to be a problem with it as it has worked correctly for several months. EXEC master.dbo.sp_addlinkedserver @server = N'Server', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Server.Domain.Com' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server',@useself=N'False',@locallogin=NULL,@rmtuser=N'#########',@rmtpassword='########' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'rpc', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'use remote collation', @optvalue=N'true' We've also tried setting the 'connect timeout' and the 'query timeout' to values other than 0, to see if that will help but without any luck. Any help or thoughts greatly appreciated. Thanks, Frank.
From: TheSQLGuru on 28 Apr 2010 13:12
Things like this are usually the result of the connectivity driver. make sure you have the latest version of it. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Frank" <francis.moore(a)gmail.com> wrote in message news:ca67bfdf-55ac-4813-8bb1-f25ee4ce07e3(a)u31g2000yqb.googlegroups.com... > Hi, > > I'm having a problem with an Oracle linked server that I'm attempting > to connect to from SQL Server 2008 (64-bit Windows 2008). When > attempting to use the linked server or even right-click it in SSMS and > select 'Test Connection', SQL Server hangs completely and it requires > a restart of the physical server that SQL Server is hosted on to bring > it back. > > Some strange things though: > > A linked server to the same Oracle database with the same connection > details and attributes works correctly through a Java application > interface (using JDBC). > A linked server to the same Oracle database with the same connection > details and attributes works correctly from SQL Server 2005 running on > a Windows 2003 server (32-bit). > The linked server DID work correctly from our SQL Server 2008 instance > (that it now fails on) for a few months up until last week. > > I've spoken to the Oracle DBA, who can see no problems at his end. > The sys admin on the Windows 2008 server insists that no changes have > happened in the past week. > Under normal circumstances when clicking on a linked server where > there is a connectivity issue, the linked server usually responds > pretty much immediately with an error, so I can't work out what's it's > actually trying to do. > > First of all, has anyone else come across a similar issue? > Secondly, is there any way to get more information about the issue > than we are currently seeing i.e. any log file that might contain a > clue as to what the problem is? > > I've posted the code to create the linked server below for > completeness, but I don't expect there to be a problem with it as it > has worked correctly for several months. > > EXEC master.dbo.sp_addlinkedserver @server = N'Server', > @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', > @datasrc=N'Server.Domain.Com' > EXEC master.dbo.sp_addlinkedsrvlogin > @rmtsrvname=N'Server',@useself=N'False',@locallogin=NULL,@rmtuser=N'#########',@rmtpassword='########' > EXEC master.dbo.sp_serveroption @server=N'SERVER', > @optname=N'collation compatible', @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'data > access', @optvalue=N'true' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'dist', > @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'pub', > @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'rpc', > @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'rpc > out', @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'sub', > @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'connect > timeout', @optvalue=N'0' > EXEC master.dbo.sp_serveroption @server=N'SERVER', > @optname=N'collation name', @optvalue=null > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'lazy > schema validation', @optvalue=N'false' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'query > timeout', @optvalue=N'0' > EXEC master.dbo.sp_serveroption @server=N'SERVER', @optname=N'use > remote collation', @optvalue=N'true' > > We've also tried setting the 'connect timeout' and the 'query timeout' > to values other than 0, to see if > that will help but without any luck. > > Any help or thoughts greatly appreciated. > > Thanks, > Frank. |