SQL Server DB2 Windows Linked Server Error   

Hello,

I am getting following error when I execute a select query against db2 
linked server on sqlserver 2005.

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "IBMDADB2" for 
linked server "IBMDB4".

Following is my select query 
Select * FROM IBMDB4.VIENNA.NIMESH.INVENTORY_TBL

Following is my Linked server sp
EXEC sp_addlinkedserver
@server = 'IBMDB4',
@srvproduct = 'IBM OLE DB Provider for DB2',
@catalog = 'VIENNA',
@datasrc= 'VIENNA',
@provider = 'IBMDADB2',
@provstr='NetLib=tcpip;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
ID=db2admin;Password=code2win;InitCat=VIENNA;Default 
Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
EXEC sp_addlinkedsrvlogin 'IBMDB4', false, 'ARES\NIMESH', 'DB2ADMIN', 
'code2win'


Please need your help

date: Fri, 13 Jun 2008 06:59:01 -0700   author:   Nimesh Gajjar

Re: SQL Server DB2 Windows Linked Server Error   

Nimesh - that's an IBM supplied driver by the looks of it - you'll need to ask 
in a DB2 forum.

 Is MS HIS involved at all?!?
 
> I am getting following error when I execute a select query against db2 
> linked server on sqlserver 2005.
> 
> Msg 7313, Level 16, State 1, Line 1
> An invalid schema or catalog was specified for the provider "IBMDADB2" for 
> linked server "IBMDB4".
> 
> Following is my select query 
> Select * FROM IBMDB4.VIENNA.NIMESH.INVENTORY_TBL
> 
> Following is my Linked server sp
> EXEC sp_addlinkedserver
> @server = 'IBMDB4',
> @srvproduct = 'IBM OLE DB Provider for DB2',
> @catalog = 'VIENNA',
> @datasrc= 'VIENNA',
> @provider = 'IBMDADB2',
> @provstr='NetLib=tcpip;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
> ID=db2admin;Password=code2win;InitCat=VIENNA;Default 
> Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
> EXEC sp_addlinkedsrvlogin 'IBMDB4', false, 'ARES\NIMESH', 'DB2ADMIN', 
> 'code2win'
> 
> Please need your help


 Neil Pike.  Protech Computing Ltd
 Microsoft SNA/HIS MVP
 https://mvp.support.microsoft.com/profile=BE66F0D8-9D78-47EF-840A-08E6D8522A2D
 http://www.linkedin.com/in/neilpike

date: Fri, 13 Jun 2008 17:46:12 +0100   author:   Neil Pike

Re: SQL Server DB2 Windows Linked Server Error   

Neil I tried MS DB2 Driver as shoen below and get error.

OLE DB provider "DB2OLEDB" for linked server "IBMDB3" returned message "The 
parameter is incorrect.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DB2OLEDB" for linked server "IBMDB3" reported an error. 
One or more arguments were reported invalid by the provider.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for 
linked server "IBMDB3".

EXEC sp_addlinkedserver
@server = 'IBMDB3',
@srvproduct = 'MICROSOFT OLE DB Provider for DB2',
@catalog = 'VIENNA',
@provider = 'DB2OLEDB',
@provstr='NetLib=TCPIP;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
ID=DB2ADMIN;Password=code2win;InitCat=VIENNA;Default 
Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
EXEC sp_addlinkedsrvlogin 'IBMDB3', false, 'ARES\NIMESH', 'DB2ADMIN', 
'code2win'


"Neil Pike" wrote:

>  Nimesh - that's an IBM supplied driver by the looks of it - you'll need to ask 
> in a DB2 forum.
> 
>  Is MS HIS involved at all?!?
>  
> > I am getting following error when I execute a select query against db2 
> > linked server on sqlserver 2005.
> > 
> > Msg 7313, Level 16, State 1, Line 1
> > An invalid schema or catalog was specified for the provider "IBMDADB2" for 
> > linked server "IBMDB4".
> > 
> > Following is my select query 
> > Select * FROM IBMDB4.VIENNA.NIMESH.INVENTORY_TBL
> > 
> > Following is my Linked server sp
> > EXEC sp_addlinkedserver
> > @server = 'IBMDB4',
> > @srvproduct = 'IBM OLE DB Provider for DB2',
> > @catalog = 'VIENNA',
> > @datasrc= 'VIENNA',
> > @provider = 'IBMDADB2',
> > @provstr='NetLib=tcpip;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
> > ID=db2admin;Password=code2win;InitCat=VIENNA;Default 
> > Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
> > EXEC sp_addlinkedsrvlogin 'IBMDB4', false, 'ARES\NIMESH', 'DB2ADMIN', 
> > 'code2win'
> > 
> > Please need your help
> 
> 
>  Neil Pike.  Protech Computing Ltd
>  Microsoft SNA/HIS MVP
>  https://mvp.support.microsoft.com/profile=BE66F0D8-9D78-47EF-840A-08E6D8522A2D
>  http://www.linkedin.com/in/neilpike
> 
> 
>

date: Fri, 13 Jun 2008 10:19:00 -0700   author:   Nimesh Gajjar

Re: SQL Server DB2 Windows Linked Server Error   

Nimesh,

Try creating the UDL for the Microsoft OLE DB Provider for DB2 using the 
Data Access Tool (DAT). After you do this and you can successfully run the 
test connection and sample query option in the DAT, then try using the UDL 
(or connection string info) in the Linked Server.

Thanks...

-- 
Stephen Jackson
Microsoft® HIS Support

Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only. This posting is provided "AS IS"
with no warranties, and confers no rights.


"Nimesh Gajjar"  wrote in message 
news:C2CF8823-F79E-47F9-9D98-D6A03CF20A1C@microsoft.com...
> Neil I tried MS DB2 Driver as shoen below and get error.
>
> OLE DB provider "DB2OLEDB" for linked server "IBMDB3" returned message 
> "The
> parameter is incorrect.".
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "DB2OLEDB" for linked server "IBMDB3" reported an 
> error.
> One or more arguments were reported invalid by the provider.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for
> linked server "IBMDB3".
>
> EXEC sp_addlinkedserver
> @server = 'IBMDB3',
> @srvproduct = 'MICROSOFT OLE DB Provider for DB2',
> @catalog = 'VIENNA',
> @provider = 'DB2OLEDB',
> @provstr='NetLib=TCPIP;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User
> ID=DB2ADMIN;Password=code2win;InitCat=VIENNA;Default
> Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
> EXEC sp_addlinkedsrvlogin 'IBMDB3', false, 'ARES\NIMESH', 'DB2ADMIN',
> 'code2win'
>
>
> "Neil Pike" wrote:
>
>>  Nimesh - that's an IBM supplied driver by the looks of it - you'll need 
>> to ask
>> in a DB2 forum.
>>
>>  Is MS HIS involved at all?!?
>>
>> > I am getting following error when I execute a select query against db2
>> > linked server on sqlserver 2005.
>> >
>> > Msg 7313, Level 16, State 1, Line 1
>> > An invalid schema or catalog was specified for the provider "IBMDADB2" 
>> > for
>> > linked server "IBMDB4".
>> >
>> > Following is my select query
>> > Select * FROM IBMDB4.VIENNA.NIMESH.INVENTORY_TBL
>> >
>> > Following is my Linked server sp
>> > EXEC sp_addlinkedserver
>> > @server = 'IBMDB4',
>> > @srvproduct = 'IBM OLE DB Provider for DB2',
>> > @catalog = 'VIENNA',
>> > @datasrc= 'VIENNA',
>> > @provider = 'IBMDADB2',
>> > @provstr='NetLib=tcpip;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User
>> > ID=db2admin;Password=code2win;InitCat=VIENNA;Default
>> > Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
>> > EXEC sp_addlinkedsrvlogin 'IBMDB4', false, 'ARES\NIMESH', 'DB2ADMIN',
>> > 'code2win'
>> >
>> > Please need your help
>>
>>
>>  Neil Pike.  Protech Computing Ltd
>>  Microsoft SNA/HIS MVP
>> 
>> https://mvp.support.microsoft.com/profile=BE66F0D8-9D78-47EF-840A-08E6D8522A2D
>>  http://www.linkedin.com/in/neilpike
>>
>>
>>

date: Fri, 13 Jun 2008 15:09:44 -0500   author:   Stephen Jackson [MSFT]

Re: SQL Server DB2 Windows Linked Server Error   

The below mentioned is not a valid  db2oledb connectionstring :

> @provstr='NetLib=TCPIP;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
> ID=DB2ADMIN;Password=code2win;InitCat=VIENNA;Default 
> Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'

Microsoft Db2oledb provider's connectionstring looks something like this:

Provider=DB2OLEDB;User ID=USERID;Password=PASSWORD;Initial 
Catalog=CATALOGNAME;Network Transport Library=SNA;Host CCSID=37;PC Code 
Page=1252;Network Address=HOSTIP;Network Port=PORTNUMBER;Package 
Collection=USERID;Default Schema=USERID;Process Binary as 
Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;Defer 
Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Derive 
Parameters=False;

Replace appropriate parameters in above connectionstring  and try using it .

As Steve mentioned you can also use DAT ( data Access Tool) . Go to Start -> 
Programs -> (HIS product Menu) and select Data Access Tool. in Data Access 
Tool , go to File ->New ->  Data Source . The Wizard should guide you  
through  the process.

HTH,
Asmita


"Nimesh Gajjar" wrote:

> Neil I tried MS DB2 Driver as shoen below and get error.
> 
> OLE DB provider "DB2OLEDB" for linked server "IBMDB3" returned message "The 
> parameter is incorrect.".
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "DB2OLEDB" for linked server "IBMDB3" reported an error. 
> One or more arguments were reported invalid by the provider.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for 
> linked server "IBMDB3".
> 
> EXEC sp_addlinkedserver
> @server = 'IBMDB3',
> @srvproduct = 'MICROSOFT OLE DB Provider for DB2',
> @catalog = 'VIENNA',
> @provider = 'DB2OLEDB',
> @provstr='NetLib=TCPIP;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
> ID=DB2ADMIN;Password=code2win;InitCat=VIENNA;Default 
> Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
> EXEC sp_addlinkedsrvlogin 'IBMDB3', false, 'ARES\NIMESH', 'DB2ADMIN', 
> 'code2win'
> 
> 
> "Neil Pike" wrote:
> 
> >  Nimesh - that's an IBM supplied driver by the looks of it - you'll need to ask 
> > in a DB2 forum.
> > 
> >  Is MS HIS involved at all?!?
> >  
> > > I am getting following error when I execute a select query against db2 
> > > linked server on sqlserver 2005.
> > > 
> > > Msg 7313, Level 16, State 1, Line 1
> > > An invalid schema or catalog was specified for the provider "IBMDADB2" for 
> > > linked server "IBMDB4".
> > > 
> > > Following is my select query 
> > > Select * FROM IBMDB4.VIENNA.NIMESH.INVENTORY_TBL
> > > 
> > > Following is my Linked server sp
> > > EXEC sp_addlinkedserver
> > > @server = 'IBMDB4',
> > > @srvproduct = 'IBM OLE DB Provider for DB2',
> > > @catalog = 'VIENNA',
> > > @datasrc= 'VIENNA',
> > > @provider = 'IBMDADB2',
> > > @provstr='NetLib=tcpip;RemoteLU=VIENNA;LocalLU=LOCAL;ModeName=QPCSUPP;User 
> > > ID=db2admin;Password=code2win;InitCat=VIENNA;Default 
> > > Schema=NIMESH;TPName=;Commit=YES;IsoLvl=NC;AccMode=;BinAsChar=NO'
> > > EXEC sp_addlinkedsrvlogin 'IBMDB4', false, 'ARES\NIMESH', 'DB2ADMIN', 
> > > 'code2win'
> > > 
> > > Please need your help
> > 
> > 
> >  Neil Pike.  Protech Computing Ltd
> >  Microsoft SNA/HIS MVP
> >  https://mvp.support.microsoft.com/profile=BE66F0D8-9D78-47EF-840A-08E6D8522A2D
> >  http://www.linkedin.com/in/neilpike
> > 
> > 
> >

date: Thu, 28 Aug 2008 10:46:05 -0700   author:   Asmita Wankhede Asmita