SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe
Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
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_PASSWO
RD
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/...&displaylang=en
), this includes Microsoft OLEDB Provider for DB2 but it can only be used o
n
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 simpl
e
$$$$.
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-bi
t
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 u
p
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 fo
r
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 o
f
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/40
0.
>
> 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_PASSWO
RD
> 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/...&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 t
o
> ask why I don't buy Enterprice Edition if it's included, the reason is sim
ple
> $$$$.
> 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:[vbcol=seagreen]
> 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:
>sql
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_PASSWO
RD
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/...&displaylang=en
), this includes Microsoft OLEDB Provider for DB2 but it can only be used o
n
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 simpl
e
$$$$.
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-bi
t
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 u
p
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 fo
r
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 o
f
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/40
0.
>
> 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_PASSWO
RD
> 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/...&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 t
o
> ask why I don't buy Enterprice Edition if it's included, the reason is sim
ple
> $$$$.
> 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:[vbcol=seagreen]
> 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:
>sql
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
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
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/d...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/d...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:[vbcol=seagreen]
> 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:
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/d...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/d...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:[vbcol=seagreen]
> 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:
Monday, March 12, 2012
Linked Server connection issue
We have a SQL Server 2005 Enterprise Edition set up on Server 2003 Standard Edtion OS and one on Server 2003 OS Enterprise Edition. All of them have their Service Pack ones installed. On the 2005 on Standard 2003 OS I can get to linked ODBC connection servers with no problem. On the 2005 SQL Server on the Enterprise Edition I get
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "EAWARMBKUP".
We are not sure whether or not this is caused by something on the OS or on the SQL SERVER software. Any help would be appreciated.
Allow remote connections is on and so is the SQL Browser Service.
I presume it is 32 bit server. There is no 64-bit MSDASQL.
Can you try using the same parameters which you have for the linked server and try connecting to the server from outside of SQL Server. You can use a new UDL file, or a simple VB script for that. Would it work?
|||if your linked server EAWARMBKUP is a sql2000 (sp3/sp3a or sp4) you should run instcat.sql on that sql2000 instance.
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
also check out http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=381384&SiteID=1 where is stated that in a 64-bit env, "
You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.
"
|||It is a 32 bit system trying to get to a Sybase database. I can get to it from another SQL Server 2005 database with no problem but not from the one we just setup. The one I can get to it from is on a Server 2003 standard edition OS and the new one is on a Server 2003 Enterprise Edition. We have to use Enterprise Edition because we are going to cluster it in Production.|||did you enable oledbconnections in your sql2005 surface area config ?|||I'm not sure how to do that so probably not.|||Start / programs / Microsoft SQL Server 2005 / configuration tools / SQL Server Surface Area Configuration
Change computer (to your sql2005 where you have the problems)
Surface Area Configuration for Features
Select oledb automation and enable it
|||It is enabled but I get the same message do I need to restart anything?|||Can you successfully ping the SQL Server machine from the machine where you are executing the linked server query?
Also you may want to review some of the ideas suggested at http://www.aspfaq.com/show.asp?id=2544 to trouble shoot linked server errors.
Thanks
Suroor
Subscribe to:
Posts (Atom)