Has anyone used SQL Server to make a real time request or update to an AS/400?

Right now my organization communicates with an AS/400 through a scheduled job of a DTS package on SQL Server 2000, to push and pull data. We'd like to do this real time.

Can you be more descriptive of your task?

Jaime E. Maccou

Id like to read from or update an AS/400 file (or table, or whatever their called) from an SQL Server stored procedure in real time. Or write a web service to do it.

From SQL Server, is a Linked Server my only option. If so, what are the linked server properties?

From a web service, what would be the connection string?

Setting up AS400 linked server:

If you plan to update the AS400 Journaling must be turned on

Create a System ODBC connection, use an AS400 login whose password doe not expire preferably. Using the Client Access ODBC driver.

in enterprise manager under security/linked servers

Right click and ADD

Specify a name for your AS400, I call mine simply AS400

select MS OLEDB for ODBC driver

IN data source specify the System Datasource set up previously

click the provider option button, check all but level zero

click the security tab and put in the AS400 user and password you want to use

under server option check all but collation compatible.

I'm not quite sure what all that does but it took me 3 months to figure it out.

David

Thanks David. Using your instructions I was able to create the Linked Server. I expand the tree I created and see tables and views. But if I try to write a sql statement (like, select * from as400_s...DRW), the message returns:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

Any suggestions?

I tried this out and could only get it working by:
- in provider option button, unchecking 'Index as Access Path'
- in SQL using SELECT * FROM <<LinkedServer>>.<<AS400Name>>.<<AS400Library>>.<< AS400File>>

hth
Phil

that is useing 4 part naming, but is unefficient from what I have read.

here is a sample of how to use OPENQUERY

select * from openquery(as400,'
select * from library.tablename
where
charfield = ''some char value'' and
numfield = 2
')

the AS400 select statement must be enclosed in single quotes, any char values must be within double single quotes, numeric values no quotes.

the AS400 after the OPENQUERY is the name of your linked server, mine is simply named AS400

I usually format my openquery statements as above, makes it simple to understand (for me)

David

OIC, you use OPENQUERY to actually get the data. That's v useful, thanks, and it does seem to be quicker too

IF I create code in .NET with this parameters I make a success full connection to AS400:

conexion.ConnectionString = " DataSource=10.254.xxx.xxx;DataBase=S1018689;UserId =yyy;Password=zzz";

mySqlString.Append("SELECT * FROM LIBRARY.TABLE");
conexion.Open();

iDB2Command command = new iDB2Command(mySqlString.ToString());

command.CommandTimeout = 0;
command.Connection = conexion;

iDB2DataAdapter SqlDa = new iDB2DataAdapter(command);

DataSet DS = new DataSet();
SqlDa.Fill(DS);

The question is: when I run de create linked server wizard it ask me for some info, and I don’t know what to put where.

@srvproduct = ?
@datasrc = ?
@provstr = ?
@catalog = ?