Linked Server Problem with Informix ODBC Driver
From: Chris Crane (chris.crane_at_frontiercorp.com)
Date: 10/08/04
- Next message: Shabam: "String searching"
- Previous message: Ramon: "Choosing database system"
- Messages sorted by: [ date ] [ thread ]
Date: 8 Oct 2004 06:45:38 -0700
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
SELECT *
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
error:
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::OpenRowset
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.
...Chris
- Next message: Shabam: "String searching"
- Previous message: Ramon: "Choosing database system"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
