Anyone Else Having Old Versions of Stuff Mysteriously Come Back to Haunt Them?

OK, so it's Halloween time and everything, but give me a
break! Here at my organization we've been having this
happen on occasion for a quite a while (maybe for the
last year or so) - old versions of things such as DTS
packages and stored procedures come back and become the
current version of that particular object. Now, we store
our DTS packages here in the msdb database, and we
haven't always deleted old versions of those, so maybe
somehow they are floating back to the top of the version
list somehow. But stored procedures? Just today I went to
check out a stored procedure I had written a while back
so that I could show one of my co-workers the code within
it, and saw that it had mysteriously reverted back to the
way it was before I changed it months ago to add the
aforementioned code. We managed to find the correct
version of the procedure, luckily, in a backup of the
database we had lying around, or I would have had to re-
write it all over again. What is particularly disturbing
about this incident is that the former version of the
stored procedure will execute successfully and return
results that appear correct on the surface. Those results
are only slightly flawed and the damage won't come to
light until much later. Not good!
We have not done any restores that would have turned back
the database to the point before the procedure or package
change; it's just that this reverting action will occur
on its own periodically with no warning.
Has anyone else experienced this? Does anyone know why
it's happening and what can be done about it?
We're running SQL Server 2000 on Windows 2000 Advanced
Server, and the current service pack (SP3a) is applied.
Thank you,
Mark Schmidt
Database Administrator
Pennsylvania House of RepresentativesMark,
Do you at all rename stored procedures? Sp_rename does not change the proc name in create proc in
syscomments and this might be a reason...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Schmidt" <MSchmidt@.pa_housewithouttheunderscore.net> wrote in message
news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
> OK, so it's Halloween time and everything, but give me a
> break! Here at my organization we've been having this
> happen on occasion for a quite a while (maybe for the
> last year or so) - old versions of things such as DTS
> packages and stored procedures come back and become the
> current version of that particular object. Now, we store
> our DTS packages here in the msdb database, and we
> haven't always deleted old versions of those, so maybe
> somehow they are floating back to the top of the version
> list somehow. But stored procedures? Just today I went to
> check out a stored procedure I had written a while back
> so that I could show one of my co-workers the code within
> it, and saw that it had mysteriously reverted back to the
> way it was before I changed it months ago to add the
> aforementioned code. We managed to find the correct
> version of the procedure, luckily, in a backup of the
> database we had lying around, or I would have had to re-
> write it all over again. What is particularly disturbing
> about this incident is that the former version of the
> stored procedure will execute successfully and return
> results that appear correct on the surface. Those results
> are only slightly flawed and the damage won't come to
> light until much later. Not good!
> We have not done any restores that would have turned back
> the database to the point before the procedure or package
> change; it's just that this reverting action will occur
> on its own periodically with no warning.
> Has anyone else experienced this? Does anyone know why
> it's happening and what can be done about it?
> We're running SQL Server 2000 on Windows 2000 Advanced
> Server, and the current service pack (SP3a) is applied.
> Thank you,
> Mark Schmidt
> Database Administrator
> Pennsylvania House of Representatives|||Thank you for your response, Tibor.
No, we haven't renamed any procs to cause this. Actually,
it came to my attention after I posted my question that
the problem with the stored procedure that I mentioned
may very well have been somebody re-creating the
procedure with old code and not realizing it until later.
But, when it comes to DTS packages, we definitely have
had this phenomenon occur several times with no apparent
intervention on anyone's part.
Mark
>--Original Message--
>Mark,
>Do you at all rename stored procedures? Sp_rename does
not change the proc name in create proc in
>syscomments and this might be a reason...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mark Schmidt"
<MSchmidt@.pa_housewithouttheunderscore.net> wrote in
message
>news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
>> OK, so it's Halloween time and everything, but give me
a
>> break! Here at my organization we've been having this
>> happen on occasion for a quite a while (maybe for the
>> last year or so) - old versions of things such as DTS
>> packages and stored procedures come back and become the
>> current version of that particular object. Now, we
store
>> our DTS packages here in the msdb database, and we
>> haven't always deleted old versions of those, so maybe
>> somehow they are floating back to the top of the
version
>> list somehow. But stored procedures? Just today I went
to
>> check out a stored procedure I had written a while back
>> so that I could show one of my co-workers the code
within
>> it, and saw that it had mysteriously reverted back to
the
>> way it was before I changed it months ago to add the
>> aforementioned code. We managed to find the correct
>> version of the procedure, luckily, in a backup of the
>> database we had lying around, or I would have had to
re-
>> write it all over again. What is particularly
disturbing
>> about this incident is that the former version of the
>> stored procedure will execute successfully and return
>> results that appear correct on the surface. Those
results
>> are only slightly flawed and the damage won't come to
>> light until much later. Not good!
>> We have not done any restores that would have turned
back
>> the database to the point before the procedure or
package
>> change; it's just that this reverting action will occur
>> on its own periodically with no warning.
>> Has anyone else experienced this? Does anyone know why
>> it's happening and what can be done about it?
>> We're running SQL Server 2000 on Windows 2000 Advanced
>> Server, and the current service pack (SP3a) is applied.
>> Thank you,
>> Mark Schmidt
>> Database Administrator
>> Pennsylvania House of Representatives
>
>.
>|||> But, when it comes to DTS packages, we definitely have
> had this phenomenon occur several times with no apparent
> intervention on anyone's part.
I still suspect sp_rename... ;-)
But without a repro, it if of course impossible to say.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Schmidt" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c39e52$ab954820$a001280a@.phx.gbl...
> Thank you for your response, Tibor.
> No, we haven't renamed any procs to cause this. Actually,
> it came to my attention after I posted my question that
> the problem with the stored procedure that I mentioned
> may very well have been somebody re-creating the
> procedure with old code and not realizing it until later.
> But, when it comes to DTS packages, we definitely have
> had this phenomenon occur several times with no apparent
> intervention on anyone's part.
> Mark
> >--Original Message--
> >Mark,
> >
> >Do you at all rename stored procedures? Sp_rename does
> not change the proc name in create proc in
> >syscomments and this might be a reason...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mark Schmidt"
> <MSchmidt@.pa_housewithouttheunderscore.net> wrote in
> message
> >news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
> >> OK, so it's Halloween time and everything, but give me
> a
> >> break! Here at my organization we've been having this
> >> happen on occasion for a quite a while (maybe for the
> >> last year or so) - old versions of things such as DTS
> >> packages and stored procedures come back and become the
> >> current version of that particular object. Now, we
> store
> >> our DTS packages here in the msdb database, and we
> >> haven't always deleted old versions of those, so maybe
> >> somehow they are floating back to the top of the
> version
> >> list somehow. But stored procedures? Just today I went
> to
> >> check out a stored procedure I had written a while back
> >> so that I could show one of my co-workers the code
> within
> >> it, and saw that it had mysteriously reverted back to
> the
> >> way it was before I changed it months ago to add the
> >> aforementioned code. We managed to find the correct
> >> version of the procedure, luckily, in a backup of the
> >> database we had lying around, or I would have had to
> re-
> >> write it all over again. What is particularly
> disturbing
> >> about this incident is that the former version of the
> >> stored procedure will execute successfully and return
> >> results that appear correct on the surface. Those
> results
> >> are only slightly flawed and the damage won't come to
> >> light until much later. Not good!
> >>
> >> We have not done any restores that would have turned
> back
> >> the database to the point before the procedure or
> package
> >> change; it's just that this reverting action will occur
> >> on its own periodically with no warning.
> >>
> >> Has anyone else experienced this? Does anyone know why
> >> it's happening and what can be done about it?
> >>
> >> We're running SQL Server 2000 on Windows 2000 Advanced
> >> Server, and the current service pack (SP3a) is applied.
> >>
> >> Thank you,
> >>
> >> Mark Schmidt
> >> Database Administrator
> >> Pennsylvania House of Representatives
> >
> >
> >.
> >|||Well, my developers are not telling me that they're doing
renames, and I have to go with that for now.
Thank you very much for the help and for something to
look for, Tibor.
Mark
>--Original Message--
>> But, when it comes to DTS packages, we definitely have
>> had this phenomenon occur several times with no
apparent
>> intervention on anyone's part.
>I still suspect sp_rename... ;-)
>But without a repro, it if of course impossible to say.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mark Schmidt" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0f01c39e52$ab954820$a001280a@.phx.gbl...
>> Thank you for your response, Tibor.
>> No, we haven't renamed any procs to cause this.
Actually,
>> it came to my attention after I posted my question that
>> the problem with the stored procedure that I mentioned
>> may very well have been somebody re-creating the
>> procedure with old code and not realizing it until
later.
>> But, when it comes to DTS packages, we definitely have
>> had this phenomenon occur several times with no
apparent
>> intervention on anyone's part.
>> Mark
>> >--Original Message--
>> >Mark,
>> >
>> >Do you at all rename stored procedures? Sp_rename does
>> not change the proc name in create proc in
>> >syscomments and this might be a reason...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mark Schmidt"
>> <MSchmidt@.pa_housewithouttheunderscore.net> wrote in
>> message
>> >news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
>> >> OK, so it's Halloween time and everything, but give
me
>> a
>> >> break! Here at my organization we've been having
this
>> >> happen on occasion for a quite a while (maybe for
the
>> >> last year or so) - old versions of things such as
DTS
>> >> packages and stored procedures come back and become
the
>> >> current version of that particular object. Now, we
>> store
>> >> our DTS packages here in the msdb database, and we
>> >> haven't always deleted old versions of those, so
maybe
>> >> somehow they are floating back to the top of the
>> version
>> >> list somehow. But stored procedures? Just today I
went
>> to
>> >> check out a stored procedure I had written a while
back
>> >> so that I could show one of my co-workers the code
>> within
>> >> it, and saw that it had mysteriously reverted back
to
>> the
>> >> way it was before I changed it months ago to add the
>> >> aforementioned code. We managed to find the correct
>> >> version of the procedure, luckily, in a backup of
the
>> >> database we had lying around, or I would have had to
>> re-
>> >> write it all over again. What is particularly
>> disturbing
>> >> about this incident is that the former version of
the
>> >> stored procedure will execute successfully and
return
>> >> results that appear correct on the surface. Those
>> results
>> >> are only slightly flawed and the damage won't come
to
>> >> light until much later. Not good!
>> >>
>> >> We have not done any restores that would have turned
>> back
>> >> the database to the point before the procedure or
>> package
>> >> change; it's just that this reverting action will
occur
>> >> on its own periodically with no warning.
>> >>
>> >> Has anyone else experienced this? Does anyone know
why
>> >> it's happening and what can be done about it?
>> >>
>> >> We're running SQL Server 2000 on Windows 2000
Advanced
>> >> Server, and the current service pack (SP3a) is
applied.
>> >>
>> >> Thank you,
>> >>
>> >> Mark Schmidt
>> >> Database Administrator
>> >> Pennsylvania House of Representatives
>> >
>> >
>> >.
>> >
>
>.
>sql