"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

> 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