May 19, 2005 at 9:11 am
Hi,
I've tried creating a SQL Server linked database to an AS400 several times without much success. I have successfully used an odbc connection I created to this database using DTS packages, but a linked server would be more useful.
I tried simply using the odbc system datasource name:
sp_addlinkedserver 'myLinkedServername2', ' ', 'MSDASQL', 'mySystemDSN', NULL, NULL
When I try to use that linked server I get:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
Another one specifying the connection string:
sp_addlinkedserver 'myLinkedServername', ' ', 'MSDASQL', NULL, NULL, 'DRIVER={RUMBA AS/400 Optimized Server (32bit)};SYSTEM=HostName;DBQ=Partition;UID=myUID;PWD=myPWD";'
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [NetManage][RUMBA Data Access]No data source specified; dialog prohibited. SQLDriverConnect]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [NetManage][RUMBA Data Access]Driver not capable. SQLSetConnectOption]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Any hints on how to proceed?
Thanks in advance
Greg
May 20, 2005 at 10:20 am
I always had an issue connecting to an AS/400 too. The Rumba drivers dont interact with SQL Server for some reason very well. I think when I finally got it to work, I had to revert back a revision or two on the drivers. Luckily I had some local tech support to use. The NetManage web site is not real clear or helpfull. Wish I had more to offer.
May 23, 2005 at 10:36 am
Ok, that's interesting.
I'm using the RUMA AS/400 Optimized Server (32bit) driver version 2888.711.00.00
How does that compare with your version numbers?
Thanks!
Greg
May 31, 2005 at 5:02 pm
Hi,
Solved the problem with queries in the following syntax:
SELECT * FROM OPENROWSET ('MSDASQL','DSN=mySystemDSN','SELECT * FROM SCHEMA.TABLE')
Similarly I was able to create a linked server using the EM wizard and the following settings:
Otherdatasource: Microsoft OLD DB provider for ODBC Drivers
Product: whatever
Datasource: mySystemDSN
Provider string: DSN=mySystemDSN
Security tab: be made using this security context: my username/password combination.
Hope this is useful for someone else.
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply