Okay, I have a wierd one here. I have a SQL Server called PRODA (SQL2000 Standard, all latest patches applied) that has the Informix ODBC Driver installed (3.82.0000 2.81.TC1). I created a linked server on PRODA, pumped all security through a simgle Informix user, opened a query window and it worked great, no problem whatsoever.
The problem comes in when I try to access that linked server configuration from a different SQL Server, will call it PRODB (SQL2000 Enterprise, all patches). Here is the code I use from PRODB to query the Linked server on PRODA:
SET NOCOUNT ON
FROM OPENROWSET('SQLOLEDB', 'PRODA'; '<SQLServerUser>'; '<password>', 'SELECT * FROM uds.uds.informix.em_employee')
SET NOCOUNT OFF
Now this works fantastic, but only once! The first time I run it, I get the correct result set. If I simply click execute again, I get an
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Deferred prepare could not be completed.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset:
returned 0x80004005: ].
[OLE/DB provider returned message: [Informix][Informix ODBC Driver]Function sequence error.]
If I wait, say 30 seconds it works again, just once. Then it gives me the error again. It should also be noted that it always works fine from PRODA.
Why would I be doing it this way? Well, I have a seperate server (PRODA) for foriegn drivers so that I can do data lookups from foriegn Databases (Informix, DB2, Oracle, etc). The reason for this is because those drivers are not entirely stable and because the other systems our not in my control, there are versioning problems so I usually have to maintain multiple versions of the same drivers to access different DBs which causes problems. Instead of risking the health of my very important Production SQL Server (PRODB) with these drivers I have had success with this hands-off approach, until now.
Considering the behavior, I can't really blame the Informix driver in this case because the code always works fine on PRODA. Any ideas, or help would be appreciated.
Thanks for your time.