Hello, When I am trying to call a stored procedure in SQL Anywhere 16 via dbisql with the following command:
When I am connecting to the database with the same connection string:
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
|
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. 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
|
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%';