Start SQL Job from X++ Job in Axapta

Question

I want to trigger an SQL job which runs an SSIS package from an AX Job, I successfully ran SQL code fetching some records from an SQL table by creating a menu item for the job and have it run on the server instead of the client but the following code runs without errors but the Job's not started?

CODE:

    static void TriggerAllocation(Args _args)
{
    UserConnection userConnection;
    Statement statement;
str sqlStatement;
SqlSystem sqlSystem;
SqlStatementExecutePermission sqlPermission;
;

sqlSystem = new SqlSystem();

sqlStatement = "EXEC MSDB.dbo.sp_start_job @Job_Name = 'MyJob'";

userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlPermission = new SqlStatementExecutePermission(
sqlStatement);
sqlPermission.assert();

statement.executeQuery(sqlStatement);

CodeAccessPermission::revertAssert();

I can't find any more clues in e.g. eventviewer, SQL logs as for what went wrong..

Kind regards,

Mike

[UPDATE] Thanks to Alex K I solved it!

using

statement.executeUpdate(sqlStatement);

instead of

 statement.executeQuery(sqlStatement);

did the trick!

Keep in mind that running the job directly from AX won't work despite: server static void You'll have to create an Menu Item of type action with property RunOn=Server

Menu Item

Did you check windows event logs? Using basically the same code, I get `Request for the permission of type 'SqlStatementExecutePermission' failed.` I'm not sure if you can use a UserConnection like this...perhaps type Connection instead? — Alex Kwitny, Jan 17 '14 at 18:19
Actually try `server static void Trigger...` and perhaps `statement.executeUpdate(...)` instead of `executeQuery` — Alex Kwitny, Jan 17 '14 at 18:26
When I said use `static server` I thought your code would eventually end up in a class, not a job :P — Alex Kwitny, Jan 24 '14 at 17:27

score 2 · Accepted Answer · answered Jan 20 '14 at 21:36

2

I should have given my comment as an answer but wasn't paying attention:

Try server static void Trigger... and perhaps statement.executeUpdate(...) instead of executeQuery

answered Jan 20 '14 at 21:36

Alex Kwitny

11,211
2
49
71

Thnx Alex, executeUpdate was indeed the solution! At first I changed userconnection to connection like you said but after running it successfully I changed it back to userconnection and it still works. – Mike Dole Jan 24 '14 at 09:15

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

Calling EXEC from executeQuery is not supported.

See this question:

How to get the results of a direct SQL call to a stored procedure?

edited May 23 '17 at 11:53

Community

1
1

answered Jan 20 '14 at 09:51

Jan B. Kjeldsen

17,817
5
32
50

Start SQL Job from X++ Job in Axapta

2 Answers2