linked server with the as/400

[swoodring]

I'm trying to set up a linked server to our as/400 with no luck. the script I uses creates the link server but every time I try to access it it just hangs. In other words when I try to access either through a simple query through query analyzer or when I try to access through enterprise manager under linked server and clikc on the tables icon for the linked server it just hangs. I end up having to kill the processes manually. I did notice under the process info it has either miscellaneous or pageiolatch_sh under the wait type. Not sure what this means or if this is the reason its not working. Here is the code I used to ceate the linked server. I've even tried when creating the linked server using data source parameter instead of the connections string. That did the same thing. I can connect using the DSN in a dts when I set up a connection. So the dsn is set up properly.

exec sp_addlinkedserver
@server = 'TEST4',
@provider = 'MSDASQL',
@srvproduct = 'Microsoft OLE Any DB Provider for ODBC',
@provstr = "ODBC;DSN = AS400; UID= HART; Password = whittman"

Any ideas would be appreiciated. I'd really like to get this set up as a linked server.

Stacy

 

[SpeedOLight]

A few differences in ODBC settings...

Try the following changes with the DSN used for the linked server:

Under packages
Enable Extended dynamic package support checked On

Under Performance
Enable lazy close support checked On
enable pre-fetch during execute checked On
Size: 1


Also, I'm client side oriented, not AS/400 oriented, so I can't tell you how to check what's installed on the AS/400. If you can't find someone there who knows how to do it (whoever installs PTFs should know), notify me and I'll dig up someone here who can do it.


Regards...Marc
Independent Software Consultant

 

[swoodring]

Actually I guess I mistyped when I said checked off I meant I put a checkmark in the box. So I changed the size to a 1 and that didn't help.

I checked the 400 and we have the following
5769XE1 *COMPATIBLE Client Access/400 Express for Windows
5769XW1 *INSTALLED Client Access/400 Windows Family Base
5769XY1 *INSTALLED Client Access Base Family
5763XD1 *INSTALLED Client Access/400 Optimized for Windows

One other thing I thought of don't know if this has anything to do with or not. When the consultants set up our sql server machine they have everything running off of computer namedomain instead of network domain. I haven't been sucessful in figuring out why they set it up this way and whats different with the other domain. Because of this we cannot use sql mail capabilites because we are signed on with a different domain. The notes say that if use the network domain jobs will hang which I tried out and they do. Basically looks like its running on the server but it never makes it to the 400. Don't know why but they do.

 

[SpeedOLight]

Ok...I finally ran across this problem myself while building a new SQL box, and was able to resolve it.

1. Make sure you have Client Access connection object(s) for you AS/400(s) on the SQL Server.

2. Make sure that the AS/400 signon information on the Connection tab in Ops Nav and, if appropriate, under the ODBC driver as well indicate "Use default user ID, prompt as needed" otherwise Client Access may use wrong or no logon credentials, in which case (the server will never prompt) it "just hangs." (This was the sticking point I had).

3. Finally, verify the security in the linked server setup. I prefer to have a small number of standard accounts (almost like local groups) for AS/400 authentication and manage security using SQL Server to limit access to the resources (stored procedures) that query the 400. This puts security in the middle tier, where it should be, and makes troubleshooting much simpler.

Hopefully, this will resolve it...

Regards...Marc
Independent Software Consultant

 

[swoodring]

Thanks for your reply. Plan on trying this out. In regard to #1 what exactly am I looking for. I believe I must have the connection objects since I can connect when I run DTS packages.

In regard to #2. where is the connection tab in the opearations navigator. I must be overlooking it. When I go in under under as/400 when I expand it I see basic operations, job management, system configuration, network, security, user and groups, file systems, and application development.

In regard to #3 I'm just mapping it to a specific profile . I have the they will be mapped to and then I enter an as/400 profile and password. Is this what you mean.

Stacy

 

[swoodring]

Just wanted to let the forum know that Marc's suggestion worked. Just in case anyone else runs across this. I changed the setting on the SQL server under my odbc setting. Under the general tab there was a connection properties button and on my setup it was set to use operations navigator default, I changed it to use window user name and password/no prompting and the the linked server worked like a charm. My as/400 connection and the sql server have the same password.

 

[Bazzer]

My solution to this issue:

1. Set up an ODBC DSN using Walldata's Rumba AS/400 Optimized Server (32bit) driver.

2. Define a linked server on the MSSQL using the OLE-DB provider for ODBC and only enter the name of the DSN defined in step 1 against 'Data Source'. Leaving 'Provider String', 'Location' and 'Catalog' all blank.

3. For security I used a generic account for everyone; but obviously this would have to be a local decision.

4. For query testing I used the following script:

SELECT COUNT(*)
FROM <AS/400 PLATFORM>.Rumba400.<library name>.<table name>

and this works on our installation.

nb. The hardest part actually was finding out that one needed to use the 'Rumba400' in the fully qualified name.

I hope this is useful.

Regards Bazza

 

[Muss]

Stacy,

How did you reference this linked server with SQL code. It looks like Bazza used <AS/400 PLATFORM>.Rumba400.<library name>.<table name>. What replaces the 'Rumba400' to reference your AS400 tables with Client Access?

Thanks,
Matt

 

[swoodring]

Here's a sample of what I used to test the link server.
select xipcls,classcode, classname
from openquery(RETAIL2, 'Select xipcls from itmxrefp')
join pbdss.dbo.class
on classcode = xipcls

 

[Bazzer]

Hi Mussa,

The command I used to view the name of the SQL package on the AS/400 is &quot;WRKRDBDIRE&quot; (Work with Reational Database Directory Entry) which gives on our system 'RUMBA400', as we use WALLDATA's Rumba package to access the AS/400's.

The string <AS/400 PLATFORM>.Rumba400.<library name>.<table name> is simply the fully qualified path to the table so for instance on our system one could put :-

DHFRAGVAZ.RUMBA400.ZOGHDA01.F0005

which is referencing a table in JDE.

Hope this helps

Bazza

 

[bigfoot]

THANK YOU, THANK YOU ALL SO MUCH!!!!!!!!

And yes, I am screaming!!!! It works!!!

I was missing the signon at the bottom of the screen!!!

WOWOWOWOWOWOWWOW!

(A little less hair, but it's there).

 

[SpeedOLight]

Glad it helped out...

Regards...Marc
Independent Software Consultant

 

[Mykarv]

Hi

I'm using SQLserver7.0 and AS400 with ClientAccessExpressV4R5M0.

have linked the server using Microsoft OLE DB Provider for ODBC Driver, with DataSource = ODBC-name, where ODBC = Client Access ExpressODBC (use default userID, prompt as needed)

When browsing linked server in EM, all tables are appearing.
When using the ODBC in Excel or other applications it works (but are promting for password)


Are hoping to use the AS400 as an Linked Server in SQL7.0 but are getting the error message:
Heading:&quot;SQL server Enterprise Manager&quot;
Message:&quot;ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid schema or catalog spesified for provider 'MSDASQL'&quot;

the error appears when trying to create this view:
SELECT *
FROM AS411.MVXBDTA001..MITMAS
(but note that when trying to save the view, in the Diagram Pane, all field-names are appearing!!!!)


when trying the syntax i first thought would be right (SELECT * FROM AS411.MITMAS) the error
Heading:&quot;SQL server Enterprise Manager&quot;
Message:&quot;ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'AS411.MITMAS'.&quot;


any help in slowing the increasing number of gray hairs are appriciated


regards
Mykarv