Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execute a sql job from AS400 command line Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 7, Visits: 10
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)
Post #1424127
Posted Wednesday, February 27, 2013 10:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 27, 2014 12:14 PM
Points: 3,433, Visits: 14,423
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
Post #1424649
Posted Wednesday, February 27, 2013 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 7, Visits: 10
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.
Post #1424656
Posted Wednesday, February 27, 2013 11:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 27, 2014 12:14 PM
Points: 3,433, Visits: 14,423
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
Post #1424665
Posted Thursday, February 28, 2013 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:51 PM
Points: 4, Visits: 37
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.
Post #1425162
Posted Thursday, February 28, 2013 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 7, Visits: 10
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)



Post #1425170
Posted Thursday, February 28, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:51 PM
Points: 4, Visits: 37
In fact, you can try to directly run the command on the server (your RUNRMTCMD destination) to see if it starts the SQL job.
Post #1425174
Posted Thursday, February 28, 2013 11:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 27, 2014 12:14 PM
Points: 3,433, Visits: 14,423
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
Post #1425213
Posted Thursday, February 28, 2013 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:51 PM
Points: 4, Visits: 37
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
Post #1425258
Posted Thursday, February 28, 2013 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 7:57 AM
Points: 7, Visits: 10
Thanks I will see how this works.
Post #1425261
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse