Linked Server to DB2 from SQL 2005 IA64BIT computer

Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT?

We were hoping to use the new Microsoft OLEDB Provider for DB2...The problem is the driver looks like it will only work on x64 computers.
"The single setup program will install the provider and tools on both x86 and x64 computers"

We were able to install the driver on the IA64 computer and were able to successfully create a data source from the Data Access Tool. Oddly enough when we attempted to create a linked server in SSMS we received an error, we also noticed that the DB2OLEDB provider is not listed in SSMS.

This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.

thx.
art.

Did you ever figure this out? I am trying to configure a linked server using DB2 Connect as provider.

Linked Server to DB2 from SQL 2005 IA64BIT computer

Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT?

We were hoping to use the new Microsoft OLEDB Provider for DB2...The problem is the driver looks like it will only work on x64 computers.
"The single setup program will install the provider and tools on both x86 and x64 computers"

We were able to install the driver on the IA64 computer and were able to successfully create a data source from the Data Access Tool. Oddly enough when we attempted to create a linked server in SSMS we received an error, we also noticed that the DB2OLEDB provider is not listed in SSMS.

This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.

thx.
art.

Did you ever figure this out? I am trying to configure a linked server using DB2 Connect as provider.

Linked Server to DB2

I am trying to define a linked server to an As400 running DB2 and am having no luck. If anyone out there has done it successfully, please let me know how.

Thanks!

Did you ever figure this out? I'm having the same issue.|||

Please try

1) Installed Host Integration 2000 client license on the SQL Server 2000 server
2) Made sure that the TCP/IP DDM Server is running on the AS400
3) All minimum requirements on the server and the host are met for the
provider
4) Verified the connection/catalog/provider strings
5) Verified the user name and password used to connect to the host

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q218590

HTH

|||

I did get this to work. The script to define the linked server is below. Note that my AS400 is named BKL400. It is not the fastest thing in the world but it sure does come in handy in certain situations. If you still have problems, email me with contact information and we can talk.

/****** Object: LinkedServer [BKL400] Script Date: 06/05/2006 17:00:01 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'BKL400', @.srvproduct=N'Microsoft OLE DB Provider for DB2', @.provider=N'DB2OLEDB', @.datasrc=N'BKL400', @.provstr=N'Data Source=bkl400;User ID=xxxxxxxxx;password=xxxxxxxxx;Initial Catalog=bkl400;Provider=DB2OLEDB;Persist Security Info=True;Network Address=bkl400;Package Collection=QSYS2;DBMS Platform=DB2/AS400', @.catalog=N'BKL400'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'use remote collation', @.optvalue=N'true'

Linked server to DB2

Does anybody have good documentation on setting up a linked server to a
DB2/AS400 database? I've been unable to set one up in Enterprise Manager or
through Query Analyszer. Thanks"sp_addlinkedserver"
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
"Microsoft Host Integration Server 2000 Product Overview"
http://msdn.microsoft.com/library/d...
server2000.asp
"Host Integration Server 2000 Resource Kit Chapter 14 - Deploying Data
Access"
http://www.microsoft.com/resources/...
hisrkc14.mspx
Cristian Lefter, SQL Server MVP
"blue_nirvana" <bluenirvana@.discussions.microsoft.com> wrote in message
news:3A853550-643E-468F-B244-8010A5FDA916@.microsoft.com...
> Does anybody have good documentation on setting up a linked server to a
> DB2/AS400 database? I've been unable to set one up in Enterprise Manager
> or
> through Query Analyszer. Thanks

Linked Server to DB2

I am trying to define a linked server to an As400 running DB2 and am having no luck. If anyone out there has done it successfully, please let me know how.

Thanks!

Did you ever figure this out? I'm having the same issue.|||

Please try

1) Installed Host Integration 2000 client license on the SQL Server 2000 server
2) Made sure that the TCP/IP DDM Server is running on the AS400
3) All minimum requirements on the server and the host are met for the
provider
4) Verified the connection/catalog/provider strings
5) Verified the user name and password used to connect to the host

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q218590

HTH

|||

I did get this to work. The script to define the linked server is below. Note that my AS400 is named BKL400. It is not the fastest thing in the world but it sure does come in handy in certain situations. If you still have problems, email me with contact information and we can talk.

/****** Object: LinkedServer [BKL400] Script Date: 06/05/2006 17:00:01 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'BKL400', @.srvproduct=N'Microsoft OLE DB Provider for DB2', @.provider=N'DB2OLEDB', @.datasrc=N'BKL400', @.provstr=N'Data Source=bkl400;User ID=xxxxxxxxx;password=xxxxxxxxx;Initial Catalog=bkl400;Provider=DB2OLEDB;Persist Security Info=True;Network Address=bkl400;Package Collection=QSYS2;DBMS Platform=DB2/AS400', @.catalog=N'BKL400'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'BKL400', @.optname=N'use remote collation', @.optvalue=N'true'

Linked server to DB2

Hi all. I have tried everything I know (which isn't much) and I am
all out of ideas.
I am trying to create a linked server from SQL Server 2000 to a DB2
server. I am using IBM OLE DB Provider for DB2 and I get the
following error:
Error 7330: OLE DB Provider 'IBMDADB2' reported an error.
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80004005: ]
All my research has led me nowhere. I have another instance of SQL
Server 2000 on another machine and creating a linked server there
with the same provider worked fine. Where could I be going wrong?
Any help will be greatly appreciated. I will be more than happy to
provide more info if you need it.
Thanks in advance.Hi
I have never tried doing this but googling turned up this article
http://www.windowsitpro.com/SQLServ...7639/37639.html
which may/may not help you. As one system works you may want to see what is
different, e.g things to check such as MDAC versions, MDAC is installed
correctly, security/domains/trusts...
John
"svenpurple7" wrote:

> Hi all. I have tried everything I know (which isn't much) and I am
> all out of ideas.
> I am trying to create a linked server from SQL Server 2000 to a DB2
> server. I am using IBM OLE DB Provider for DB2 and I get the
> following error:
> Error 7330: OLE DB Provider 'IBMDADB2' reported an error.
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80004005: ]
> All my research has led me nowhere. I have another instance of SQL
> Server 2000 on another machine and creating a linked server there
> with the same provider worked fine. Where could I be going wrong?
> Any help will be greatly appreciated. I will be more than happy to
> provide more info if you need it.
> Thanks in advance.
>|||Thanks for the reply John.
The two instances of SQL Server are identical. The only difference is
in operating systems. The one that doesn't work is Windows 2003
server and the one that does work is Windows 2000 server.
I'll take a look at the linkyou sent.
Thanks again.|||Hi
Windows 2003 has more secure default settings therefore you may need to
enable something such as a service or port that is on by default in Windows
2000.
John
"svenpurple7" wrote:

> Thanks for the reply John.
> The two instances of SQL Server are identical. The only difference is
> in operating systems. The one that doesn't work is Windows 2003
> server and the one that does work is Windows 2000 server.
> I'll take a look at the linkyou sent.
> Thanks again.
>

Linked server to DB2

Does anybody have good documentation on setting up a linked server to a
DB2/AS400 database? I've been unable to set one up in Enterprise Manager or
through Query Analyszer. Thanks
"sp_addlinkedserver"
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
"Microsoft Host Integration Server 2000 Product Overview"
http://msdn.microsoft.com/library/de...server2000.asp
"Host Integration Server 2000 Resource Kit Chapter 14 - Deploying Data
Access"
http://www.microsoft.com/resources/d.../hisrkc14.mspx
Cristian Lefter, SQL Server MVP
"blue_nirvana" <bluenirvana@.discussions.microsoft.com> wrote in message
news:3A853550-643E-468F-B244-8010A5FDA916@.microsoft.com...
> Does anybody have good documentation on setting up a linked server to a
> DB2/AS400 database? I've been unable to set one up in Enterprise Manager
> or
> through Query Analyszer. Thanks

Linked Server to DB2

Hi,

I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.

I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.

Currently when i Insert the following error is encountered :

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSITIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.

When i try to select a CLOB column in DB2 the following error is encountered:

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABILITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".

Thanks in advance for any answer.

AshwinHi Ashwin, I am trying to setup a Linked Server to DB2 using the same driver. Can you please email me the screen shot of your settings or the script to create it?

Thanks,

Matt

Quote:

Originally Posted by ashwingawande

Hi,

I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.

I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.

Currently when i Insert the following error is encountered :

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSITIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.

When i try to select a CLOB column in DB2 the following error is encountered:

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABILITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".

Thanks in advance for any answer.

Ashwin

Linked server to DB2

Does anybody have good documentation on setting up a linked server to a
DB2/AS400 database? I've been unable to set one up in Enterprise Manager or
through Query Analyszer. Thanks"sp_addlinkedserver"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
"Microsoft Host Integration Server 2000 Product Overview"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnhis/html/his_hiserver2000.asp
"Host Integration Server 2000 Resource Kit Chapter 14 - Deploying Data
Access"
http://www.microsoft.com/resources/documentation/host/2000/all/reskit/en-us/part3/hisrkc14.mspx
Cristian Lefter, SQL Server MVP
"blue_nirvana" <bluenirvana@.discussions.microsoft.com> wrote in message
news:3A853550-643E-468F-B244-8010A5FDA916@.microsoft.com...
> Does anybody have good documentation on setting up a linked server to a
> DB2/AS400 database? I've been unable to set one up in Enterprise Manager
> or
> through Query Analyszer. Thanks