Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Drop corrupt stored procedure

6 posts in General Discussion Last posting was on 2005-10-27 13:19:04.0Z
millerch Posted on 2005-10-26 16:56:03.0Z
Sender: 61c7.4358f768.1804289383@sybase.com
From: millerch@uphs.upenn.edu
Newsgroups: ianywhere.public.general
Subject: Drop corrupt stored procedure
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <435fb523.62a3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Oct 2005 09:56:03 -0700
X-Trace: forums-1-dub 1130345763 10.22.241.41 (26 Oct 2005 09:56:03 -0700)
X-Original-Trace: 26 Oct 2005 09:56:03 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4878
Article PK: 17458

Does anyone know how I can delete/drop a corrupt stored
procedure without using Sybase Central. Everytime I click
on the stored procedure within Sybase Central it locks up
the server. Only way to fix right now is to restart the
Service when it locks up.

Is there a way you can delete it from sysobjects?

Thanks
Chris


Stephen Rice Posted on 2005-10-26 19:55:01.0Z
From: Stephen Rice <NSsrice@ianywhere.com>
Reply-To: NSsrice@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Drop corrupt stored procedure
References: <435fb523.62a3.1681692777@sybase.com>
In-Reply-To: <435fb523.62a3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: srice-laptop.sybase.com
X-Original-NNTP-Posting-Host: srice-laptop.sybase.com
Message-ID: <435fdf15$1@forums-1-dub>
Date: 26 Oct 2005 12:55:01 -0700
X-Trace: forums-1-dub 1130356501 10.25.98.210 (26 Oct 2005 12:55:01 -0700)
X-Original-Trace: 26 Oct 2005 12:55:01 -0700, srice-laptop.sybase.com
Lines: 40
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4879
Article PK: 17455

Hi Chris

sysobjects is an ASE thing so I hope you haven't posted in the wrong
newsgroup.

You can DROP an object from Interactive SQL by connecting with the
appropriate permissions and using DROP PROCEDURE <procedure-name>

I expect you will find that you encounter the same difficulty since this
is pretty much what Sybase Central does. Does the database server
record any errors? It is unusual for it to lock up without issuing an
error (often an assertion). You can check the event log, or add a -o
<logfilename> to your server start command line.

If it continues to lock up I suspect the database is corrupt. I would
run dbvalid on it to see. If it is corrupt you will need to revert to
your backup and recover.

/steve

millerch@uphs.upenn.edu wrote:
> Does anyone know how I can delete/drop a corrupt stored
> procedure without using Sybase Central. Everytime I click
> on the stored procedure within Sybase Central it locks up
> the server. Only way to fix right now is to restart the
> Service when it locks up.
>
> Is there a way you can delete it from sysobjects?
>
> Thanks
> Chris

--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com


Chris Miller Posted on 2005-10-26 20:17:16.0Z
Sender: 65d8.435fe349.1804289383@sybase.com
From: Chris Miller
Newsgroups: ianywhere.public.general
Subject: Re: Drop corrupt stored procedure
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <435fe44b.65e7.1681692777@sybase.com>
References: <435fdf15$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Oct 2005 13:17:16 -0700
X-Trace: forums-1-dub 1130357836 10.22.241.41 (26 Oct 2005 13:17:16 -0700)
X-Original-Trace: 26 Oct 2005 13:17:16 -0700, 10.22.241.41
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4880
Article PK: 31467

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.

Any other suggestions would be greatly appreciated. This
stored procedure is the only thing that is corrupt. We
recently did a restore and we think this stored procedure
got corrupted from that. We were able to recreate the
stored procedure with a different name. We want to remove
the old one so that down the line, someone doesn't keep
locking up the database.

Thanks
Chris

> Hi Chris
>
> sysobjects is an ASE thing so I hope you haven't posted in
> the wrong newsgroup.
>
> You can DROP an object from Interactive SQL by connecting
> with the appropriate permissions and using DROP PROCEDURE
> <procedure-name>
>
> I expect you will find that you encounter the same
> difficulty since this is pretty much what Sybase Central
> does. Does the database server record any errors? It is
> unusual for it to lock up without issuing an error (often
> an assertion). You can check the event log, or add a -o
> <logfilename> to your server start command line.
>
> If it continues to lock up I suspect the database is
> corrupt. I would run dbvalid on it to see. If it is
> corrupt you will need to revert to your backup and
> recover.
>
> /steve
>
> millerch@uphs.upenn.edu wrote:
> > Does anyone know how I can delete/drop a corrupt stored
> > procedure without using Sybase Central. Everytime I
> > click on the stored procedure within Sybase Central it
> > locks up the server. Only way to fix right now is to
> > restart the Service when it locks up.
> >
> > Is there a way you can delete it from sysobjects?
> >
> > Thanks
> > Chris
>
> --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
>
> --- Please Post ---
> Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
> "Report a bug" are all available on www.ianywhere.com


Greg Fenton Posted on 2005-10-27 00:27:31.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Drop corrupt stored procedure
References: <435fdf15$1@forums-1-dub> <435fe44b.65e7.1681692777@sybase.com>
In-Reply-To: <435fe44b.65e7.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <43601ef3$1@forums-1-dub>
Date: 26 Oct 2005 17:27:31 -0700
X-Trace: forums-1-dub 1130372851 24.43.194.135 (26 Oct 2005 17:27:31 -0700)
X-Original-Trace: 26 Oct 2005 17:27:31 -0700, cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 30
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4881
Article PK: 31468


Chris Miller wrote:
> Any other suggestions would be greatly appreciated. This
> stored procedure is the only thing that is corrupt. We
> recently did a restore and we think this stored procedure
> got corrupted from that. We were able to recreate the
> stored procedure with a different name. We want to remove
> the old one so that down the line, someone doesn't keep
> locking up the database.
>

Please always post the version and build number of SQLAnywhere that you
are using (e.g. use the command "dbeng9 -v" for SQLAnywhere 9.x).

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/