Unknown cause of linked server error

Question

We have an old Windows 2000 server running SQL Server 2000 Standard Edition, SP3. This has a linked server setup to connect to an Informix DB via an ODBC connection. For most of the year, running OPENQUERY against the linked server works fine without any problem. However, every few months this linked server fails, and does not work again until the SQL Server is rebooted. The error we see in Enterprise Manager is this :

---------------------------
SQL Server Enterprise Manager
---------------------------
Error 7399: OLE DB provider 'UNKNOWN' reported an error.  

Memory allocation failure.]
Memory allocation failure.]
OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80004005:   ].
---------------------------
OK   
---------------------------

If I try running any SQL query against the linked server in Query Analyzer, the error reported is this :

[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBCreateCommand::CreateCommand returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.

If I apply the TRACE flags referenced here I get a little more detail :

OLE DB error trace [Non-interface error:  OLE DB provider MSDASQL
returned an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should
be either DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBCreateCommand::CreateCommand returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.

Any ideas on what could cause this? We're not aware of any server failures or changes to settings, and a reboot always seems to fix this. The Informix DB appears to be version Informix Dynamic Server 2000 9.21.UC2, and upgrading or patching it isn't available as an option.

Answer 1

Be aware that IDS 9.21 is long out of support - indeed, both 9.30 and 9.40 (the successor versions of IDS) are also out of support. You are years overdue for an upgrade, even though it appears that you cannot actually do the upgrade for political reasons.

It appears that there is a memory leak somewhere on the SQL Server side, maybe caused by a memory leak in the Informix ODBC driver that you are using.

If you cannot upgrade anything, then a periodic reboot of SQL Server to clear the accumulated memory is the simplest, most reliable option. Since you imply it works for months at a time usually, I'd look at rebooting once a month or so.