Starting / execute CL program on AS400 / iSeries

• jje_de

  Old Hand

  Points: 395

  More actions

  August 30, 2011 at 3:19 am

  #243913

  Hello,

  I've a syntax problem with our AS400 and our SQL Server 2005.

  We have a connection via Linked Server, which is used for selects and Updates.

  For example: "[font="Courier New"]UPDATE AS400.LINKSTRING.LIBNAME.TABLENAME SET FIELDNAME = '222' WHERE FIELDNAME = '111'[/font]" out of a SQL query runs fine.

  Now I want to execute a CL program on the iSeries. We do this since today with Microsoft Access in a VBA script.

  Is there any way to execute the CL program in a stored procedure???

  I have no idea how to do this

  (... like [font="Courier New"]execute AS400.LINKSTRING.LIBNAME.PROGNAME [/font]... ???)

  It would be very nice, if someone can help me...

  Thanks

  Joerg

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  August 30, 2011 at 6:07 am

  #1374908

  I guess you'd better do that on the AS/400 side: wrap the program call into a stored procedure and then call it from SQL Server.

  I've always done it this way and works well.

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• jje_de

  Old Hand

  Points: 395

  More actions

  August 30, 2011 at 6:27 am

  #1374921

  Gianluca Sartori (8/30/2011)

  ---

  ..... into a stored procedure and then call it from SQL Server.

  .....Gianluca

  Hi Gianluca,

  thanks for your reply!

  Ok, if this is a way, I try it. COuld you say me "how to start a stored procedure on the AS/400 side out of the SQL Server"?

  We do some changes in data in the SQL Server, then insert the modified records in AS/400 tables. Then we must start the programm on the AS/400.

  In the past we do this with triggers in tables (SQL Server change a value in an AS/400 table and the AS/400 program looks every minute into this table if there is a changing and starts then another prog - but this is not a very good way...)

  Bye

  Joerg

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  August 30, 2011 at 6:35 am

  #1374929

  jje_de (8/30/2011)

  ---

  Gianluca Sartori (8/30/2011)

  ---

  ..... into a stored procedure and then call it from SQL Server.

  .....Gianluca

  Hi Gianluca,

  thanks for your reply!

  Ok, if this is a way, I try it. COuld you say me "how to start a stored procedure on the AS/400 side out of the SQL Server"?

  EXEC AS400.LINKSTRING.LIBNAME.PROCEDURENAME

  This should be enough.

  We do some changes in data in the SQL Server, then insert the modified records in AS/400 tables. Then we must start the programm on the AS/400.

  In the past we do this with triggers in tables (SQL Server change a value in an AS/400 table and the AS/400 program looks every minute into this table if there is a changing and starts then another prog - but this is not a very good way...)

  Bye

  Joerg

  If it polls a table every one minutes it's not a trigger, but a totally different thing.

  Indeed, a trigger could really be a way to implement it.

  -- Gianluca Sartori

• jje_de

  Old Hand

  Points: 395

  More actions

  August 30, 2011 at 6:39 am

  #1374931

  Yes you are right, that's really no trigger we call this so 😉 ...

  Ok, I try that way and give feedback the next days - thank you very much

  Joerg