sp_hidetext hid everything!!

[mark_gearhart1]

I was testing sp_hidetext for my stored procedure and accidently left off the proc name as the first arg.

Now, every single proc into the database is hidden!! My fault.

Is there any way to reverse the damage that I have just caused?

Thanks

[marcin_najs · Accepted solution]

Hi.

The results of executing sp_hidetext are not reversible.

You have to reompile your stored procedure.

Regards.

--
Marcin

[Former Member · Accepted solution]

Marcin,

A bit more detail would be good. Avinash can:

1. Drop and recreate ALL the procedures

OR

2. Like Bret says, reload the most recent dump of the database.

Regards,

Jean-Pierre

[kevin_sherlock]

Also, depends on version.  If you have ASE 15.x ....

There is a way to recover the text.  However, since ASE 15.x is still out there and supported, I'm not inclined to share the method publicly as I feel it would be a security issue and not proper for this forum.

If this is ASE 15.x, and you absolutely don't have any other recourse, open a SAP/SYBASE case and request the details of the recovery method.

And BTW - for anyone who would claim that I've said something improper by just saying it's possible.  The method can be derived by anyone who has a copy of the ASE software, and even a mediocre ability to sleuth it out.

[kevin_sherlock]

If you are really desperate, the rows in syscomments might still be there as deleted rows (DOL_ROW_DELETED status).  You could use dbcc page(<dbid>,<pagenumber>,1) to look at deleted rows of the "syscomments" table.

select firstpage from syspartitions where id = object_id('syscomments')

Start your dbcc page commands from that page and look for the DOL_ROW_DELETED rows that contain the old source code. 

[former_member89972]

sp_hidetext does not change the crdate for a  procedure.

New procedures added between you doing sp_hidetext and now need not be touched.

Earlier ones can be recreated by loading  the database by a different name, extract and recreate.

crdate will of course change when you drop and recreate.

HTH

Avinash

[former_member188958]

You can load your most recent full backup, either entirely replacing the current image of the database, or just using the "archive database" feature so that you can extract the procedure source code.