Creating a linked server to DB2 within SQL Server Management Studi

Discussion:

(too old to reply)

samadkhan

2008-06-30 11:33:02 UTC

Hi,

I've successfully created a Linked server within SQL Server 2005 Management
Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I right
click the linked server and select 'Test Connection' and get a message 'The
test connection to linked server succeeded'.

However, when I try to expand Catalog node I receive the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "SQL Server" for linked server "(null)" reported an
error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL
Server" for linked server "(null)". The provider supports the interface, but
returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

At the time of posting this question, if I follow the link in the error
message, there was no information on the error.

Any help much appreciated.

Thanks in advance,
Samad Khan

Russell Fields

2008-06-30 17:10:04 UTC

Permalink

Samad Khan,

Please right click on the Linked Server definition and Script Linked Server
As... \Create. Paste the create script into a response so that the group
can see the details of the linked server definition.

Thanks,
RLF

Post by samadkhan
Hi,
I've successfully created a Linked server within SQL Server 2005 Management
Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I right
click the linked server and select 'Test Connection' and get a message 'The
test connection to linked server succeeded'.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "SQL Server" for linked server "(null)" reported an
error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL
Server" for linked server "(null)". The provider supports the interface, but
returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
OK
------------------------------
At the time of posting this question, if I follow the link in the error
message, there was no information on the error.
Any help much appreciated.
Thanks in advance,
Samad Khan

jemsws

2008-07-08 19:30:43 UTC

Permalink

Linked Server Text
/****** Object: LinkedServer [CMH] Script Date: 07/08/2008 10:29:12
******/
EXEC master.dbo.sp_addlinkedserver @server = N'CMH', @srvproduct=N'CMH',
@provider=N'MSDASQL', @datasrc=N'DsnCMH'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'CMH',@useself=N'False',@locallogin=NULL,@rmtuser=N'LINKITG',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'rpc out',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'connect timeout',
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'collation name',
@optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'query timeout',
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'use remote
collation', @optvalue=N'true'
--
Trying to get this done with the least amount of effort

Post by Russell Fields
Samad Khan,
Please right click on the Linked Server definition and Script Linked Server
As... \Create. Paste the create script into a response so that the group
can see the details of the linked server definition.
Thanks,
RLF

samadkhan

2008-07-28 11:29:01 UTC

Permalink

Hi Russell,

Thank you for responsing. I've extracted the script as follows:

/****** Object: LinkedServer [DB2LINKSERVER] Script Date: 07/28/2008
11:13:59 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB2LINKSERVER',
@srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB',
@datasrc=N'DB2_DATABASE_SERVER', @provstr=N'Provider=DB2OLEDB;User
ID=db2admin;Password=db2admin;Initial Catalog=DB2DATABASENAME;Network
Transport Library=TCP;Host CCSID=1252;PC Code Page=1252;Network
Address=DB2_DATABASE_SERVER;Network Port=50000;Package
Collection=DB2_STAGING;Default Schema=DB2_STAGING;Process Binary as
Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Defer
Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection
Pooling=False;Derive Parameters=False;', @catalog=N'DB2DATABASENAME'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'DB2LINKSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'db2admin',@rmtpassword='db2admin'

GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'rpc',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'rpc
out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'connect
timeout', @optvalue=N'30'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'use
remote collation', @optvalue=N'true'

Any help is appricated.

Thanks,
Samad

Russell Fields

2008-08-02 15:01:27 UTC

Permalink

Samad,

Your script looks OK to me, to the extent that I can tell. Unfortunately, I
do not have DB2 to test against, so I can only go so far with the testing
However, doing a little more research I came across this KB

http://support.microsoft.com/kb/222937/EN-US/ which has the following:
1. Comments on running the DB2OLEDB provider in-process.
2. The total length of the linked server initstring must be no more than 278
characters, so it is advantageous to use the DB2OLEDB short connection
string arguments as documented above.

Since your connection string is over 400 characters long, perhaps that is
causing you some problem. Also check for the in-process setting.

Some other links of interest include:
http://support.microsoft.com/kb/218590/EN-US/
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=582877&SiteID=17

RLF

samadkhan

2008-08-04 10:08:05 UTC

Permalink

Thanks Russell,

I'm trying to shortten the connection string and will let you know how I get
on.

Enjoy,
Samad

samadkhan

2008-08-15 10:51:02 UTC

Permalink

Hi Russell,

I didn't have much joy using DB2 driver. The connection was still more than
278 characters in length due to the configuration I had to supply. I did have
more luck using IBMDADB2 driver. The script below will create the link server:

/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 11:28:41
******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver
@server=N'LINK_SERVER_NAME', @droplogins='droplogins'

/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 11:28:36
******/
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_SERVER_NAME',
@srvproduct=N'IBMDADB2', @provider=N'IBMDADB2', @datasrc=N'LINK_SERVER_NAME',
@location=N'DB2_SERVER', @provstr=N'Data Source=DATABASE_NAME;User
ID=DB_USERNAME;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security
Info=True;Location=DB2_SERVER;'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USERNAME',@rmtpassword='DB_PASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'rpc
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'use
remote collation', @optvalue=N'true'

However I couldn't view stored procedures, table data, column information.
And within query analyser I can't return data.

I get a the error:
The OLE DB provider IBMDADB2 for linked server supplied inconsistent
metadata for a column. The column "COLUMN_NAME" (compile-time ordinal 1) of
object ""SCHEMA_NAME"."TABLE_NAME"" was reported to have a "DBTYPE" of 129 at
compile time and 130 at run time.

I think it may be a collation issue but will let you know when it's sorted,
or you have any ideas?

Thanks again,
Samad

Russell Fields

2008-08-15 13:50:12 UTC

Permalink

samadkhan,

Yes, this is a problem of different storage types across different database
software. Here is an IBM link:
http://www-1.ibm.com/support/docview.wss?uid=swg1JR19327

Also, a similar problem linking to Oracle:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21278440.html

In both, the answer was to use OPENQUERY.

RLF

Post by samadkhan
Hi Russell,
I didn't have much joy using DB2 driver. The connection was still more than
278 characters in length due to the configuration I had to supply. I did have
/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 11:28:41
******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver
@server=N'LINK_SERVER_NAME', @droplogins='droplogins'
/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 11:28:36
******/
@srvproduct=N'IBMDADB2', @provider=N'IBMDADB2',
@datasrc=N'LINK_SERVER_NAME',
@location=N'DB2_SERVER', @provstr=N'Data Source=DATABASE_NAME;User
ID=DB_USERNAME;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security
Info=True;Location=DB2_SERVER;'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USERNAME',@rmtpassword='DB_PASSWORD'
GO
@optname=N'collation compatible', @optvalue=N'false'
GO
@optname=N'data
GO
@optname=N'dist', @optvalue=N'false'
GO
@optname=N'pub', @optvalue=N'false'
GO
@optname=N'rpc', @optvalue=N'true'
GO
@optname=N'rpc
GO
@optname=N'sub', @optvalue=N'false'
GO
@optname=N'connect timeout', @optvalue=N'0'
GO
@optname=N'collation name', @optvalue=null
GO
@optname=N'lazy
GO
@optname=N'query timeout', @optvalue=N'0'
GO
@optname=N'use
However I couldn't view stored procedures, table data, column information.
And within query analyser I can't return data.
The OLE DB provider IBMDADB2 for linked server supplied inconsistent
metadata for a column. The column "COLUMN_NAME" (compile-time ordinal 1) of
object ""SCHEMA_NAME"."TABLE_NAME"" was reported to have a "DBTYPE" of 129 at
compile time and 130 at run time.
I think it may be a collation issue but will let you know when it's sorted,
or you have any ideas?
Thanks again,
Samad

samadkhan

2008-08-15 15:58:02 UTC

Permalink

Great minds think alike :)

I just found the article myself half an hour ago and tested it. It worked!!!

I had to use Openquery method to execute any sql, but it works.

eg:
select * from OPENQUERY(SAMPLE, 'select * from EMPLOYEE')
instead of:
select * from SAMPLE..SCHEMA_NAME.EMPLOYEE

I've scripted the Linked server if anyone requires it - and save them the
blood and sweat I weant through :)

/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 16:45:52
******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver
@server=N'LINK_SERVER_NAME', @droplogins='droplogins'

/****** Object: LinkedServer [LOANIQ] Script Date: 08/15/2008 16:46:01
******/
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_SERVER_NAME',
@srvproduct=N'IBMDADB2.1', @provider=N'IBMDADB2',
@datasrc=N'LINK_SERVER_NAME', @location=N'DB2_SERVER', @provstr=N'Data
Source=DB2_DATABASENAME;User
ID=DB_USER;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security
Info=True;Location=DB2_SERVER;'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USER',@rmtpassword='DB_PASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'rpc
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'collation name', @optvalue=N'Latin1_General_CI_AS'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'use
remote collation', @optvalue=N'true'
GO

Thanks for all your help Russell and the quick response.

Enjoy,
Samad