Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:
> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, Robert
I'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL2000
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causing
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fatal error 625 - Connection broken problem (Help needed)

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000
database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok
and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing
that error message?
Thanks in advance for all help received!
Regards, RobertI'd run DBCC CHECKDB immediately.
Linchi
"ROBinBrampton" wrote:

> Hello everyone,
> When I execute the following statement in the query analyzer on a msSQL200
0
> database...
> delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where
> Userid=78 and DVDID=8 and onhold=0);
> I get the following error...
> Server: Msg 21, Level 20, State 1, Line 1
> Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
> Connection Broken
> If I execute a simple SELECT statement, or any other statement, it runs ok
> and doesn't cause an error.
> Is there anything wrong with the above statement? and how can it be causin
g
> that error message?
> Thanks in advance for all help received!
> Regards, Robert
>

Fastest way to delete to huge datarow?

Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:

> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:

> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert

Fastest way to delete to huge datarow?

Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.de
Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:

> On 04.12.2006 15:29, Gerrit Horeis wrote:
>
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert

Fastest way to delete to huge datarow?

Hi all,
I've got a table which is storing Images with a size up to 1GB. When
I'm going to delete one of this rows the DELETE Statement takes up to 5
minutes. Which is the best way to delete the row more faster?
Has anyone an idea? Thanks in advance!
--
Gerrit Horeis
Software Developer
CI-Gate Development & Consulting GmbH
http://www.ci-gate.de
http://www.xira.de
http://www.bitbauer.deOn 04.12.2006 15:29, Gerrit Horeis wrote:
> I've got a table which is storing Images with a size up to 1GB. When
> I'm going to delete one of this rows the DELETE Statement takes up to 5
> minutes. Which is the best way to delete the row more faster?
> Has anyone an idea? Thanks in advance!
Did you measure what is slow there? Do you have an index on that table?
Is it used for deletion? ... With the little information you disclose
it's hard to come up with any reasonable advice.
Regards
robert|||Hi
Thanks for your answer. In the table im storing several image formats.
The delete Statement is
"Delete from Formats where ID = @.FormatID"
"ID" is the primary key of the table. Do I have to create an index as
well? I ever thought Sql just removes the reference to the data and so
it must be very fast.
Please help!
Robert Klemme schrieb:
> On 04.12.2006 15:29, Gerrit Horeis wrote:
> > I've got a table which is storing Images with a size up to 1GB. When
> > I'm going to delete one of this rows the DELETE Statement takes up to 5
> >
> > minutes. Which is the best way to delete the row more faster?
> >
> > Has anyone an idea? Thanks in advance!
> Did you measure what is slow there? Do you have an index on that table?
> Is it used for deletion? ... With the little information you disclose
> it's hard to come up with any reasonable advice.
> Regards
> robert|||On 05.12.2006 14:22, Gerrit Horeis wrote:
> Thanks for your answer. In the table im storing several image formats.
> The delete Statement is
> "Delete from Formats where ID = @.FormatID"
> "ID" is the primary key of the table. Do I have to create an index as
> well? I ever thought Sql just removes the reference to the data and so
> it must be very fast.
It seems you got the basics right. But storage of BLOBS is usually more
complex and less efficient than, say, VARCHAR columns. I guess you have
to run the Profiler to find out why it is actually slow.
Kind regards
robert

Fastest way to delete hundreds of table triggers and hundreds of stored procedures?

How can i delete all user stored procedures and all table triggers very fast
in
a single database?

Thank youYou can run the following script in Query Analyzer. Be certain you are in
the correct database.

USE MyDatabase
DECLARE @.DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N'DROP ' +
CASE xtype
WHEN 'P' THEN N'PROCEDURE '
WHEN 'TR' THEN N'TRIGGER '
END +
QUOTENAME(USER_NAME(uid)) +
N'.' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN('P', 'TR')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> How can i delete all user stored procedures and all table triggers very
> fast
> in
> a single database?
> Thank you|||I will try this today.

Thank you

> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements|||"serge" <sergea@.nospam.ehmail.com> wrote in message
news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
> I will try this today.

As we discover Bank of America goes offline since their databases somehow
lost their entire schema in a hacker attack. :-)

> Thank you
> > You can run the following script in Query Analyzer. Be certain you are
in
> > the correct database.
> > USE MyDatabase
> > DECLARE @.DropStatement nvarchar(4000)
> > DECLARE DropStatements CURSOR
> > LOCAL FAST_FORWARD READ_ONLY FOR
> > SELECT N'DROP ' +
> > CASE xtype
> > WHEN 'P' THEN N'PROCEDURE '
> > WHEN 'TR' THEN N'TRIGGER '
> > END +
> > QUOTENAME(USER_NAME(uid)) +
> > N'.' +
> > QUOTENAME(name)
> > FROM sysobjects
> > WHERE xtype IN('P', 'TR')
> > OPEN DropStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM DropStatements INTO @.DropStatement
> > IF @.@.FETCH_STATUS = -1 BREAK
> > EXEC(@.DropStatement)
> > END
> > CLOSE DropStatements
> > DEALLOCATE DropStatements|||Well, at least the tables are still there :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Xxh0d.21421$2s.7550@.twister.nyroc.rr.com...
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
>> I will try this today.
>
> As we discover Bank of America goes offline since their databases somehow
> lost their entire schema in a hacker attack. :-)
>
>>
>> Thank you
>>
>> > You can run the following script in Query Analyzer. Be certain you are
> in
>> > the correct database.
>>> > USE MyDatabase
>> > DECLARE @.DropStatement nvarchar(4000)
>> > DECLARE DropStatements CURSOR
>> > LOCAL FAST_FORWARD READ_ONLY FOR
>> > SELECT N'DROP ' +
>> > CASE xtype
>> > WHEN 'P' THEN N'PROCEDURE '
>> > WHEN 'TR' THEN N'TRIGGER '
>> > END +
>> > QUOTENAME(USER_NAME(uid)) +
>> > N'.' +
>> > QUOTENAME(name)
>> > FROM sysobjects
>> > WHERE xtype IN('P', 'TR')
>> > OPEN DropStatements
>> > WHILE 1 = 1
>> > BEGIN
>> > FETCH NEXT FROM DropStatements INTO @.DropStatement
>> > IF @.@.FETCH_STATUS = -1 BREAK
>> > EXEC(@.DropStatement)
>> > END
>> > CLOSE DropStatements
>> > DEALLOCATE DropStatements
>>
>>|||What's the fastest way to delete tables? :)

> Well, at least the tables are still there :-)|||I ran this and it was fast, it's what i was looking for.

I am trying to figure out how to delete ONLY the user objects and not the
system objects.

By changing the WHERE condition to become:

WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

I tried running it and it seems it did not delete the Stored Procedures with
TYPE = System.

I just want to make sure this condition I am using :

AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

is the right one, that I am not screwing other things unknowingly?

Thank you

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> > How can i delete all user stored procedures and all table triggers very
> > fast
> > in
> > a single database?
> > Thank you|||>> Well, at least the tables are still there :-)

"serge" <sergea@.nospam.ehmail.com> wrote in news:BRJ0d.35921$lP4.2446119
@.news20.bellglobal.com:

> What's the fastest way to delete tables? :)

USE master
DROP DATABASE CriticalFinancialInfo
GO|||> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

Yes, this is correct. I should have included this in the script I posted.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:tdK0d.36158$lP4.2450872@.news20.bellglobal.com ...
>I ran this and it was fast, it's what i was looking for.
> I am trying to figure out how to delete ONLY the user objects and not the
> system objects.
> By changing the WHERE condition to become:
>
> WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> I tried running it and it seems it did not delete the Stored Procedures
> with
> TYPE = System.
> I just want to make sure this condition I am using :
> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> is the right one, that I am not screwing other things unknowingly?
> Thank you
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
>> You can run the following script in Query Analyzer. Be certain you are
>> in
>> the correct database.
>>
>> USE MyDatabase
>> DECLARE @.DropStatement nvarchar(4000)
>> DECLARE DropStatements CURSOR
>> LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT N'DROP ' +
>> CASE xtype
>> WHEN 'P' THEN N'PROCEDURE '
>> WHEN 'TR' THEN N'TRIGGER '
>> END +
>> QUOTENAME(USER_NAME(uid)) +
>> N'.' +
>> QUOTENAME(name)
>> FROM sysobjects
>> WHERE xtype IN('P', 'TR')
>> OPEN DropStatements
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DropStatements INTO @.DropStatement
>> IF @.@.FETCH_STATUS = -1 BREAK
>> EXEC(@.DropStatement)
>> END
>> CLOSE DropStatements
>> DEALLOCATE DropStatements
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "serge" <sergea@.nospam.ehmail.com> wrote in message
>> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
>> > How can i delete all user stored procedures and all table triggers very
>> > fast
>> > in
>> > a single database?
>>> > Thank you
>>>>>
>>|||My quest continues, maybe i should explain what i am trying to achieve.

There is a database with 3000+ Stored Procedures. We give copies to other
people
and we continue making updates to the *development* database. When we want
to give the other people our latest stored procedures, we have code that
deletes all
stored procedures one by one, thus taking maybe 30 minutes to delete.
Then we recreate all the SPs.

Now i wanted to find out if there was a way to speed this process, i
originally thought
that deleting all SPs one shot could do the trick. But the further i analyze
it, i see some complications.

For example, the other people could very well have created their own SPs,
how can
i NOT delete those SPs?

Do you or anyone else have any idea how i can accomplish this?

Thank you

> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> Yes, this is correct. I should have included this in the script I posted.|||"serge" <sergea@.nospam.ehmail.com> wrote in
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com :

> My quest continues, maybe i should explain what i am trying to
> achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people
> and we continue making updates to the *development* database. When we
> want to give the other people our latest stored procedures, we have
> code that deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can
> i NOT delete those SPs?

You *need* version control.

I suppose the simplest way without spending any money would be to add a
table with the current version number of each stored procedure. Your
"update db with new stored procedures" would have to check that version
number and decide whether to drop and recreate the stored procedure.

Taking the idea a step further, the version table would also contain the
complete source of the stored procedure. Then another stored procedure
could cursor over the table, doing the drops and creates. Distributing
your new procedures would involve exporting this table to an external file
(say an MDB) and distributing it to the other recipients.|||serge (sergea@.nospam.ehmail.com) writes:
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people and we continue making updates to the *development*
> database. When we want to give the other people our latest stored
> procedures, we have code that deletes all stored procedures one by one,
> thus taking maybe 30 minutes to delete. Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought that deleting all SPs one shot could do the trick.
> But the further i analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can i NOT delete those SPs?

First of all, does their license permit them to add their own stored
procedures?

As Ross said, you need version control.

In our shop we have all our stored procedures, triggers, tables, in short
all database objects under version control in SourceSafe, and SourceSafe
is the master for all building efforts.

To build and install we have a toolset, called AbaPerls. One tool is
DBBUILD which builds an entire database from scripts, that is tables,
stored procedures, el todo. DBBUILD is also what we add when we add
a new component, or subsystem as we call it, to the database. Then we
have another tool DBUPDGEN which reads SourceSafe, and finds all changes
between two labels and that produces an update script. (For changed tables
you get a template to move over the data, but in most cases you have to
modify the generated code.) Furthermore, AbaPerls has its own set of
tables, so we know what we have loaded into a database. There is also
a stored procedure which lists mismatches between AbaPerls and SQL Server's
own system tables. Customer-added code would end up there.

I have made AbaPerls available as freeware on http://www.abaris.se/abaperls/
But as Ross outlined, you can achieve something a lot simpler with quite
easy means, and it may be enough for your organization.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I understand and I do agree your idea is very good. But at the moment, I am
trying
and hoping that there is a simpler way to rely on the WHERE Condition and
using
some custom field or something in the sysobjects table to decide whether
that SP
is one of our own SP and not delete it.

Thanks for your idea. I'll have to keep this in mind, I might have to use
this
approach in the future.

> You *need* version control.
> I suppose the simplest way without spending any money would be to add a
> table with the current version number of each stored procedure. Your
> "update db with new stored procedures" would have to check that version
> number and decide whether to drop and recreate the stored procedure.
> Taking the idea a step further, the version table would also contain the
> complete source of the stored procedure. Then another stored procedure
> could cursor over the table, doing the drops and creates. Distributing
> your new procedures would involve exporting this table to an external file
> (say an MDB) and distributing it to the other recipients.|||We do have SourceSafe on our end but not to go into details here,
that wouldn't be a viable solution at the time being. Maybe in the future.

Like i just replied to Ross, at the moment, I am trying and hoping that
there is a simpler way to rely on the WHERE Condition and using
some custom field or something in the sysobjects table to decide whether
that SP is one of our own SP and not delete it.

It's very appreciated that you've made your AbaPerls toolset freeware for
everyone to use. I have taken note of the link and I will have to look at
this
in great length in the future. At this moment, it's very hard to switch to
using
tools for what i require. If i can get away with a simple WHERE condition
to NOT delete SPs not created by us, I'll be very interested to use.

Due to time constraints, I don't want to add a new project to work on.
This task of deleting SPs fast is already not part of my regular work, add
to that I have to figure out ways to improve some slow SPs we have (again
not part of my regular work).

Thank you

> First of all, does their license permit them to add their own stored
> procedures?
> As Ross said, you need version control.
> In our shop we have all our stored procedures, triggers, tables, in short
> all database objects under version control in SourceSafe, and SourceSafe
> is the master for all building efforts.
> To build and install we have a toolset, called AbaPerls. One tool is
> DBBUILD which builds an entire database from scripts, that is tables,
> stored procedures, el todo. DBBUILD is also what we add when we add
> a new component, or subsystem as we call it, to the database. Then we
> have another tool DBUPDGEN which reads SourceSafe, and finds all changes
> between two labels and that produces an update script. (For changed tables
> you get a template to move over the data, but in most cases you have to
> modify the generated code.) Furthermore, AbaPerls has its own set of
> tables, so we know what we have loaded into a database. There is also
> a stored procedure which lists mismatches between AbaPerls and SQL
Server's
> own system tables. Customer-added code would end up there.
> I have made AbaPerls available as freeware on
http://www.abaris.se/abaperls/
> But as Ross outlined, you can achieve something a lot simpler with quite
> easy means, and it may be enough for your organization.|||serge (sergea@.nospam.ehmail.com) writes:
> Like i just replied to Ross, at the moment, I am trying and hoping that
> there is a simpler way to rely on the WHERE Condition and using
> some custom field or something in the sysobjects table to decide whether
> that SP is one of our own SP and not delete it.

There is no such custom field. Possibly you could add some condition which
looked in syscomments for things you recognize, but that would be completely
bizarre to do.

You would have to have list of known procedures to delete. Then again, that
is not very difficult to make effecient:

CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
DECLARE @.proc sysname
DECLARE drop_cur INSENSITIVE CURSOR FOR
SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
JOIN sysobjects o ON i.nstr = o.name
WHERE o.xtype = 'P'
OPEN drop_cur
WHILE 1 = 1
FETCH drop_cur INTO @.proc
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP PROCEDURE ' + @.proc)
END
DEALLOCATE drop_cur

iter_charlist_to_tbl is on
http://www.sommarskog.se/arrays-in-...list-of-strings

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As already mentioned, a manifest and/or version control to probably the best
long term solution. In the interim. you might consider adding extended
properties to the objects you own so that you can more easily identify
these. You'll need to include the extended properties in your DDL scripts.

CREATE PROC MyProcedure
AS
SELECT 'MyProcedure'
GO

EXEC sp_addextendedproperty
'OwnedBy',
'MyApplication',
'USER',
'dbo',
'PROCEDURE',
'MyProcedure'
GO

--this
SELECT *
FROM sysobjects o
JOIN ::fn_listextendedproperty(
'OwnedBy',
'USER',
'dbo',
'PROCEDURE',
NULL,
NULL,
NULL
) ep ON o.name = ep.objname
WHERE o.xtype = 'P' AND
ep.value = 'MyApplication'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com ...
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to other
> people
> and we continue making updates to the *development* database. When we want
> to give the other people our latest stored procedures, we have code that
> deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze
> it, i see some complications.
> For example, the other people could very well have created their own SPs,
> how can
> i NOT delete those SPs?
> Do you or anyone else have any idea how i can accomplish this?
> Thank you
>
>> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
>>
>> Yes, this is correct. I should have included this in the script I
>> posted.|||Thanks again for the post. I'll have to look into this more closely
in the next few days (hopefully).

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95636012ECE1Yazorman@.127.0.0.1...
> serge (sergea@.nospam.ehmail.com) writes:
> > Like i just replied to Ross, at the moment, I am trying and hoping that
> > there is a simpler way to rely on the WHERE Condition and using
> > some custom field or something in the sysobjects table to decide whether
> > that SP is one of our own SP and not delete it.
> There is no such custom field. Possibly you could add some condition which
> looked in syscomments for things you recognize, but that would be
completely
> bizarre to do.
> You would have to have list of known procedures to delete. Then again,
that
> is not very difficult to make effecient:
> CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
> DECLARE @.proc sysname
> DECLARE drop_cur INSENSITIVE CURSOR FOR
> SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
> JOIN sysobjects o ON i.nstr = o.name
> WHERE o.xtype = 'P'
> OPEN drop_cur
> WHILE 1 = 1
> FETCH drop_cur INTO @.proc
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP PROCEDURE ' + @.proc)
> END
> DEALLOCATE drop_cur
> iter_charlist_to_tbl is on
> http://www.sommarskog.se/arrays-in-...list-of-strings
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks interesting. Having extended properties to the SP.
I'll have to investigate this further, hopefully soon.

Thank you again.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:rnh1d.10373$yp2.9988@.newssvr30.news.prodigy.c om...
> As already mentioned, a manifest and/or version control to probably the
best
> long term solution. In the interim. you might consider adding extended
> properties to the objects you own so that you can more easily identify
> these. You'll need to include the extended properties in your DDL
scripts.
> CREATE PROC MyProcedure
> AS
> SELECT 'MyProcedure'
> GO
> EXEC sp_addextendedproperty
> 'OwnedBy',
> 'MyApplication',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> 'MyProcedure'
> GO
> --this
> SELECT *
> FROM sysobjects o
> JOIN ::fn_listextendedproperty(
> 'OwnedBy',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> NULL,
> NULL,
> NULL
> ) ep ON o.name = ep.objname
> WHERE o.xtype = 'P' AND
> ep.value = 'MyApplication'

Faster Deletion from a production database

I need to delete several million transactions from a production database. I
have tried the SP below but it takes 4 1/2 hours to delete 5,000
transactions. Can someone please look over this and let me know if there is
someway to speed up this process? I can't truncate the table because I stil
l
need the most recent (6 months) worth of transactions. The remaining
transactions date back to 10/21/2003 and have been backed up and now need to
be deleted.
Thanks in advance,
Matt
CREATE PROC dbo.ArchiveProduction
(
@.CutOffDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete transactions from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
FileContentsID int, FileReceiveID int)
INSERT INTO @.tmp
SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
FileContentsID,
r.[ID] AS FileReceiveID
FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
f.FileReceiveID
LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
WHERE r.FileFindDate < @.CutOffDate
-- Acknowledgments
DELETE FROM dbo.Acknowledgments
WHERE TransactionID IN
(
Select TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
16, 1)
RETURN -1
END
-- OutboundTranSet
DELETE FROM dbo.OutboundTranSet
FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
16, 1)
RETURN -1
END
-- OutboundBatch
DELETE FROM dbo.OutboundBatch
FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
16, 1)
RETURN -1
END
-- OutboundFile
DELETE FROM dbo.OutboundFile
WHERE [ID] IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
1)
RETURN -1
END
-- PayorResponse
DELETE FROM dbo.PayorResponse
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
16, 1)
RETURN -1
END
-- ClaimInfo
DELETE FROM dbo.ClaimInfo
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
RETURN -1
END
-- Transactions
-- AttachmentImages
DELETE FROM dbo.AttachmentImages
FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
16, 1)
RETURN -1
END
-- AttachmentsToClaims
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.AttachmentsToClaims', 16, 1)
RETURN -1
END
-- ClaimsToAttachments
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.ClaimsToAttachments', 16, 1)
RETURN -1
END
-- Transactions
DELETE FROM dbo.Transactions
WHERE [ID] IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
1)
RETURN -1
END
-- Batches
-- Batches
DELETE FROM dbo.Batches
WHERE [ID] IN
(
SELECT BatchID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
RETURN -1
END
-- FileContents
DELETE FROM dbo.FileContents
WHERE [ID] IN
(
SELECT FileContentsID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
1)
RETURN -1
END
-- FileReceive
DELETE FROM dbo.FileReceive
WHERE [ID] IN
(
SELECT FileReceiveID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
GO1) Don't use table variables for large amounts of data (more than say 500
rows), use temporary tables.
2) Use joins or EXISTS, not IN.
3) Create indexes on all columns that you join on, including those in the
temporary tables.
4) By the looks of it you can cut out quite a few joins, specially between
the OutboundFile and Transactions tables.
Jacco Schalkwijk
SQL Server MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>I need to delete several million transactions from a production database.
>I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
> is
> someway to speed up this process? I can't truncate the table because I
> still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
> to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
> 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks, Jacco! I'll give it a shot.
"Jacco Schalkwijk" wrote:

> 1) Don't use table variables for large amounts of data (more than say 500
> rows), use temporary tables.
> 2) Use joins or EXISTS, not IN.
> 3) Create indexes on all columns that you join on, including those in the
> temporary tables.
> 4) By the looks of it you can cut out quite a few joins, specially between
> the OutboundFile and Transactions tables.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>
>|||Depending on how many records are in the tmp Table, Your problem *might* be
because there are no indices on that tmp table. WHich means that each delet
e
must do a complete table scan on that table. SO, If there are a chunk of
records in there, I might suggest using four table variables instead, as
follows:
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchesIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
And then use these tables in all the delete queries instead... This will
allow each delete query to use Indexes on Table variables and may improve
performace substantially..
Also I suggest you put the errhandling at the end, and just "Goto" a named
label... It'll make the SP much cleaner and easier to maintain, as so:
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
/*SELECT t.[ID] as TranID,
b.[ID] BatchID,
t.OutboundFileID,
f.[ID] FileContentsID,
r.[ID] FileReceiveID
FROM FileReceive r
LEFT JOIN FileContents f ON f.FileReceiveID = r.[ID]
LEFT JOIN Batches b ON b.FileContentsID = f.[ID]
LEFT JOIN Transactions t ON t.BatchID = b.[ID]
WHERE r.FileFindDate < @.CutOffDate */
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0) -- This is the end of normal processing
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||oh oh, some extra stuff in there I neglected to delete.. Here's correctted
version...
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0)
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||What is the recovery model on your database? Try setting to simple recovery.
Also, create a loop where you delete only 100,000 records in each iteration
followed by a checkpoint. You will also want to perform a backup just prior
to this.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I
still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks to all of you for your help. With your input the delete now only
takes 2 1/2 minutes to delete 5,000 transactions and all associated items.
"Matt" wrote:

> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I st
ill
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 1
6,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16,
1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages
',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 1
6,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 1
6,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16
, 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>