Execute a sql job from AS400 command line

  • I am trying to use this command to execute a sql job from an AS400 command line. I am having trouble getting it to work. It doesn't say that the job started. It just says 'The remote command has produced no output'.

    I am running this:

    RUNRMTCMD CMD('sqlcmd -U user -P password -S server name -Q "msdb.dbo.sp _start_job @job_name = ''FACT852: ProcessRetailSales'', @server_name = '' server''"') RMTLOCNAME('99.99.99.99' *IP) RMTUSER(user) RMTPWD(password)

  • vconlan (2/26/2013)


    I am trying to use this command to execute a sql job from an AS400 command line. I am having trouble getting it to work. It doesn't say that the job started. It just says 'The remote command has produced no output'.

    I am running this:

    RUNRMTCMD CMD('sqlcmd -U user -P password -S server name -Q "msdb.dbo.sp _start_job @job_name = ''FACT852: ProcessRetailSales'', @server_name = '' server''"') RMTLOCNAME('99.99.99.99' *IP) RMTUSER(user) RMTPWD(password)

    I have not used it for a while but you can use stored procedure to execute the job but admin permission is required for both SQL Server and AS400.

    Kind regards,
    Gift Peddie

  • I have and EDI process on my AS400 and at the end of that process I need to call a sql job. However we can't get it to work.

  • vconlan (2/27/2013)


    I have and EDI process on my AS400 and at the end of that process I need to call a sql job. However we can't get it to work.

    Then you can create SSIS package that can run both or create a job steps that runs both.

    Kind regards,
    Gift Peddie

  • You can add >> c:\temp.txt to redirect the command output to check if the job started successfully.

    Also make sure the user is a member of SQLAgent*Role in msdb.

  • I'm sorry I am confused.

    I need to initiate this on the aS400. I am trying to use this command. Does this command not work?

    RUNRMTCMD CMD('sqlcmd -U user -P password -S server name -Q "msdb.dbo.sp _start_job @job_name = ''FACT852: ProcessRetailSales'', @server_name = '' server''"') RMTLOCNAME('99.99.99.99' *IP) RMTUSER(user) RMTPWD(password)

  • In fact, you can try to directly run the command on the server (your RUNRMTCMD destination) to see if it starts the SQL job.

  • thsu (2/28/2013)


    You can add >> c:\temp.txt to redirect the command output to check if the job started successfully.

    Also make sure the user is a member of SQLAgent*Role in msdb.

    That will not work for IBM AS400 because since 2005 admin permissions in both IBM and Microsoft is required for most automation solutions to work.

    Kind regards,
    Gift Peddie

  • Here is my example:

    RUNRMTCMD CMD('sqlcmd.exe -S "MY-SQL-SERVER" -Q "execute msd

    b.dbo.sp_start_job @job_name = ''TestOnly'' "

    >> C:\temp\temp.txt')

    RMTLOCNAME('MYPC.THSU.COM' *IP)

    RMTUSER('thsu')

    RMTPWD('mypassword')

    Results in temp.txt:

    Job 'TestOnly' started successfully.

    TestOnly job history showed "The job succeed"

    Hope this helps.

    BTW, I use Windows Authentication in SQL server.

    -Tom

  • Thanks I will see how this works.

  • When we run the command we get the .txt on the SQL server but it is empty.

    If we try to run RMTCMD to just do a regular copy file from one folder to the other it is working. However as soon as we do SQLCMD. It doesn't work. We tried doing a sql command just to run a select * from table and this doesn't work. Is there something that needs to be installed on the AS400 or some rights that I am not aware of.

  • I don't remember exactly what I did with that project years ago.

    But I am sure that no need to install anything extra on AS400.

    If you directly run SQLCMD from Windows command prompt, it should give you error message to diagnose.

  • thsu (3/4/2013)


    I don't remember exactly what I did with that project years ago.

    But I am sure that no need to install anything extra on AS400.

    If you directly run SQLCMD from Windows command prompt, it should give you error message to diagnose.

    It would be nice if you remember the year because an Agent Job after an EDI between SQL Server and AS400 can be payroll transaction so running it with the simple test example may not be relevant to the actual task needed.

    Kind regards,
    Gift Peddie

  • Thanks. We got it to work. We used the RMTCMD command to call a .bat. The main issue is that we had to use OSQL instead of sqlcmd which is weird because we are on SQL 2005.

    This is the .bat

    osql -U sqluser -P sqlpassword -S server -Q "EXEC msdb.dbo.sp_start_job N'JOB NAME'" .

    Also we has to change the user and password for the RMTCMD because it is different than the sql user and password.

    Thanks for your help.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply