Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
allend2010
Starting Member
28 Posts |
 Posted - 2003-06-24 : 09:09:22
|
| Hello:I was wondering if anybody is familar with linking servers with DB2, I have used the following in my sp:exec sp_addlinkedserver 'DB2TEST', '', 'IBMDADB2', 'TESTENV'exec sp_addlinkedsrvlogin 'DB2TEST', 'false', NULL, 'myUID', 'myPWD'but when I try to execute a query using the full schema path table name (ie SELECT * FROM DB2TEST..TEST.FIGURES, I am getting the following error:"Error 7311: Could not obtain the schema rowset for OLE DB provider 'IBMDADB2'. The provider supports the interface, but returns a failure code when it is used."Likewise, if I use OPENQUERY, (ie SELECT * FROM OPENQUERY(SELECT * FROM DB2TEST..TEST.FIGURES')) I get a different error:"Error 7350: Could not process object 'SELECT * FROM DB2TEST..TEST.FIGURES'. Could not get the column information from the OLE DB provider 'IBMDADB2'."I have tried looking into these error codes but without much success and IBM doesn't have too much available that I could find for troubleshooting. Any insight or help anybody could provide would be greatly appreciated here. PS I was able to connect using the ODBC provider but that is not my preferred method in this case. Thanks,Allen D. |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-24 : 09:47:12
|
"Come in, DB2Man...Come in!! This is an emergency!! Calling DB2Man!!"Our own DB2 expert should be here any moment.  |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-24 : 12:17:42
|
quote:
"Come in, DB2Man...Come in!! This is an emergency!! Calling DB2Man!!"Our own DB2 expert should be here any moment.
Don't look at me, I've only done it through a gateway to the mainframe.What platform are you trying to connect to? AS400? Unix?All my DB2 development is platform centric...Now if you want to know how to create a db2 sproc, partion tablespaces, unload using DSNTIAUL, (yada yada yada), I'm on call.Brett8-) |
|
|
|
allend2010
Starting Member
28 Posts |
Posted - 2003-06-24 : 13:06:32
|
| In this case, I am connecting to a Mainframe using MS SNA Server (4.0) as gateway on the DB2 client, I just specify the DB alias and it works fine through ODBC. It's just the IBM OLE DB driver that is giving me a problem. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-24 : 13:10:15
|
| I would bet it's that IBM and MS aren't playing nice again.Send them to their rooms until their learn how to behave.Seriously I think the IBM Driver is having a problem with MS SNA Server.But I bet it work just dandy with IBM's DB2 Connect.Brett8-) |
|
|
|
Bradysama
Starting Member
1 Post |
Posted - 2007-09-18 : 10:57:31
|
| This problem really drove me nuts, but I figured out what was causing it. What you need to do is check the 'Allow InProcess' box when you are picking the provider during the linked server creation process. Once you have made the linked server, you have probably noticed that the 'Provider Options' button is disabled - you need to do this before you click 'OK' when initially setting up the linked server. IBM seems to have no documentation addressing this issue - I found something on the troubleshooting page for a completely different OLE DB provider. Here is the text that steered me in the right direction:Allow InProcess - This option should be checked. Checking this option stops errors like:Error 7311: Could not obtain the schema rowset for OLE DB provider 'UNKNOWN'. The provider supports the interface, but returns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x800706c6:]. Error 0x800706c6 is RPC_S_INVALID_BOUND. The error message identifies the OLE DB provider that the error comes from as UNKNOWN and our log files show no sign of the error. SQL Server uses a proxy/stub dll, msdaps.dll, to marshal oledb interfaces between processes/apartments. These errors from the 'UNKNOWN' provider come from msdaps.dll. If the OLE DB provider is invoked out of process or is marked as apartment model then this stub gets used. By default if the OLE DB provider is a Microsoft provider then SQL Server invokes it in process and this proxy is not used. Checking the Allow InProcess box when using our provider ensures that this stub dll is not used and so avoids this error. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-09-25 : 10:52:46
|
| Hi all, I was able to make a linked server to DB2 with ODBC with System DSN. General tab:Other data source -> Microsoft OLE DB Provider for ODBC DriverProduct name: <db2server>Data source: <system dsn name>In Security tab choose Be made using this security context:Remote login: userWith Password: pwd |
|
|
|
|
|
|
|
|
Linking to DB2 Via IBM OLE DB Provider