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

Anyone else have problems with Sort/Merge in For Each loop?

Just wondering if I am an isolated case or just doing stuff other people don't. Ive created around a dozen packages for importing various types of data into a datawarehouse and some of the packages have random crashes. Over time I have been trying to tie together what the crashing ones have in common and now believe I have completely isolated it: Sort + Merge inside a dataflow in a for each loop. The more sorts and merges in the flow the more likely the problem is to occur.

This week I created 2 identical packages to import some XML... one used some gnarly complicated substrings and such to extract some data and add it as derived columns while the other used the XML correctly that produced 2 streams (one being meta data - other being detail data) and sorted + merged them. The gnarly substring package could run all night (processes about a file every 2 or 3 seconds so you can do the math) while the merged data flows crashes somewhere between 1 and 4 hrs of running - usually right around 3 hrs (also processes about 1 file every 2 seconds). The file it crashes on wil be completly random and not related to file size - I've had crashes on files with as little as 1 row of data and 1 row of metadata and as big as 1 row of metadata and 100,000+ rows of data. Another more complicated package I created has 4 merges (and thus 8 sorts) and it will crash anywhere between 5 minutes and 20 minutes of running.

The crash is usually one where the whole process just shuts down with a memory dump but occasionally I have gotten errors about out of memory or warnings about threads leaking buffers.

I am trying to do some "research" to see if others have this problem. I've spent 1.5 months writing all ETL jobs in SSIS and its gotten to the point management has asked me to explore other platforms. I do have a case open with Microsoft on the package with 4 merges - but we've spent weeks now just trying to get debugging tools to get them some useful info when the process crashes. I am running SP1 and kb91822.

Anyone else?

FYI to anyone that comes across this thread - msft identified a bug in the sort object (stack heap corruption) and now has a hotfix. If you run into this same situation please contact them (until the patch is released to the public)

Anybody ever use Actuate e.Reports?

I have an interview tomorrow for a dba position and they make frequent mention of this Actuate e.Report stuff and it looks like Crystal Reports from the web site. Any opinions? Is it garbage? Should I run and hide or chase and embrace? If its crap, I can blow this off. I have a few more things cooking.Yes, I used Actuate e.report before. Similar to Crystal Report, you can design your own e report for the internet. Nothing special, same design and running same sql scripting. Nothing special to me.|||We use Actuate at my work. I'm just an intermediate developer myself, but some of the reports developed by our central IT folks are just incredible. It seems to be very powerful, but kind of a pain to learn.