SQL Anywhere database - Delete from sysobjects

Author

Delete from sysobjects

Chris Miller

2005-10-27, 7:41 am

We noticed a stored procedure was corrupt in our database.
We have tried numerous ways to delete it.
Ex. "drop owner.procname" from an interactive SQL window
and right click on the procedure itself in Sybase Central
and tried deleting it that way.

Everytime we attempt to do this, it locks up the database
and we have to restart the Service.

We ran "Validate Database" and it came back with no errors.

The only thing we can come up with now is to try to delete
it from the sysobjects table.
Would I have to delete it from the sysprocedures table also?
Does anyone see any problems that could occur from doing
this?
Is there any other means by which we could try first?

Breck Carter [TeamSybase]

2005-10-27, 7:41 am

You are talking about SQL Anywhere/ASA, right? not ASE?

You won't be able to delete from sysobjects; it a view based on a
UNION.

You cannot explicitly delete anything from any SYS.* table in SQL
Anywhere.

What version and build are you using?

Breck

On 26 Oct 2005 11:05:44 -0700, Chris Miller wrote:

>We noticed a stored procedure was corrupt in our database.
>We have tried numerous ways to delete it.
>Ex. "drop owner.procname" from an interactive SQL window
>and right click on the procedure itself in Sybase Central
>and tried deleting it that way.
>
>Everytime we attempt to do this, it locks up the database
>and we have to restart the Service.
>
>We ran "Validate Database" and it came back with no errors.
>
>The only thing we can come up with now is to try to delete
>it from the sysobjects table.
>Would I have to delete it from the sysprocedures table also?
>Does anyone see any problems that could occur from doing
>this?
>Is there any other means by which we could try first?

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Chris Miller

2005-10-27, 7:41 am

SQL Anywhere 8.03
Anyway I can get this off my database then?

Chris

> You are talking about SQL Anywhere/ASA, right? not ASE?
>
> You won't be able to delete from sysobjects; it a view
> based on a UNION.
>
> You cannot explicitly delete anything from any SYS.* table
> in SQL Anywhere.
>
> What version and build are you using?
>
> Breck
>
> On 26 Oct 2005 11:05:44 -0700, Chris Miller wrote:
>
> database. >We have tried numerous ways to delete it.
> errors. >
> delete >it from the sysobjects table.
> also? >Does anyone see any problems that could occur from
> doing >this?
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Mike Emery

2005-10-27, 7:41 am

Out of curiosity, how have you determined that the procedure is corrupt?
Does the database give an assertion? Also, what is your specific build
number?

--
-Mike Emery
Product Support Analyst
iAnywhere Solutions

<Chris Miller> wrote in message news:435fc578.7a52.1681692777@sybase.com...
> We noticed a stored procedure was corrupt in our database.
> We have tried numerous ways to delete it.
> Ex. "drop owner.procname" from an interactive SQL window
> and right click on the procedure itself in Sybase Central
> and tried deleting it that way.
>
> Everytime we attempt to do this, it locks up the database
> and we have to restart the Service.
>
> We ran "Validate Database" and it came back with no errors.
>
> The only thing we can come up with now is to try to delete
> it from the sysobjects table.
> Would I have to delete it from the sysprocedures table also?
> Does anyone see any problems that could occur from doing
> this?
> Is there any other means by which we could try first?

Chris Keating(iAnywhere Solutions)

2005-10-27, 7:41 am

If all you want to do is drop the procedure, have you tried DROP
PROCEDURE <proc_name> from DBISQL?

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

********************
********************
********************
*****************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

********************
********************
********************
*****************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

********************
********************
********************
*****************

Chris Miller

2005-10-27, 9:23 am

It's corrupt because we can not view/edit/delete/rename it.
If we try doing any of the following from Sybase Cental or
command prompt, it locks up the database and we have to
restart the Service.

We are on Build 8.03

Also we have tried using dbisql drop procedure and it does
the same thing.

Thanks
Chris

> Out of curiosity, how have you determined that the
> procedure is corrupt? Does the database give an assertion?
> Also, what is your specific build number?
>
> --
> -Mike Emery
> Product Support Analyst
> iAnywhere Solutions
>
> <Chris Miller> wrote in message
> tried deleting it that way. >
> errors. >
>
>

Greg Fenton

2005-10-27, 9:23 am

Chris Miller wrote:
> It's corrupt because we can not view/edit/delete/rename it.
> If we try doing any of the following from Sybase Cental or
> command prompt, it locks up the database and we have to
> restart the Service.

From my post in the other newsgroup, you didn't answer these questions:

Can you indicate what you mean by "the database locks up" ?
Are all connections blocked?
Can you establish any new connections with DBISQL? If so, does what
does sa_conn_info and sa_locks tell you about what is going on?

Are you logging the server's console to a log file (-o <filename> on the
server startup line)? If so, are there any error or warning messages in
there?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Mark Culp

2005-10-28, 11:23 am

When you say "it doesn't work", what do you mean -
What statement did you execute, and
what error message do you get?
--
Mark Culp
SQLAnywhere Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
Chris, Miller wrote:[color=darkred
]
>
> Doesn't work I tried.
>
> Version 8.03
>
> Thanks
> Chris M
>

Pavel

2005-10-31, 9:23 am

If everything fails as you say, I'd recommend unloading the
DB and editing the reload file (usually reload.sql).
[color=darkred]
> When you say "it doesn't work", what do you mean -
> What statement did you execute, and
> what error message do you get?
> --
> Mark Culp
> SQLAnywhere Research and Development
> ----------------------------------------------------------
> --------------- ** Whitepapers, TechDocs, bug fixes are
> all available through the ** ** iAnywhere Developer
> Community at http://www.ianywhere.com/developer **
> ----------------------------------------------------------
> --------------- Chris, Miller wrote:
> ********************
********************
******************
> the market-leading database for mobile, embedded and small
> ********************
********************
******************
> ********************
********************
******************
> *******************[
/color]

Chris Miller

2005-10-31, 9:23 am

What I mean by locks up is that is freezes Sybase central.
We can not run anymore commands, look in any other tables,
etc.... We cannot log into the database or establish any
more connection strings into the database. No error message
is ever generated. Tried using Sybase central and DBISQL
connection strings.

Please people, read all strings before replying. I'm sick
of repeating myself.

Thank you,

Chris Miller

> Chris Miller wrote:
>
> From my post in the other newsgroup, you didn't answer
> these questions:
>
> Can you indicate what you mean by "the database locks up"
> ? Are all connections blocked?
> Can you establish any new connections with DBISQL? If so,
> does what does sa_conn_info and sa_locks tell you about
> what is going on?
>
> Are you logging the server's console to a log file (-o
> <filename> on the server startup line)? If so, are there
> any error or warning messages in there?
>
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Chris Keating(iAnywhere Solutions)

2005-10-31, 11:23 am

You'll probably need to contact Technical Support to get this issue
resolved. Within North America, you can reach Technical Support by
calling 1-800-8SYBASE. If you are located outside of North America, you
can find the contact information for your local Sybase Support Center by
going to the Sybase home page at http://www.sybase.com, clicking on the
"Support" link at the top of the page, and then choosing "Support
Centers" from the left hand frame.

You can always report a bug for free by visiting CaseXpress at
http://casexpress.sybase.com/cx/cx.stm. When submitting calls through
CaseXpress, please make sure to give an accurate description of the
steps you take to reproduce the bug, and include any files that may be
needed (database, source code) to reproduce the problem.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

********************
********************
********************
*****************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

********************
********************
********************
*****************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

********************
********************
********************
*****************

Greg Fenton

2005-10-31, 11:23 am

Chris Miller wrote:
>
> Please people, read all strings before replying. I'm sick
> of repeating myself.
>

I did read all of your posts, in their entirety, in both threads in
*both* newsgroups to which you posted.

The questions asked were to get clarification on points you had made
(e.g. you never stated whether you attempted to log in with a separate
program when the database "locked up").

You have also stated that a system stored procedure is "corrupt",
something I've never heard of before and was asking for a description of
what that terminology means.

Please recognize that quite often newsgroup threads go way off in a
direction that they don't need to simply because of different
terminologies used by different posters. This is why quite often you'll
see posts from people asking for Very Basic Information before we even
begin addressing the issue at hand (e.g. what OS, what version and build
number, etc...).

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links