cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos
3,322

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
Former Member
0 Kudos

Let me start off by saying ... I don't know the answer!

Is it possible you have a trailing unprintable character in that name? Like an ASCII space or NBSP? If it is related to something like that you won't see a result set from this query select * from sysobjects where type = 'P' and name='sp_sync_reset_template';

but it should show up with a query like this one select cast(name as binary(128)), name from sysobjects where type = 'P' and name like '%sp_sync_reset_template%';

Accepted Solutions (0)

Answers (1)

Answers (1)

Breck_Carter
Participant

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.

Former Member
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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

Breck_Carter
Participant
0 Kudos

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 🙂