September 18, 2003 at 9:37 am
I'm trying to set up a linked server in SQL Server 7 to a DB2 database.
The process I've followed is described below.
In Enterprise Manager:
New Linked Server
In General Tab…
Linked Server: TEST
Provider Name: Microsoft OLE DB Provider for ODBC Drivers
Product Name: TEST
Data Source: DDW0001P
Provider String: left blank
Location: left blank
Catalog: left blank
In Security Tab…
Local Login: my WinNT login name from dropdown list
Remote User: my login name for DB2
Remote Password: my password for DB2
For login not defined above: No connection be made
In Server Options Tab…
Data Access is the only option selected
I'm getting the following error:
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
I've also tried running the following stored proc…
EXEC sp_addlinkedserver
@server = 'TEST',
@srvproduct = 'TEST',
@provider = 'MSDASQL',
@datasrc = 'DDW0001P'
I get the same error message as above.
Could anyone give some direction on how to successfully accomplish this task?
Thanks!!!
September 18, 2003 at 9:53 am
Is Client Access installed on teh SQL Server?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 18, 2003 at 10:04 am
I'm not sure. I was not responsible for the install/configuration of SQL Server. How do I find out?
September 19, 2003 at 12:41 am
Hi,
I also had some problems to set-up an AS/400 as a linked server with SQL server 2k.
But finally we were successful with the Client Access ODBC driver for iSeries (latest Client Access release) and an AS/400 user that has All Object rights.
Matthias
September 19, 2003 at 3:08 am
You almost certainly need the DB2 ODBC driver. These are free in the DB2 install media. You will get better performance if you use a product called 'DB2 Connect'.
ODBC to DB2 always transfers data 1 row per packet. DB2 Connect uses DRDA, which supports set-based transfer, similar to the way SQL Server works. Unfortunately DB2 Connect is not free.
If you only want to do low-volume queries (maybe 50 rows max) then ODBC should be fine and give adequate performance. If you need to get a large number of rows, then ODBC will be slow.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 19, 2003 at 3:27 am
Yes I totally agree with Matthias..U need to install Client Access ODBC Driver(s) on ur server with SQL Server.
Cheers!
Arvind
Arvind
Arvind
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply