Better way to do a large delete?

I have to do a delete from a table with about 25 million rows. I need to
delete about 4 million rows, based on the date in another table
Table Session
SessionID SessionDate
1 1/25/2004
2 7/25/2001
I have created a view (SessionView) containing all sessionid's where the
sessiondate less than 3 years old.
(Create view SessionView As Select SessionID From Session Where
Session.sessiondate >= getdate() - 1095)
Table Claims
ClaimID SessionID etc etc
etc
24578 1 15
HealthPractice 5554441234
6548975 2 52 ChirocPract
5551234567
What I need is to delete all rows from Table Claims that have sessionids in
SessionView.
Delete From Claims Where Claims.sessionID In (Select SessionID from
SessionView)
-In this case ClaimID 6548975 would be deleted but ClaimID 24578 would not.
But I must be doing something wrong (I am very new at SQL Server, and have
never done a large scale delete that wasn't just straight forward), because
the delete seems to take forever. Last time I had to stop it at just over 5
hours because the network admin needed to do some work on the server and
reboot it.
I have set the recovery to simple, and the server has no other active
databases or other applications running on it.
Any assistance appreciated.
Nancy Lytle
N_Lytle@.terplaum.umd.eduyou could also copy the rows you want to keep to another table, drop
the orginal one, and rename the copy|||nancy,
try deleting in smaller chunks, say 1000 rows each. this should help.
dean
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>|||Another thing could be to drop all indexes, delete your rows, recreate
the indexes.
Its all about how much you can do with the table. Do you have a
maintenance window where no one are using the database or is it always
online?|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:580414
Nancy Lytle (word_diva@.hotmail.com) writes:
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
>...
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and
> have never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on
> the server and reboot it.
Rather than creating a view, insert all the sessionids to delete into
a temp table.
You could also try this syntax:
DELETE Claims
FROM Claims c
JOIN #temp t ON c.session_id = t.session_id
If Claims does not have its clustered index on SessionId, it could be
worth to do
CREATE CLUSTERED INDEX session_id_ix ON Claims(session_id)
WITH DROP_EXISTING
for this task, and then restore the original index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, I was trying to avoid that because I actually have about 10 tables
that I have to handle in a similiar fashion, and copying was also going
slowly.
I think I will end up using Dean's suggesting and chunking it out.
Thanks,
Nancy
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138312336.363053.109410@.o13g2000cwo.googlegroups.com...
> you could also copy the rows you want to keep to another table, drop
> the orginal one, and rename the copy
>|||I think this is the way I will have to go, so I will just use smaller date
intervals, until I get everything deleted.
Thanks,
Nancy
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:OvslVLsIGHA.2828@.TK2MSFTNGP12.phx.gbl...
> nancy,
> try deleting in smaller chunks, say 1000 rows each. this should help.
> dean
> "Nancy Lytle" <word_diva@.hotmail.com> wrote in message
> news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
>|||When bulk deleting 4 million rows, the slowness you are experiencing
probably has more to do with transaction logging than anything else. I
assume this must a one time purge, but if it is a recurring process, then
you will probably want to reconsider your data model and workflow.
Start by dropping any indexes from the table that would not assist in
qualifying the rows needed for deletion. For example, if you are deleting
based on transaction date, then you would not need to retain an index on
LastName or AccountType. This will reduce the amount of transaction logging
during the process, and besides, all the indexes will need to be
defragmented after the process has completed anyway.
You can try creating a loop and delete the rows based on a date column,
however, this usally results in batches that vary greatly in size. For
example, your company have a lot more transactions in 2005 than in 2004.
Using the technique below, you can delete in batches of exactly 1000 rows.
set rowcount 1000
while
delete from mytable where . . .
if @.@.rowcount = 0 break
checkpoint
end
"Nancy Lytle" <word_diva@.hotmail.com> wrote in message
news:er8ntEsIGHA.2668@.tk2msftngp13.phx.gbl...
> I have to do a delete from a table with about 25 million rows. I need to
> delete about 4 million rows, based on the date in another table
> Table Session
> SessionID SessionDate
> 1 1/25/2004
> 2 7/25/2001
> I have created a view (SessionView) containing all sessionid's where the
> sessiondate less than 3 years old.
> (Create view SessionView As Select SessionID From Session Where
> Session.sessiondate >= getdate() - 1095)
> Table Claims
> ClaimID SessionID etc etc etc
> 24578 1 15 HealthPractice
> 5554441234
> 6548975 2 52
> ChirocPract 5551234567
> What I need is to delete all rows from Table Claims that have sessionids
> in SessionView.
> Delete From Claims Where Claims.sessionID In (Select SessionID from
> SessionView)
> -In this case ClaimID 6548975 would be deleted but ClaimID 24578 would
> not.
> But I must be doing something wrong (I am very new at SQL Server, and have
> never done a large scale delete that wasn't just straight forward),
> because the delete seems to take forever. Last time I had to stop it at
> just over 5 hours because the network admin needed to do some work on the
> server and reboot it.
> I have set the recovery to simple, and the server has no other active
> databases or other applications running on it.
> Any assistance appreciated.
> Nancy Lytle
> N_Lytle@.terplaum.umd.edu
>

Best way to update/insert/delete in sqlserver database

Hello
I've passed the last days going around with xml, sqlxml, uppdategrams,
diffgrams, transactions, sp's, everything, I suppose, but still have
not a clear line of thoughts...
Im new in the info systems world.. I'm currently developing an
windows app in vb.net running over sqlserver 2000. Till now I was
using simple inserts, updates and deletes in db tables (not much so
far) but now that performance and data consistency issues are being
discussed, I was trying to understand the best way to pass data
to/from database.
Clearly I want to have the business logic in server side, being
executed by storedprocedures, but in large updates, inserts and
deletes I must pass many data and that it's a problem.
For an update I was doing something like this:
Try
loTransaction = lcnPlada.BeginTransaction()
lcmActualiza.Transaction = loTransaction
For Each loLinha In tdtActualizaHistorico.Rows
lcmActualiza.CommandText = "insert into
stetiquetahistorico (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
" & _
"values (" & CType(loLinha(0), Integer) &
"," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
Integer) & "," & tnNumeroEmissao + 1 & ")"
lcmActualiza.ExecuteNonQuery()
Next
loTransaction.Commit()
Return True
end try
But have logic (a for each) inside a transaction its not a good idea,
right?
I've seen many different examples of adodb objects constructing a
stream with the xml statements that executes a sp and maps the table
columns to perform updates or inserts and then
adodb.command.commandstream = stream
adodb.command.execute
and in server side creates a sp that
sp_xml_preparedocument
...
insert...
sp_xml_removedocument
Sqlserver books online has an example of it that works correct in
northwind database but what about using sqlcommand? or
sqlxmlcommand?
Can I use a sqlxmlcommand to execute a stream that was written in
code, instead of using templates?
Is there other ways than sqlxml to perform this kind of tasks?
Anybody can help me, please?
Thanks in advance
sp_xml_preparedocument/OpenXML is a T-SQL feature. So it is independent of
what client you are using to connect to the database. Steps are:
1. Write your stored proc to do the logic
2. Use the normal way that your client requires to pass arguments and
execute the stored proc.
You only need the command stream object if you are using FOR XML to get XML
back from the server.
HTH
Michael
"?scar Martins" <subdueme@.hotmail.com> wrote in message
news:229f0a23.0410141120.1efff3b8@.posting.google.c om...
> Hello
> I've passed the last days going around with xml, sqlxml, uppdategrams,
> diffgrams, transactions, sp's, everything, I suppose, but still have
> not a clear line of thoughts...
> Im new in the info systems world.. I'm currently developing an
> windows app in vb.net running over sqlserver 2000. Till now I was
> using simple inserts, updates and deletes in db tables (not much so
> far) but now that performance and data consistency issues are being
> discussed, I was trying to understand the best way to pass data
> to/from database.
> Clearly I want to have the business logic in server side, being
> executed by storedprocedures, but in large updates, inserts and
> deletes I must pass many data and that it's a problem.
> For an update I was doing something like this:
> Try
> loTransaction = lcnPlada.BeginTransaction()
> lcmActualiza.Transaction = loTransaction
> For Each loLinha In tdtActualizaHistorico.Rows
> lcmActualiza.CommandText = "insert into
> stetiquetahistorico
> (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
> " & _
> "values (" & CType(loLinha(0), Integer) &
> "," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
> CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
> Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
> Integer) & "," & tnNumeroEmissao + 1 & ")"
> lcmActualiza.ExecuteNonQuery()
> Next
> loTransaction.Commit()
> Return True
> end try
> But have logic (a for each) inside a transaction its not a good idea,
> right?
>
> I've seen many different examples of adodb objects constructing a
> stream with the xml statements that executes a sp and maps the table
> columns to perform updates or inserts and then
> adodb.command.commandstream = stream
> adodb.command.execute
> and in server side creates a sp that
> sp_xml_preparedocument
> ...
> insert...
> sp_xml_removedocument
> Sqlserver books online has an example of it that works correct in
> northwind database but what about using sqlcommand? or
> sqlxmlcommand?
> Can I use a sqlxmlcommand to execute a stream that was written in
> code, instead of using templates?
> Is there other ways than sqlxml to perform this kind of tasks?
> Anybody can help me, please?
>
> Thanks in advance

Best Way to handle UID to another server

I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".

My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.

However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.

Is even using a Trigger to Stored Proc the best way?

What about Transaction Replication - which I've never attempted - is that a better way?

Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.

Thanks as always for your input,

PeterI would never suggest using triggers that reference objects outside of their own database. A recipe for disaster.
A better strategy would be to have the trigger load your data into a staging table, and then have a job scheduled once per minute to transfer the data from the staging table to the remote server. This is very robust if you can handle latency of up to one minute (scheduled jobs can be set to run once per minute).
If you require zero latency, then I would look into replication as a solution.|||Thanks Blindman:

I did some BOL checking and replication sounds good, but the table the data needs to go into has different field names and is in use in another application. The target DB happens to be on the same server.

What about have a trigger call a stored procedure and pass the fields in question along with the key to the SP?

The SP would make the connection to the other DB and do the UID.

You remain helpful,

Peter|||I still don't like it. What you are trying to avoid is having your trigger fail and system "A" come to a crashing halt when system "B" goes down or the connection fails. Your solution needs to be strong enough to fail safely, or you are just doubling or trippling the possible points of failure.
The advantage of the staging tables is that, if the connection to system "B" is lost then system "A" continues merrily on, loading data into the staging tables. When system "B" comes back online the data in the staging tables is automatically transferred.
No downtown for system "A".
No data-loss for system "B".
No late-night hours getting both systems back in synch for "DBA".
If you can stand up to a 1 minute latency, this is the way to go.
If you can't handle the latency, then replicate to duplicate tables in system "B" and put the triggers on THEM.|||Blindman:

I can stand a latency of even up to 5 minutes.

My concerns:

1. The table I'm feeding updates to has a different name, naming scheme and field types. I'll need to manipulate the data I'm "pushing" to Server B regardless of the approach I take.
2. With #1, I didn't know if Replication can handle. Perhaps "Transactional Replication" isn't the proper type of replication for this
3. I'm totally new to Replication and it doesn't look trivial via BOL.

Any additional help here is appreciated.

I'm event considering having the application update the server B tables directly. I don't like that for a number of reasons including if someone updates the back-end directly (unlikely - but possible), it wouldn't be updating server B.

Peter|||The staging table method should handle all your concerns. I would recommend transforming the data when it is shuttled from the staging tables to the destination database, rather than when it is inserted into the staging tables, as you want your triggers to be as streamlined as possible.

Best way to DTS

Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Best way to DTS

Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.
If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegrou ps.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>
|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?
|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Best way to DTS

Does anyone know of a better way I could go about doing running a DTS
package where I basically delete all the data from the SQL server and
then move (re-populate) it with data from our Navision (ERP) database?
Right now I have connections which connect to each other through
connections. The first deletes the data from the tables, the next 40 or
so move the data from our ERP database back to the SQL server. The
reason we do so is because we cannot figure out a way how to move only
the data that has changed in the ERP database to to the SQL server, so
instead we just erase the SQL server every night and copy all the data
from the ERP database into it so we know we are getting current data
once a day. The main reason I need help is because this process is
taking 5+ hours now and has grown with the amount of data we have input
over time. I would really appreciate ANY input on my situation, it
would help tremendously.If you don't know how to identify changed data only, maybe SQL Server can
help you - do please check basic Replication topics in Books OnLine if this
is what you need. Also, as a quick solution - backup of the database on
production server and restore on standby server should be much faster than
deleting and inserting all of the data.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
<barhoc11@.yahoo.com> wrote in message
news:1124116978.660087.4840@.g47g2000cwa.googlegroups.com...
> Does anyone know of a better way I could go about doing running a DTS
> package where I basically delete all the data from the SQL server and
> then move (re-populate) it with data from our Navision (ERP) database?
> Right now I have connections which connect to each other through
> connections. The first deletes the data from the tables, the next 40 or
> so move the data from our ERP database back to the SQL server. The
> reason we do so is because we cannot figure out a way how to move only
> the data that has changed in the ERP database to to the SQL server, so
> instead we just erase the SQL server every night and copy all the data
> from the ERP database into it so we know we are getting current data
> once a day. The main reason I need help is because this process is
> taking 5+ hours now and has grown with the amount of data we have input
> over time. I would really appreciate ANY input on my situation, it
> would help tremendously.
>|||Thanks so much Dejan for the quick response I just have a couple
questions for you or anyone who has an answer, if you have the time to
answer I would greatful.
Is this possible to replicate a Navision(ERP) database to a SQL server?
Can you explain the quick solution you mentioned...- would this include
dumping the data to a temporary database then inserting it in the SQL
server database?|||> Is this possible to replicate a Navision(ERP) database to a SQL server?
Uh-uh...
AFAIK Navision has two possibilities: to use it's native db or to use SQL
Server as RDBMS. I guessed you have SQL Server version of Navision. This way
replication would be quite simple. But from your answer I infer you use
native Navision db. If this is true, then you would have to write custom
Publisher in order to use the replication, and I guess this is not an option
for you. Also, it is not possible to restore backup of Navision native db to
SQL Server. So I guess your best option is to find which rows have changed
and create more efficient DTS packages. Maybe you can ask in some Navision
group how to find only changed rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

best way to delete the reporting services jobs in sqlserver

I have six thousand reporting services jobs in sqlserver, which were
scheduled to run only once and their execution date is older than a month.
how do I know which of the jobs are useful out of these 6000.
Can I write a job, to purge the reporting services jobs older than a month
and schedule it to run every week, basically how do I manage these ever
growing jobs ?
Thanks in advance..GAis it okay to delete the subscriptions like below '
delete from reportserver..subscriptions
WHERE ModifiedDate < dateadd(day, -7, getdate()) AND Description LIKE 'SRS%'
TIA
"DallasBlue" wrote:
> I have six thousand reporting services jobs in sqlserver, which were
> scheduled to run only once and their execution date is older than a month.
> how do I know which of the jobs are useful out of these 6000.
> Can I write a job, to purge the reporting services jobs older than a month
> and schedule it to run every week, basically how do I manage these ever
> growing jobs ?
>
> Thanks in advance..GA
>|||I have deleted subscriptions using this method. There are cascading deletes
when to remove the related records in ReportSchedule and Schedule tables.
Though, bulk deleting a lot of subscriptions at the same time might not work
with that statement. I know for me I've gotten timeouts when trying a direct
delete. So instead, I create a memory table (DECLARE @.x TABLE...) and put in
all the SubscriptionID for the ones I want to delete. Then I delete from the
Subscription table using a join to the memory table. I know it might seem
strange to do it that way, but because of the way the tables are indexed, it
works.
Once you the records are deleted from the tables, the Reporting Service will
automatically delete the actual SQL Agent jobs when it does its maintenance,
I'm not sure of the frequency that it does that, but at minimum its once a
day.
Carmine
"DallasBlue" wrote:
> is it okay to delete the subscriptions like below '
> delete from reportserver..subscriptions
> WHERE ModifiedDate < dateadd(day, -7, getdate()) AND Description LIKE 'SRS%'
> TIA
> "DallasBlue" wrote:
> > I have six thousand reporting services jobs in sqlserver, which were
> > scheduled to run only once and their execution date is older than a month.
> > how do I know which of the jobs are useful out of these 6000.
> > Can I write a job, to purge the reporting services jobs older than a month
> > and schedule it to run every week, basically how do I manage these ever
> > growing jobs ?
> >
> >
> > Thanks in advance..GA
> >
> >

Best Way to delete a parent record

whats the best way to delete a parent record?

for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?

my code below:

The T-SQL Stored Procedure

ALTER PROCEDURE dbo.DeleteInventory(@.InventoryIDint)ASDELETE FROM BookingWHERE InventoryID = @.InventoryIDDELETE FROM InventoryWHERE InventoryID = @.InventoryID

The code in the Code Behind to execute when a button(delete) click

Try Dim tempAs Integer = SqlDataSource4.DeleteIf temp > 0Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp &" Records/Rows Deleted."Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text ="No records Deleted!"End If Catch exAs Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text ="An Error Occured.<br />" & ex.Message.ToStringEnd Try

any better methods anyone wants to share?

Assuming that it makes business sense to delete an inventory record and orders for that item, your stored procedure would be better as

ALTER PROCEDURE dbo.DeleteInventory
(
@.InventoryID int,
@.Count INT OUTPUT -- Return the number of Inventory records to delete as an output parameter
)
AS
SET NOCOUNT ON
SELECT @.COUNT = COUNT(*) FROM Booking WHERE InventoryID = @.InventoryID
BEGIN TRANSACTION
DELETE FROM Booking WHERE InventoryID = @.InventoryID
DELETE FROM Inventory WHERE InventoryID = @.InventoryID
COMMIT

|||

i used "

Dim

"tempAs Integer = SqlDataSource4.Delete"

to get the rows affected, isn't it about the same? by the way with ur method, its simpler but
i seem to have problems. how do i "link" it to a variable? i know how to link it to a Control, etc but how to a variable?