locked
Linked Server with MSSQL and DB2 RRS feed

  • Question

  • Hello all,

    I'm trying to configure a Linked Server with DB2 (running on Windows as well) from the Management Studio as per the normal way:
    1. New Linked Server ->
    Linked Server: TESTSRV
    Server type: Other Data source
    Provider: Microsoft OLE DB for DB2
    Product Name: Something
    Data Source: Windows name of the computer
    Provider String: 
    Provider=DB2OLEDB;User ID=Administrator;Initial Catalog=SAMPLE;Network Transport Library=TCPIP;Host CCSID=1252;PC Code Page=1252;Network Address=10.100.1.2;Network Port=50000;
    Package Collection=P6186270;Default Schema=ADMINISTRATOR;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Database Name=SAMPLE;Authentication=Server;
    Decimal As Numeric=False;Derive Parameters=True;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;
    Catalog: SAMPLE

    I also configured a DSN through the Data Access Tool (provided by Microsoft's OLE DB Driver for DB2, and not through the Windows ODBC Data Sources utility) called TESTSRV 
    (the Windows hostname of the DB2 inst.), with the following configuration:
    Data Source platform:
    DB2/NT
    Network Transport Library: 
    TCP/IP

    Address or Alias: 10.100.1.2
    Port: 50000
    Certificate Common Name: <none>

    Initial Catalog: SAMPLE
    Package Collection: P6186270
    Default Schema: Administrator
    Default qualifier: <none>
    Database name: SAMPLE

    Host CCSID: ANSI - Latin I (1252)
    PC Code Page: ANSI - Latin I (1252)

    Security method: Interactive sign-on
    Username: Administrator (Windows account login)
    Password: <password> (Windows account password)
    Authentication method: Server
    Derived Parameters: <checked>

    Although the connection is successful using the Windows credentials of the DB2 server, the sample query fails with:

    Could not connect to data source 'TESTSRV':
    One or more errors occurred during processing of command.
    Error in BIND or PRECOMPILE option "KEE?0xF1" with value "". SQLSTATE: 56095, SQLCODE: -30104

    The same error message is displayed when I try to query the server with OPENQUERY
    select * from openquery(TESTSRV, 'select * from sample.act')

    Additionally, when I try to expand the Linked Server entry's 'Tables' from the Object Explorer I get the following
    "Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
    Additional information:
    ->An exception occured while executing a Transact-SQL statement or batch.
      (Microsoft.SqlServer.ConnectionInfo)
      ->Cannot fetch a row from OLE DB Provider "SQL Server" for linked server "(null)". (Microsoft SQL Server, Error:7330)


    PS: The package name was discovered with using 'LIST PACKAGES for schema' command. I tried some others as well in case this wasn't correct, but I got the same result.
    Wednesday, April 1, 2015 1:38 PM

Answers

  • Hi ,

    1) run query

    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO

    2) 

    Step 1. Grant rigths to TEMP directory

    icacls C:\Windows\ServiceProfiles\<SQL Server Account>\AppData\Local\Temp /grant <User>:(R,W)

    The most commonly used pathes:

    C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
    C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

    Monday, April 6, 2015 10:24 AM
  • Hi,

    The error message reported here is quite general, we could not get much useful information from the error message here. Could you please try the IBM OLEDB DB2 provider described in the following document?

    How to create a linked server with IBM DB2 OLEDB provider

    http://www-01.ibm.com/support/docview.wss?uid=swg21394344

    If it does not work also, I suspect that the problem should be related to the DB2 instance you specified. You could also open a support ticket to Microsoft to request help.

    Wednesday, April 15, 2015 1:56 AM

All replies

  • Hi Ioakim,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Regards,
    Michelle Li

    Thursday, April 2, 2015 5:55 AM
  • Hi ,

    1) run query

    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO

    2) 

    Step 1. Grant rigths to TEMP directory

    icacls C:\Windows\ServiceProfiles\<SQL Server Account>\AppData\Local\Temp /grant <User>:(R,W)

    The most commonly used pathes:

    C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
    C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

    Monday, April 6, 2015 10:24 AM
  • Hi,

    The error message reported here is quite general, we could not get much useful information from the error message here. Could you please try the IBM OLEDB DB2 provider described in the following document?

    How to create a linked server with IBM DB2 OLEDB provider

    http://www-01.ibm.com/support/docview.wss?uid=swg21394344

    If it does not work also, I suspect that the problem should be related to the DB2 instance you specified. You could also open a support ticket to Microsoft to request help.

    Wednesday, April 15, 2015 1:56 AM