We have an older iSeries running V4R4. We user Rumba/400 for AS400 access, and we've run into a problem using the Rumba ODBC driver for linked server access from our SQL server to our AS400 (The ODBC driver periodically fails when the linked server is used heavily). Because of this, we've been doing everything we can to avoid using the linked server, and instead use OPENROWSET() wherever possible. This works well as long as we don't need to do an update, insert, delete, or execute a program on the as400. When we need to do any of those, we seem to be stuck using the linked server on our SQL server.

I have set up several stored procedures to run RPG or CL programs, and they all work well for what we need (they're in DTS packages).

We're currently trying to replace an update using the linked server with something more stable. The first thing we tried was to use an update openrowset(), but, it seems we are limited to 1 row at a time which can be rather ugly. After that, I tried putting the SQL into a CL program, and creating a stored procedure to call the external program (SQL based stored procedures require ILE C/400 to compile). This works fine except that no result set is returned, so again, we can't use OPENROWSET since it expects a resultset.

My first thought was that since it would be good to know the number of rows updated anyway, simply adding a result set of the returned rows would be the easiest solution. but, I can't seem to find any documentation online as to how to return a result set in RPG or CL.

Granted, my experience in RPG, and CL is very limited, so, I could easily be overlooking something very obvious.

Any ideas? (purchasing ILE C/400 isn't an option at this time unfortunately)

thanks in advance,
Kevin