Execute a sql job from AS400 command line

Question

Execute a sql job from AS400 command line

vconlan

SSC-Addicted

Points: 497
More actions
February 26, 2013 at 8:58 am

#74467

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)

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

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

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

vconlan SSC-Addicted Points: 497 More actions · Answer 2

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.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

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

thsu SSC Veteran Points: 222 More actions · Answer 4

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.

vconlan SSC-Addicted Points: 497 More actions · Answer 5

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)

thsu SSC Veteran Points: 222 More actions · Answer 6

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

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

thsu SSC Veteran Points: 222 More actions · Answer 8

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

vconlan SSC-Addicted Points: 497 More actions · Answer 9

vconlan

SSC-Addicted

Points: 497

February 28, 2013 at 12:49 pm

#1592170

Thanks I will see how this works.

vconlan SSC-Addicted Points: 497 More actions · Answer 10

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.

thsu SSC Veteran Points: 222 More actions · Answer 11

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.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 12

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

vconlan SSC-Addicted Points: 497 More actions · Answer 13

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.