Dropping index in SYBASE ASE 12.0

[bschechter]

Ladies and Gentlemen,

We want to drop and recreate indexes via PowerBuilder powerscript using SYBASE ASE 12.0, but we are having problems with the drop side.

We can create them using:

ls_sql = "CREATE INDEX index1 on dba.table1"
EXECUTE IMMEDIATE :ls_sql;

However the drop SQL does not work:

ls_sql = "DROP INDEX dba.table1.index1"
EXECUTE IMMEDIATE :ls_sql;

Neither does:

ls_sql = "DROP INDEX table1.index1"
EXECUTE IMMEDIATE :ls_sql;

Any help would be greatly appreciated.

Regards,
Brian

 

[Starg]

Brian, are you getting any error messages either in the Powerscript or the server logs?

 

[bschechter]

Starg,

Yes SYBASE does not like the syntax if we use:
DROP INDEX dba.table1.index1

And SYBASE can not find the object if we use:
DROP INDEX table1.index1

Regards,
Brian

 

[Starg]

I know its a simple question, but are you using the same connection to create as you are to drop the index? If you change the SQL to "select db_name()" do they both return the same name?

Starg

 

[bschechter]

Yes. We have only one connection to DBMS (SQLCA) that we are using for all the SQL.

Brian

 

[dickiebird]

Permissions ? Grants ? Dickie Bird
db@dickiebird.freeserve.co.uk

 

[RayPeck]

My guess is you're not logged in as "dba", which appears to be the owner of the table. Whereas you can specify the database and owner on the create index command, there does not appear to be that same ability on the drop index command. I don't know if this is helpful, but if the user dropping the index were "dba" I believe it would work (syntax would just be "drop index table1.index1&quot

 

[bschechter]

If we create the index using syntax,

CREATE INDEX index1 on dba.table1

We can not drop the index using if we are connected as dba:

DROP INDEX table1.index1

It works if we leave the dba out when creating the table. It appears that the table is then created as dbo owner.

 

[JMCraig]

Very puzzling problem.

Have you tried posting to one of the PowerBuilder forums on Sybase's site? Might get some help there. Sybase apparently has a way to log questions like this without opening a support case now too--I don't know what the responsiveness of that would be.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net