on 01-06-2015 1:17 PM
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
>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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
>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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.