avoiding accidental deletes...best practises?

Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

also are there any 'best practises' to avoid accidental deletes.

For example Im making a lot of delete statements like:

del from table_name
where item_id = 23

but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

Let me ask this question: would you want a script stopping half-way through a stored procedure ?

To wait for a user dialog box to complete at 4am ?

If you are having problems with accidential deletes, consider investing in Lumigent log explorer (www.lumigent.com) or Red Gates Log Rescue (www.red-gate.com) - these tools will allow you to recover the data you deleted.

Regards,

DB007

|||

use it in a transaction ....

keep autocommit off....and keep ur logs (incase it still happens) to recover the data..

|||

Hi Airwalker,

The thing to remember is accidents will happen, and just to have processes in place to help clear up the mess afterwards.

Even with transactions (if used correctly) once committed the data is still deleted (accidentially, maliciously - by a hacker !).

The options available to you are : Recover directly from the transaction log (as per previous post), Restore a prior database backup and copy the data back into the live system, make the database read-only.

Now option 3 is unlikely as this system requires updates, but suitable for systems that are designed to be read-only.

Option 2 - takes a long time, even with page level restores available in SQL 2005 - and normally a significant amount of disk space (esp. if a copy of the database needs to be taken).

Option 1 - Usually involves the least amount of time, and can be performed offline whilst users are continuing to use the system.

Best Regards,

D

|||thanks for the advice guys...much appreciated.|||

If you are running SQL Server 2005, you might consider using SQL Server 2005 database snapshots. They are great for allowing you to very easily recover data that gets altered or deleted by mistakes like that.

You could have an Agent job that periodically creates a snapshot and gets rid of older snapshots. Then, you can pull data from the most recent snapshot to fix those kind of mistakes. Just be aware that DB snapshots are by no means a replacement for a good backup strategy.

http://glennberrysqlperformance.spaces.live.com

|||

one method i have adoped is .. from my application i can delete (for Eg) only one users roles (1-M) from UserROle table.. So i have created a Trigger which checks whether deleted table contains morethan one user if yes roll back and exist.. So from QA such delete statment can be prevented....

One more method is Just create a trigger in all table which checks Count(*) from deleted table and it is 0 then it means you are deleting all the rows... roll back and raiserror..

All these methods depends... it may or may not valid in all scenario...

try this script and do the necessary modificaiton

drop table test

create table test (col1 int)

insert into test select 1

insert into test select 2

insert into test select 3

insert into test select 4

insert into test select 5

insert into test select 6

insert into test select 1

insert into test select 1

alter trigger testtrig

on test for delete

as

declare @.i int

select @.i=count(*) from test

print @.i

if @.i=0

begin

print 'u r trying to delete blah...blah'

rollback tran

end

delete from test

delete from test where col1=1

select *From test

Madhu

Avoid update without where clause

Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
No, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald
|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sq...y_triggers.htm
but may not be completely safe.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:

> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you want to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened?". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
|||Good answer, i like the sledgehammer example
Thank you

>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>
>.
>
|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald

>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message[vbcol=seagreen]
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void
>
>.
>

Avoid update without where clause

Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =transaction mode. When OFF, it returns the connection to autocommit =transaction mode.
When a connection is in implicit transaction mode and the connection is =not currently in a transaction, executing any of the following =statements starts a transaction:
-- Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> > Using SQL 7.0,
> > Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where > clause ? > > In order to avoid devastating lapse of memory :o\
> > Thanks !
> > Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
> > Hi every one,
> >
> > Using SQL 7.0,
> >
> > Without using transaction, is there a way that SQL void
> > (refuse) any update or delete if there is no where
> > clause ?
> >
> > In order to avoid devastating lapse of memory :o\
> >
> > Thanks !
> >
> > Donald|||I agree with Mark completely
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*)
Then the DELETE statement
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted
Then we use a "-- ROLLBACK COMMIT" as the final line
This is commented out so that you have to DBLCLICK on the keyword that you want to use
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>> Hi every one,
>> Using SQL 7.0,
>> Without using transaction, is there a way that SQL void
>> (refuse) any update or delete if there is no where
>> clause ?
>> In order to avoid devastating lapse of memory :o\
>> Thanks !
>> Donald
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
>> Donald,
>> You could get into the habit of always executing things
on the live
>> servers like this:
>> BEGIN TRAN
>> <your DML code>
>> <some select statements to verify your DML code>
>> When you are satisfied, issue a COMMIT TRAN, or if you
cocked it up,
>> issue a ROLLBACK TRAN. I always do this when making
changes to
>> production, even if they have been scripted and tested.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Donald wrote:
>> > Hi every one,
>> >
>> > Using SQL 7.0,
>> >
>> > Without using transaction, is there a way that SQL
void
>> > (refuse) any update or delete if there is no where
>> > clause ?
>> >
>> > In order to avoid devastating lapse of memory :o\
>> >
>> > Thanks !
>> >
>> > Donald
>
>.
>|||One way I achieved this in the past was to create triggers
on the table(s) in question and make sure the rowcount
wasn't over a certain threshold. Some tables might only
have 1 row updated at a time and never more than that,
others maybe 5 or 10. So I made the trigger check the
rowcount for this.
Van
>--Original Message--
>Hi every one,
>Using SQL 7.0,
>Without using transaction, is there a way that SQL void
>(refuse) any update or delete if there is no where
>clause ?
>In order to avoid devastating lapse of memory :o\
>Thanks !
>Donald
>.
>|||Very good points Steve! I agree with you completely too, and you put it
much better than I did.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> I agree with Mark completely.
> We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
> Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
> Then the DELETE statement.
> Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
> Then we use a "-- ROLLBACK COMMIT" as the final line.
> This is commented out so that you have to DBLCLICK on the keyword that you want to use.
> Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
> We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...

Avoid update without where clause

Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:

> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
>|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN.
Especially when doing the work in PRODUCTION - but regardless - on the TES
T BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "detai
ls" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from t
he first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you w
ant to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor"
joins can increase row presentation, this is extremely important - you might
have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "wha
t just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD fi
le for proof...|||Good answer, i like the sledgehammer example
Thank you

>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1383601c443ff$12727930$a001280a@.phx
.gbl...
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald

>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void[vbcol=seagreen]
>
>.
>

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
--
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?
>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
--
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:
>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in where
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>> Thanks, I found it. my setting is 0 under QueryTime out, it should
>> mean unlimited, right ' But I do get the error message
>> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
>> when deleting a lot of record under EM !! Any idea?
>>
>> >This is a client side configuration issue (ODBC really...)
>> >
>> >from query analyzer... you can control this from tools - options -
>> >connections - query time out
>> --
>> Ginola
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> >This is a client side configuration issue (ODBC really...)
> >
> >from query analyzer... you can control this from tools - options -
> >connections - query time out
> --
> Ginola

avoid Timeout expired?

Hi,
When I execute the "Delete" statement in Query for large database, it
prompts the error
"[Microsoft][ODBC SQL Server Driver]Timeout expired"
I know there is setting to configurate it, but I couldn't find out
which one. can anyone tell me please? thanks
GinolaThis is a client side configuration issue (ODBC really...)
from query analyzer... you can control this from tools - options -
connections - query time out
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403c13f7.8256406@.msnews.microsoft.com...
> Hi,
> When I execute the "Delete" statement in Query for large database, it
> prompts the error
> "[Microsoft][ODBC SQL Server Driver]Timeout expired"
> I know there is setting to configurate it, but I couldn't find out
> which one. can anyone tell me please? thanks
>
> --
> Ginola|||Thanks, I found it. my setting is 0 under QueryTime out, it should
mean unlimited, right ' But I do get the error message
"[Microsoft][ODBC SQL Server Driver]Timeout expired "
when deleting a lot of record under EM !! Any idea?

>This is a client side configuration issue (ODBC really...)
>from query analyzer... you can control this from tools - options -
>connections - query time out
Ginola|||Hi,
"0 is Unlimited".
Try to delete the records from Query Analyzer using Delete statement. Here
delete statement will be faster if you have indexes on columns used in where
clause of delete statement.
Thanks
Hari
MCDBA
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Hi,
I am really really new in SQL server and I haven't had much experience
in using Query Analyzer. Most of my experience is from MS-Access
If I write the following, will it work ?
Use DBName
go
Delete * from [TableName]
go
Why we don't use delete under EM instead ?
On Wed, 25 Feb 2004 13:04:58 +0530, "Hari" <hari_prasad_k@.hotmail.com>
wrote:

>Hi,
>"0 is Unlimited".
>Try to delete the records from Query Analyzer using Delete statement. Here
>delete statement will be faster if you have indexes on columns used in wher
e
>clause of delete statement.
>Thanks
>Hari
>MCDBA
>
>"Ginola" <ginola@.mailcity.om> wrote in message
>news:403d30ed.15670093@.msnews.microsoft.com...
>
Ginola|||Enterprise Manager does not look at the timeout settings used by QA. There
is no way to change the default timeout setting for EM...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ginola" <ginola@.mailcity.om> wrote in message
news:403d30ed.15670093@.msnews.microsoft.com...
> Thanks, I found it. my setting is 0 under QueryTime out, it should
> mean unlimited, right ' But I do get the error message
> "[Microsoft][ODBC SQL Server Driver]Timeout expired "
> when deleting a lot of record under EM !! Any idea?
>
> --
> Ginola|||Try a Truncate table <tablename>. It's the fastest way to delete all records
from a table.

Autonumber...

Sorry for my bad English but I'm Italian...

I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?I guess you are looking for a code to reset the identity seed for a table.
Here you go:

DBCC CHECKIDENT (TableName, RESEED, 1)

Shervin

"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start
with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer
or
> Enterprise Manager...?|||In article <bkp1t5$a7j$1@.grillo.cs.interbusiness.it>,
wdilan_NOSPAM@.tin.it says...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?

I worried about that for a long time, too. It just seemed unnatural to
have a bunch of missing numbers. But after continually resetting them
(there are a couple of ways to do it -- I see someone already is helping
with that) I finally realized that there is no reason to do so.

Think what the numbers will look like after a few weeks of production.

-- Rick|||Since you want to delete all the rows in the table you can use

TRUNCATE TABLE table_name

That will delete all rows AND reset the seed in one command.

- Jason

> "Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
> news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> > Sorry for my bad English but I'm Italian...
> > I have a question for you...
> > I delete all record of table but I don't set autonumber field to start
> with
> > value 1...
> > What do you do to set a values of autonumber colomns with Query Analizer
> or
> > Enterprise Manager...?|||On 25 Sep 2003 10:57:39 -0700 in comp.databases.ms-sqlserver,
JayCallas@.hotmail.com (Jason) wrote:

>Since you want to delete all the rows in the table you can use
>TRUNCATE TABLE table_name
>That will delete all rows AND reset the seed in one command.

Doesn't seem to work if you have foreign key constraints.

--
A)bort, R)etry, I)nfluence with large hammer.

Autonumber...

Sorry for my bad English but I'm Italian...
I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?See my reply in other group. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message news:bkp1m7$kp9$1@.fata.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?
>
>