OLEDB connection from sql2005 to informix10 is flaky / non-functional
We are using the latest IBM OLEDB/ODBC drivers.
We have successfully created persistent ODBC connections to the
database with linked tables in Access2003.
We have created a linked server in sql server2005 sp3 (in SSMS) hosted
on windowsXP sp3.
We have been able to draw data from the linked server using openquery
().
OUR PROBLEM: the linked server connection is not consistently
persistent. It comes and goes.
Does anyone see anything wrong with our server options or anything
else?
----------------------------------------
technical info
The timeout setting is 10 minutes set at the server instance
Here is the sql command (run from the SSMS query window)
select * from openquery(OPD_PRINCEds, 'select * from
opddba.<tableName>'){a statement of this form has been used regularly without connection
issues against Oracle10}
When if fails, the messages tab of the result window says:
>Msg 7399, Level 16, State 1, Line 5
>The OLE DB provider "Ifxoledbc" for linked server "OPD_PRINCEds" reported an error.
>The provider did not give any information about the error.
>Msg 7303, Level 16, State 1, Line 5
>Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "OPD_PRINCEds".
the linked server script:
EXEC master.dbo.sp_addlinkedserver
@server = N'OPD_PRINCEds',
@srvproduct=N'informix',
@provider=N'Ifxoledbc',
@datasrc=N'opd',
@provstr=N'ifxoledbc'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'OPD_PRINCEds',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'opdread1',
@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OPD_PRINCEds',
@optname=N'use remote collation', @optvalue=N'true'
GO