on 2017 Feb 09 6:12 AM
Hello,
When I am trying to call a stored procedure in SQL Anywhere 16 via dbisql with the following command:
dbisql -nogui -onerror exit -c dsn=SAAP_RCS_rem_0101;uid=saap;pwd=sql CALL sp_sync_reset_template()
Could not execute statement. Procedure 'sp_sync_reset_template' not found SQLCODE=-265, ODBC 3 State="42S02" Line 1, column 1 CALL sp_sync_reset_template()
When I am connecting to the database with the same connection string:
dbisql -nogui -onerror exit -c dsn=SAAP_RCS_rem_0101;uid=saap;pwd=sql
I can find the stored procedure in sysobject table: (SAAP)> select * from sysobjects where type = 'P' and name like 'sp_sync_reset%';
name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig seltrig ckfirst cache audflags objspare
sp_sync_reset_template 13061 1 P 0 0 0 0 0 (NULL) (NULL) 0 0 0 0 0 0 0 0
Why is dbisql showing procedure not found when the procedure is present in the database?
Note: this is the body of the procedure
create PROCEDURE sp_sync_reset_template()
BEGIN
CALL sp_sync_drop_subscription_to_pub();
CALL sp_sync_drop_sync_user();
DELETE FROM SYNC_USER;
SET OPTION PUBLIC.ml_remote_id=NULL;
END
One possibility is that the procedure is not visible because it is owned by a different user id (e.g., DBA) so the CALL should be qualified: CALL DBA.sp_sync_reset_template()
You might also need a GRANT EXECUTE on DBA.sp_sync_reset_template TO saap.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
So have you qualified the call with the owner name, as Breck has suggested?
Otherwise, it will still throw that error, unless DBA is a group/"user-extended role" and user "saap" is a member of that group/has been granted that role. See the docs for details on that basic concept.
Note: The "visibility" of a database object for a particular user (i.e. does he need to qualify the owner or not) is independent from the permissions he has to access/modify that object (such as granted by a GRANT EXECUTE statement).
As Volker points out (and my reply implied) you have TWO problems: name visibility and permission.
You fixed the permission problem.
The name visibility problem can be fixed by qualifying the name in CALL DBA.sp_sync_reset_template(), or bypassing that via group membership:
GRANT GROUP TO DBA;
GRANT MEMBERSHIP IN GROUP DBA TO saap;
Caution: Those are old-school GRANT statements, the "new improved" methods are much more difficult 🙂
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.