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

asked 09 Feb, 06:12

rohinibasuu's gravatar image

rohinibasuu
56413
accept rate: 0%

edited 09 Feb, 06:21

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%';

(09 Feb, 16:46) Nick Elson S...

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, 07:02

Breck%20Carter's gravatar image

Breck Carter
25.7k427586845
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, 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, 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, 12:58) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×76
×2

question asked: 09 Feb, 06:12

question was seen: 140 times

last updated: 09 Feb, 16:46