SAP SQL Anywhere

Question

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

Answer 1

1

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.

permanent link

answered 09 Feb '17, 07:02

Breck Carter
32.5k●539●724●1050
accept rate: 20%

Comment Text Removed

Noticed that the uid is showing as 1 in the resultset from sysobject. Uid 1 is DBA.

Even after granting execute permission to saap, it is still throwing the same error

(09 Feb '17, 08:03) rohinibasuu

Replies hidden

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).

(09 Feb '17, 09:01) Volker Barth

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 :)

(09 Feb '17, 12:58) Breck Carter

flat view

SAP SQL Anywhere

dbisql showing 'procedure not found' whereas sysobject is having that object

Follow this question

Related questions