AS400 and linked servers

[ettore]

Maybe this is not the right newsqroup.

I developed an intranet application based on SQL Server 2000 and a Access 97
Database. Access DB is 'linked' to Sql Server through sp_addlinkedserver
procedure.

Now I must substitute Access DB with an AS400 (IBM) database. I'm not able to
link this server.

I created a system DSN (let's call it xDSN) and when I the following command in
Query Analyser:

exec sp_addlinkedserver 'as400','','MSDASQL','xDSN'

I got no errors.
Nevertheless when I try to read an AS400 table I get no results.

If I check 'linked servers' in Enterprise manager I see As400 but all the same
locks when I try to see the tables list.

Notice that xDSN is correct since when I try to read AS400 from an Access mdb I
have no problems.

Does anybody know the solution ?

Thanks

Ettore

[Dingo]

Try this:

-- Creates a linked server to DB2 on AS/400
EXEC sp_addlinkedserver
@server = 'linkedservername',
@srvproduct = 'DB2',
@provider = 'MSDASQL',
@provstr = 'DSN=xDSN;SERVER=linkedservername;UID=username;PWD=password'
GO

-- Maps a linked server login
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'linkedservername',
@useself = 'false',
@locallogin = NULL, --This maps all local users to the
specified linked server login
@rmtuser = 'username',
@rmtpassword = 'password'
GO

SELECT * FROM linkedservername.AS/400name.libraryname.tablename
GO

Hope this helps.

JG

"ettore" <ettore...@tin.it> wrote in message
news:1c5a01c092c8$a0cfc7a0$9ae62ecf@tkmsftngxa02...

[ettore]

Thanks for suggestion.

It worked ! Now I see the list of tables and views in "Enterprise manager", but
when I try a simple query like "select * from ..." I get only the first row and
this message:

Server: Msg 7399,Level 16, State 1,Line 1
OLE DB Provider 'MSDASQL' reported an error.
[OLE DB provider returned message: [IBM][Programma di controllo ODBC Client
Access (32 bit)] Il programma di controllo non può eseguire la funzione
richiesta.]

which translated should sound
ODBC control program Client Access cannot run requested function.

Do you know what does this mean?

Thanks again

Ettore
-----Original Message-----
Try this:

Hope this helps.

JG

Thanks

Ettore

.