Linked server in sql 2005, Oracle and DB2

We are moving from a SQL 2000 32-bit Standard Edition to a SQL 2005 64-bit
standard Edition and are using linked server to Oracle 10 and DB2 on AS/400.
Current system:
Windows 2000 32-bit
SQL 2000 sp3
Linked Server 1, DB2 linked server:
Driver: Microsoft OLE DB Provider for ODBC Driver
Product name: MSDASQL
Data source:
Provider string: dsn=asw;UID:A_USER_NAME;PWD:A_PASSWORD
Location: EVER01
Catalog: EVER01
Local login: aLocalUser
Remote user: aRemoteUser
Remote password: aRemotePassword
Linked server 2, Orcale linked server:
Driver: Microsoft OLE DB Provider for ODBC Driver
Product name: MSDASQL
Data source:
Provider string: dsn=wmansto;UID:A_USER_NAME;PWD:A_PASSWORD
Location: EVER01
Catalog: EVER01
Local login: aLocalUser
Remote user: aRemoteUser
Remote password: aRemotePassword
New system:
Windows 2003 Server Standard Edition
SQL 2005 64-bit
Linked server 1 and 2 ( preferd the same name as in the old system so I
don't have to change to many queries). But I can't find the Microsoft OLE DB
Provider for ODBC Driver in the list of drivers
Now we are going to use a server is a AMD 64-bit with Windows 2003 64-bit
Standard Edition and SQL 2005 64-bit Standard Edition.
I know that Microsoft released a feature pack for SQL 2005 (
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
), this includes Microsoft OLEDB Provider for DB2 but it can only be used on
SQL 2005 Developer Edition or SQL 2005 Enterprice. I think you are going to
ask why I don't buy Enterprice Edition if it's included, the reason is simple
$$$$.
What I want to do is too setup two linked servers ( mention above) and use
the as I do on the old system. I find some drivers but most of them is 32-bit
and they will not show up in the dialog Add new Linked server (in SQL
server). I would be nice to have the drivers in 64-bit but if I can set it up
with 32-bit drivers it would be fine.
Regarding the DB2 connection I havn't found any thing thats help, I have
read somthing that I should use Host Integration Client tools to build the
connection but I havn't come around to test that yet.
Regarding the Oracle linked server I have tried the following ( TNS name: is
wman_sto:
EXEC master.dbo.sp_addlinkedserver @.server = N'WMANSTO',
@.srvproduct=N'OraOLEDB.Oracle', @.provider=N'OraOLEDB.Oracle',
@.datasrc=N'wman_sto'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'data access',
@.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'use remote
collation', @.optvalue=N'true'
--
Bjorn VI am have not been able to create a successful linked server in SQL 64bit for
Oracle either, but I do have DB2 on AS/400 running:
1. Install iSeries Access for Windows on your SQL Server
2. Create linked server using "IBM DB2 UDB for iSeries IBMDA400 OLE DB
Provider" Product Name and Data Source should both be the DNS Server name of
the AS400 you want to connect to.
It works great.
If you find an answer to the Oracle problem please post it on this thread.
I would appreciate any help you find.
...JS
"Bjorn" wrote:
> We are moving from a SQL 2000 32-bit Standard Edition to a SQL 2005 64-bit
> standard Edition and are using linked server to Oracle 10 and DB2 on AS/400.
>
> Current system:
> Windows 2000 32-bit
> SQL 2000 sp3
> Linked Server 1, DB2 linked server:
> Driver: Microsoft OLE DB Provider for ODBC Driver
> Product name: MSDASQL
> Data source:
> Provider string: dsn=asw;UID:A_USER_NAME;PWD:A_PASSWORD
> Location: EVER01
> Catalog: EVER01
> Local login: aLocalUser
> Remote user: aRemoteUser
> Remote password: aRemotePassword
> Linked server 2, Orcale linked server:
> Driver: Microsoft OLE DB Provider for ODBC Driver
> Product name: MSDASQL
> Data source:
> Provider string: dsn=wmansto;UID:A_USER_NAME;PWD:A_PASSWORD
> Location: EVER01
> Catalog: EVER01
> Local login: aLocalUser
> Remote user: aRemoteUser
> Remote password: aRemotePassword
> New system:
> Windows 2003 Server Standard Edition
> SQL 2005 64-bit
> Linked server 1 and 2 ( preferd the same name as in the old system so I
> don't have to change to many queries). But I can't find the Microsoft OLE DB
> Provider for ODBC Driver in the list of drivers
>
>
> Now we are going to use a server is a AMD 64-bit with Windows 2003 64-bit
> Standard Edition and SQL 2005 64-bit Standard Edition.
> I know that Microsoft released a feature pack for SQL 2005 (
> http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
> ), this includes Microsoft OLEDB Provider for DB2 but it can only be used on
> SQL 2005 Developer Edition or SQL 2005 Enterprice. I think you are going to
> ask why I don't buy Enterprice Edition if it's included, the reason is simple
> $$$$.
> What I want to do is too setup two linked servers ( mention above) and use
> the as I do on the old system. I find some drivers but most of them is 32-bit
> and they will not show up in the dialog Add new Linked server (in SQL
> server). I would be nice to have the drivers in 64-bit but if I can set it up
> with 32-bit drivers it would be fine.
> Regarding the DB2 connection I havn't found any thing thats help, I have
> read somthing that I should use Host Integration Client tools to build the
> connection but I havn't come around to test that yet.
>
> Regarding the Oracle linked server I have tried the following ( TNS name: is
> wman_sto:
> EXEC master.dbo.sp_addlinkedserver @.server = N'WMANSTO',
> @.srvproduct=N'OraOLEDB.Oracle', @.provider=N'OraOLEDB.Oracle',
> @.datasrc=N'wman_sto'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'data access',
> @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'dist',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'pub',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'sub',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'lazy schema
> validation', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'use remote
> collation', @.optvalue=N'true'
>
>
> --
> Bjorn V|||Hi,
Followed your sugestion on creating a linked server for db2 on sql
server 2005(64 bit).
When i run the query it errors out with "cannot initialize oledb for
"IBMDA400"
Wonder if it has anything to do with the connection strin i am using.
Any help is appreciated.
Thnx in advance.
JESC wrote:
> I am have not been able to create a successful linked server in SQL 64bit for
> Oracle either, but I do have DB2 on AS/400 running:
> 1. Install iSeries Access for Windows on your SQL Server
> 2. Create linked server using "IBM DB2 UDB for iSeries IBMDA400 OLE DB
> Provider" Product Name and Data Source should both be the DNS Server name of
> the AS400 you want to connect to.
> It works great.
> If you find an answer to the Oracle problem please post it on this thread.
> I would appreciate any help you find.
> ...JS
> "Bjorn" wrote:
> > We are moving from a SQL 2000 32-bit Standard Edition to a SQL 2005 64-bit
> > standard Edition and are using linked server to Oracle 10 and DB2 on AS/400.
> >
> >
> > Current system:
> > Windows 2000 32-bit
> > SQL 2000 sp3
> >
> > Linked Server 1, DB2 linked server:
> > Driver: Microsoft OLE DB Provider for ODBC Driver
> > Product name: MSDASQL
> > Data source:
> > Provider string: dsn=asw;UID:A_USER_NAME;PWD:A_PASSWORD
> > Location: EVER01
> > Catalog: EVER01
> >
> > Local login: aLocalUser
> > Remote user: aRemoteUser
> > Remote password: aRemotePassword
> >
> > Linked server 2, Orcale linked server:
> > Driver: Microsoft OLE DB Provider for ODBC Driver
> > Product name: MSDASQL
> > Data source:
> > Provider string: dsn=wmansto;UID:A_USER_NAME;PWD:A_PASSWORD
> > Location: EVER01
> > Catalog: EVER01
> >
> > Local login: aLocalUser
> > Remote user: aRemoteUser
> > Remote password: aRemotePassword
> >
> > New system:
> > Windows 2003 Server Standard Edition
> > SQL 2005 64-bit
> >
> > Linked server 1 and 2 ( preferd the same name as in the old system so I
> > don't have to change to many queries). But I can't find the Microsoft OLE DB
> > Provider for ODBC Driver in the list of drivers
> >
> >
> >
> >
> > Now we are going to use a server is a AMD 64-bit with Windows 2003 64-bit
> > Standard Edition and SQL 2005 64-bit Standard Edition.
> >
> > I know that Microsoft released a feature pack for SQL 2005 (
> > http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
> > ), this includes Microsoft OLEDB Provider for DB2 but it can only be used on
> > SQL 2005 Developer Edition or SQL 2005 Enterprice. I think you are going to
> > ask why I don't buy Enterprice Edition if it's included, the reason is simple
> > $$$$.
> >
> > What I want to do is too setup two linked servers ( mention above) and use
> > the as I do on the old system. I find some drivers but most of them is 32-bit
> > and they will not show up in the dialog Add new Linked server (in SQL
> > server). I would be nice to have the drivers in 64-bit but if I can set it up
> > with 32-bit drivers it would be fine.
> >
> > Regarding the DB2 connection I havn't found any thing thats help, I have
> > read somthing that I should use Host Integration Client tools to build the
> > connection but I havn't come around to test that yet.
> >
> >
> > Regarding the Oracle linked server I have tried the following ( TNS name: is
> > wman_sto:
> >
> > EXEC master.dbo.sp_addlinkedserver @.server = N'WMANSTO',
> > @.srvproduct=N'OraOLEDB.Oracle', @.provider=N'OraOLEDB.Oracle',
> > @.datasrc=N'wman_sto'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
> > compatible', @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'data access',
> > @.optvalue=N'true'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'dist',
> > @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'pub',
> > @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc',
> > @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'rpc out',
> > @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'sub',
> > @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'connect
> > timeout', @.optvalue=N'0'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'collation
> > name', @.optvalue=null
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'lazy schema
> > validation', @.optvalue=N'false'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'query
> > timeout', @.optvalue=N'0'
> > GO
> > EXEC master.dbo.sp_serveroption @.server=N'WMANSTO', @.optname=N'use remote
> > collation', @.optvalue=N'true'
> >
> >
> >
> >
> >
> > --
> >
> > Bjorn V