|
|
Posted by TechWitch on 01/19/07 00:11
I've seen a number of posts from frustrated folks about this topic,
with not much offered as a solution. I actually managed to get this to
work today, so I thought I'd post for the benefit of the community.
My team went through a number of puzzling errors in getting this work,
like the CLI0150E (Driver not capable) error, and a variety of DB2OLEDB
errors. None which were really indicative of the problem. Here's how
I got a read/write connection to function between SQL 2005 and a DB2
UDB database on AIX (v8.2)
1) First I configured a UDL data source as described in this Microsoft
article: http://support.microsoft.com/kb/218590/ This created the
resulting provider string:
Here is the resulting provider string:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=DB2OLEDB;User ID=<masked>;Password=<masked>;Initial
Catalog=<MYDBNAME>;Network Transport Library=TCP;Host CCSID=37;PC Code
Page=1252;Network Address=myserver.corp.something.org;Network
Port=50000;Package Collection=<TABSCHEMA>;Default
Schema=<TABSCHEMA>;Process Binary as Character=False;Units of
Work=RUW;Default Qualifier=<TABSCHEMA>;DBMS Platform=DB2/6000;Defer
Prepare=False;Persist Security Info=True;Connection Pooling=True;Derive
Parameters=False;
2) Then I created the linked server in SQL 2005:
/****** Object: LinkedServer [MYDB2LINKEDSERVER] Script Date:
01/18/2007 15:10:00 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'OLEDB2',
@srvproduct=N'Microsoft OLE DB Provider for DB2',
@provider=N'DB2OLEDB', @datasrc=N'<MYSERVER>',
@provstr=N'Provider=DB2OLEDB;Initial Catalog=<MYDBNAME>;Network
Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network
Address=<myserver>.corp.nai.org;Network Port=50000;Package
Collection=NULLID;Process Binary as Character=False;Units of
Work=RUW;DBMS Platform=DB2/6000;Defer Prepare=False;Persist Security
Info=False;Connection Pooling=True;Derive Parameters=False;',
@catalog=N'<MYDBNAME>'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'rpc out',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLEDB2', @optname=N'use
remote collation', @optvalue=N'true'
3) Then, I had to manually change this option in SQL 2005 (There used
to be a button for it in SQL 2000, so you have to do it manually now)
exec master.dbo.sp_MSset_oledb_prop 'DB2OLEDB','AllowInProcess',1
*** IMPORTANT STEP. IT WON'T WORK WITHOUT IT!!**
4) Then I ran my SQL:
select * from MYDB2LINKEDSERVER.MYDBNAME.TABSCHEMA.MY_TABLE
insert into MYDB2LINKEDSERVER.MYDBNAME.TABSCHEMA.MY_TABLE
select MY_SQL_COL from MY_SQL_TABLE
[Back to original message]
|