Re: Stored Procedures

[Steve Krim <steve_krim@skprof.com>]

Those are in fact sqr's that were terminated abnormally...  They are safe
to drop.  Just be carefule you don't drop on that is executing!!!

The shell script I use is simple and as follows:
***************** Start Shell Script ********************
#! /bin/ksh
. setenv.psoft
LSTFLE="stored_procs.tmp"
LSTFLE1="stored_procs"
LSTFLE2="stored_procs.log"
DB_NAME=[dbname]

rm -f $LSTFLE
rm -f $LSTFLE1
rm -f $LSTFLE2

isql -U$USER -P$PASSWD -Ssrvr <<EOF1 > $LSTFLE
use $DB_NAME
go
select name
from sysobjects
where type = 'P'
  and name like 'SQR%'
  and crdate < getdate()
go
EOF1
sed -e '1,2d' -e '/row/d' -e '/^$/d' $LSTFLE >$LSTFLE1
rm -f $LSTFLE

exec < $LSTFLE1
while read STORED_PROCEDURE
do
echo "Dropping $STORED_PROCEDURE"
isql -U$USER -P$PASSWD -Ssrvr <<EOF2 >$LSTFLE2 >>$LSTFLE2
use $DB_NAME
go
drop procedure $STORED_PROCEDURE
go
EOF2
done

exit 0

****************** End Shell Script ****************************

And I schedule this nightly...

HTH,

Steve
At 12:39 PM 8/16/99 -0500, you wrote:
>SQRUG,
>
>I am using SQR 4.3.2.2 / Sybase 11.x / Windows95 as my development
>environment and SQR seems to be leaving stored procedures on the database.
>There are over 30 procedures, with names like, SQR_4294834497_929551157,
>residing on our database and I'm not sure where they came from.  My guess
>is that they are temporary procs that were created and not destroyed,
>possibly because the SQR execution was interupted.  Are there any dangers
>associated with droping these procedures?  My DBA would really like to get
>rid of them, but I would like to make sure they aren't needed first.
>
>TIA,
>
>Todd
>
>