Executing xsjob in sap hana

[Former Member]

Hello Experts,

I'm trying to execute a background job using SQL procedure in sap hana studio. I have written a xsjob and also a SQL procedure. I have also saved it and activated it using  XS admin tool. The problem is when I  try to run the script in SAP HANA studio, It gets scheduled but doesn't display the output. In XS admin tool I'm getting error message stating " SQL exception 258: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2422". I have even written the scripts for .xsaccess,.xsprivileges and .xsapp(This is blank). Do I need to provide more permissions? Please help me to solve this issue. I'm herewith enclosing my SQL procedure and .XSJOB script.

demosql.xsjob

{ "description": "First SQL job",

"action": "Testdemo::jobsCreateEntry",

"schedules":

[ { "description": "This job will run every minute",

"xscron": "* * * * * * 59"

  }

   ]

}

jobsCreateEntry (SQL Procedure)

PROCEDURE "TEST1"."Testdemo::jobsCreateEntry" ()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA TEST1 AS

BEGIN

select * from TESTABLE;

END;

For your reference

The project name is : Testdemo

The schema used is : TEST1

The table in the schema is TESTABLE

Thanks

Madhav J

[thomas_jung · Accepted solution]

>The problem is when I  try to run the script in SAP HANA studio, It gets scheduled but doesn't display the output

I'm not sure what you mean by that. You ran the SQLScript procedure from the SQL Console?  If so what does that have to do with the scheduling of the job?

>In XS admin tool I'm getting error message stating

" SQL exception 258: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2422"

What user and password did you enter into the Job Details in XSAdmin?  This is what the job will be running under and it seems like this user isn't authorized to run this SQLScript procedure.

[Former Member · Accepted solution]

Hi Thomas,

Thanks for your reply. The user through which I logged in is 'User' and it has all the possible authorizations. After I Activate and save the Job in xs admin tool, I must get the required output.Right? But I'm not getting any output. Please help me out.

Thanks

Madhav J

[thomas_jung · Accepted solution]

>The user through which I logged in is 'User' and it has all the possible authorizations.

I don't understand what your login user has to do with this.  What user did you input into the job definition in the XSAdmin tool. This is the user which the job will run as.

>After I Activate and save the Job in xs admin tool, I must get the required output.Right?

Required output?  From the XSAdmin tool you will only see the job log - success or failure of the job. You said earlier you were getting an error message.

[Former Member · Accepted solution]

Hi Thomas,

Initially I was using incorrect username and password. But now I tried with user as SYSTEM and the associated password. It displayed the message SUCCESS in XS Admin tool. But the I'm not getting the required output that is  I should get displayed with the contents of the table TESTABLE..

Thanks

Madhav J

[thomas_jung · Accepted solution]

Are you expecting the output of the procedure to be placed into the Job Log? If so that's just not how it works.  Exceptions will be shown, but nothing else.

[Former Member · Accepted solution]

Hi Thomas,

I'm very much thankful to you. I got the expected output. I'm able to schedule a job in background successfully. Thanks for all your replies and help.

[patrickbachmann]

Madhav,

If you want your procedure to execute your SQL and then SAVE it off somewhere (ie: persist the results) you could create an empty table definition somewhere and then change your SQL to something like

INSERT INTO YourNewTable SELECT * FROM TESTTABLE

Then go look at that physical table for the results.  Of course you need to have INSERT access to the schema that you are trying to write to.  This could be a good test for you if you want to see that the procedure is actually working.  Although if you were to put breakpoints on your original procedure and stop just after your original select you could actually see the output results when debugging.  But scheduling the job of course you could not.  Besides this test you are doing I can see valid reasons for wanting to persist data.

-Patrick

[Former Member]

Hello Patrick,

Thanks for your reply. I will sure follow your suggestion.