Linking SQL server 7 to AS400 using linked feature

[NYJunky]

Hello,

We are trying to link SQL server 7 (with sp3 on NT 4 machine) with
AS400 using client access. I created a DSN but i am getting this
error. I'll really appreciate if anyone can help me resolve this
issue.
Thanks
Fahd

Server: Msg 7399, Level 16, State 1
OLE DB provider `MSDASQL` reported an error. A provider-specific error
occurred.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager]
Driver`s
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. COMM RC=0x3]"

 

[bassguy]

Do you have to Link or can you import the data?

bassguy

 

[NYJunky]

Thanks for your response. yes i do have a link using client access and i am connected.
Thanks

 

[bassguy]

When you set up the DSN are you using your username and password or is there a application role set for your connection. AS400 are very tight about the security so you may have to talk to the security officer about getting an account that allows DSN or ODBC connections.

bassguy

 

[NYJunky]

I am keeping it blank but in SQL server security/Linked server(they will be mapped to), i am giving name and password to use if none is given.

 

[NYJunky]

I can use that exact DSN using microsoft access 97. so i must have the autherization.

 

[bassguy]

If the DSN is working with access 97 then it should work with the SQL server....else I am out of guesses

sorry


Bassguy

 

[NYJunky]

I think that the problem might be coneecting OLE DB to ODBC (DSN) but i have already installed SQL server SP1,2,3 and MDAC 2.5 and 2.6 ...is there any other way to change ole db provier for ODBC drivers?
Thanks for your continued interest

 

[bassguy]

Hi again....I am just guessing now...but have you completely filled out the linked server permissions Etc? I am sure there is something that is simple and overlooked...try the remote login and password...sorry

bassguy

 

[NYJunky]

Thanks again for your response. I am connected using the same id and password using client access. and on security tab on sQL server linked servers, i am clicking (they will be mapped to then the exact same user id and pwd)

 

[NYJunky]

The rest of the options on linked server are as follows:
Linked server = AS400
other data source provider name = Microsoft OLE db provider for ODBC drivers
Product name =Client Access
Data source = MyDSN name here
Provider string = no value (blank)
Location = no value (blank)
catalog = no value (blank)
only data access is checked in server options
SECURITY only they'll be mapped to is checked with ID and PWD ....

 

[bassguy]

try to import a table from the as400 using the DSN.

see what that does
Bassguy

 

[NYJunky]

Sorry to bother you so much but i am new to SQL server .. can you tell me how can i do that. transfer data using DSN ?
Thanks ..i really appreciate your help with this issue

 

[bassguy]

try this for a reference...they explain much better than me

http://msdn.microsoft.com/library/d.../html/achowImportAttachSqlDatabaseTablesS.asp

 

[NYJunky]

Thanks for the link ....works like a charm. I am able to improt a table from AS400.

 

[bassguy]

No problem at all

I have been there

bassguy

 

[NYJunky]

So you think ..the problem is with OLE DB driver then? to link servers ?

 

[bassguy]

I think that to correctly link with the as400 you have to have something better than the client access driver...there is another microsoft server that can act as a liason...I just cannot remember what it is called (i am sure it is a three letter acronym ...something like host integration server....)...it is another server that just works for connectivity...I have not used it because it was not an option for my work...you may want to look at migrating to one platform or the other with your application....apps using Heterogenus platforms are difficult as you are finding out.

 

[NYJunky]

Thanks alot I really appreciate your help.

 

[SpeedOLight]

You can use Client Access via TCP/IP as a Linked Server. Do a search in Tek-Tips for "Linked Server with AS/400", and it should show you all the necessary parameters for setting this up. Sorry I didn't see this earlier, or I could have helped you out.

Regards...Marc
Independent Software Consultant