When developing a system one usually has to enter test data into the
database, and usually you have to start out with doing it manually. At least,
I always find myself in that situation having developed an empty database
which needs to be filled with test data for the application development
process to go smoothly.
A problem with relation databases when entering data manually is
dependencies. I am well aware of not having to enforce key dependencies and I
know they can be removed (and then yet again added) to table relations. But I
wish to do things as quickly and smoothly as possible.
I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
2005. One problem when manually entering data seems to stick and never go
away, and it is quite frustrating. When having created a database structure
(or, perhaps someone else has!) I start entering data. Sometimes you are too
quick and start entering data in tables in the wrong order so that you end up
with a record half filled in with one or a few mandatory dependency keys to
other [empty] tables. And here's the problem.
It is a catch 22 situation since I cannot leave the field blank (dependency
key required!) and I cannot enter a value. And the problem is SQL Server
requires one of the two solutions - it is not possible to delete the record
altogether and start anew. Actually, it isn't even possible to close the SQL
Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
ILLEGAL.
In this situation there is only two things you can do: either you force the
SQL Server management studio to shut down using the task manager, or you
start adding records to the dependency table(s). The former is not a good
solution, and the latter isn't necessarily feasible (some complex systems
have too large data hierarchies to make it workable).
I was hoping this issue would be resolved for the 2005 version, but it
isn't. There has to be a way to delete a memory-based not completed record
without the whole SQL Server crashing. And since identity seed and default
value columns aren't filled in immediately upon finishing the record (they
were in 7.0 and 2000!) my guess is that the record isn't physically saved
until later. So why create this stupid catch 22 situation? It is very, very
frustrating!Per
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
I'm little bit confused with your arguments. If you know the database's
structure , it is easy to fill a 'parent' table first and then all child
tables in order to get it filled in the right way. Have you created a
diagram of the databse to see what is a relationship between tables, and if
you define a column does not accept NULL , you must provide a value
,otherwise allow NULLs to the column
I have just starting a new application which works against a database and
need to insert sample data , so first I have all doucumenation about the
database ,
a diagram, dependencies , data intgerity and etc.... so i 've just easily
filled the data
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!
I try to avoid entering the data directly from SSMS , whu not ctreate a
script that does inserting?
"Per Bylund" <Per Bylund@.discussions.microsoft.com> wrote in message
news:8BB178FE-1C18-4B3E-8F70-38D96016A79E@.microsoft.com...
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At
> least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies
> and I
> know they can be removed (and then yet again added) to table relations.
> But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank
> (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the
> record
> altogether and start anew. Actually, it isn't even possible to close the
> SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force
> the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!|||"Uri Dimant" wrote:
> I'm little bit confused with your arguments. If you know the database's
> structure , it is easy to fill a 'parent' table first and then all child
> tables in order to get it filled in the right way. Have you created a
> diagram of the databse to see what is a relationship between tables, and if
> you define a column does not accept NULL , you must provide a value
> ,otherwise allow NULLs to the column
> I have just starting a new application which works against a database and
> need to insert sample data , so first I have all doucumenation about the
> database , a diagram, dependencies , data intgerity and etc... so i've just
> easily filled the data
There is nothing to be confused about. I'm working in large projects with
many developers developing different parts of the application at the same
time - they all need sample data to develop and test their components or
functions. Even though I do use diagrams (and I do print them) this
limitation in SQL Server is quite frustrating - there are always people
getting stuck in the catch 22 having to forcefully shut down their SQL Server
client.
Imagine a project with 20 or 30 developers (or more) who start working in
the morning and some of them (perhaps 50%) start entering sample data. Quite
a few of them will get stuck with the stupid "you have to enter data but all
data entered is illegal" catch 22.
Of course, if everybody was well structured and thought through everything
first and started doing only after thinking it through - then we would have
no problem. But you cannot develop a software requiring the user to use it in
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometimes
in a rush myself and get caught by SQL Server's "catch 22 feature."
It should be fairly easy supporting a delete function for non-finished
records with dependencies. There is already such a function, but it doesn't
work (you cannot use it because SQL Server will not allow you to delete a
half finished record!) - so it must be a bug.
I believe I am a pretty good developer and I have quite a long experience
from using SQL Server's last four major versions. Even though I should avoid
this catch 22, I still get caught sometimes (but not too often). It is
something Microsoft should look into and fix.|||Per
Well , MS should perfom some internal work to make sure that the data you
entered in the right way, so why do we have CONSTRAINTS and many others
thing to pervent violation of the data integrity. Do you send a request to
MS?
So ,in your case I had also a project as you desribed, we just filled a
"central" database with sample data and then every developer had just
restored the database on his workstation, I see what is you point , so what
will be happened if the developer would want to add/remove /alter data? Well
, one thing I can say IT IS BY DESIGN
"Per Bylund" <PerBylund@.discussions.microsoft.com> wrote in message
news:3E4A1D5B-A970-4ACA-AE13-2CA14ECEAD9C@.microsoft.com...
> "Uri Dimant" wrote:
>> I'm little bit confused with your arguments. If you know the database's
>> structure , it is easy to fill a 'parent' table first and then all child
>> tables in order to get it filled in the right way. Have you created a
>> diagram of the databse to see what is a relationship between tables, and
>> if
>> you define a column does not accept NULL , you must provide a value
>> ,otherwise allow NULLs to the column
>> I have just starting a new application which works against a database
>> and
>> need to insert sample data , so first I have all doucumenation about the
>> database , a diagram, dependencies , data intgerity and etc... so i've
>> just
>> easily filled the data
> There is nothing to be confused about. I'm working in large projects with
> many developers developing different parts of the application at the same
> time - they all need sample data to develop and test their components or
> functions. Even though I do use diagrams (and I do print them) this
> limitation in SQL Server is quite frustrating - there are always people
> getting stuck in the catch 22 having to forcefully shut down their SQL
> Server
> client.
> Imagine a project with 20 or 30 developers (or more) who start working in
> the morning and some of them (perhaps 50%) start entering sample data.
> Quite
> a few of them will get stuck with the stupid "you have to enter data but
> all
> data entered is illegal" catch 22.
> Of course, if everybody was well structured and thought through everything
> first and started doing only after thinking it through - then we would
> have
> no problem. But you cannot develop a software requiring the user to use it
> in
> only one way. There will always be people stressed out forgetting to check
> dependencies (or perhaps even not understanding dependencies). I am
> sometimes
> in a rush myself and get caught by SQL Server's "catch 22 feature."
> It should be fairly easy supporting a delete function for non-finished
> records with dependencies. There is already such a function, but it
> doesn't
> work (you cannot use it because SQL Server will not allow you to delete a
> half finished record!) - so it must be a bug.
> I believe I am a pretty good developer and I have quite a long experience
> from using SQL Server's last four major versions. Even though I should
> avoid
> this catch 22, I still get caught sometimes (but not too often). It is
> something Microsoft should look into and fix.
>|||Per Bylund wrote:
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies and I
> know they can be removed (and then yet again added) to table relations. But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are too
> quick and start entering data in tables in the wrong order so that you end up
> with a record half filled in with one or a few mandatory dependency keys to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the record
> altogether and start anew. Actually, it isn't even possible to close the SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very, very
> frustrating!
Perhaps you should stop trying to use Management Studio/Enterprise
Manager to enter data into your tables as if they were spreadsheets, and
instead script your INSERT statements. Creating your test data in this
fashion, with scripts, will prevent you from getting caught up in the
GUI's error trapping, and will also give you the ability of re-using the
test data for future development.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 4 Jul 2006 02:14:02 -0700, Per Bylund <Per
Bylund@.discussions.microsoft.com> wrote:
(snip)
>It is a catch 22 situation since I cannot leave the field blank (dependency
>key required!) and I cannot enter a value. And the problem is SQL Server
>requires one of the two solutions - it is not possible to delete the record
>altogether and start anew.
Hi Per,
The reason that you can't delete the row, is that it's not inserted into
the database yet. So instead of trying to delete the row, why don't yu
just cancel the (at that moment still unfinished) attempt to insert?
Hit ESC. This should remove the new roow input. (Unless yoou have just
change the value in one field - in that case, the first ESC only undoes
the change to that field and aborting the complete row requires a second
ESC).
--
Hugo Kornelis, SQL Server MVP
Showing posts with label manually. Show all posts
Showing posts with label manually. Show all posts
Sunday, March 25, 2012
Deleting Manual Data Entries
When developing a system one usually has to enter test data into the
database, and usually you have to start out with doing it manually. At least
,
I always find myself in that situation having developed an empty database
which needs to be filled with test data for the application development
process to go smoothly.
A problem with relation databases when entering data manually is
dependencies. I am well aware of not having to enforce key dependencies and
I
know they can be removed (and then yet again added) to table relations. But
I
wish to do things as quickly and smoothly as possible.
I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
2005. One problem when manually entering data seems to stick and never go
away, and it is quite frustrating. When having created a database structure
(or, perhaps someone else has!) I start entering data. Sometimes you are too
quick and start entering data in tables in the wrong order so that you end u
p
with a record half filled in with one or a few mandatory dependency keys to
other [empty] tables. And here's the problem.
It is a catch 22 situation since I cannot leave the field blank (dependency
key required!) and I cannot enter a value. And the problem is SQL Server
requires one of the two solutions - it is not possible to delete the record
altogether and start anew. Actually, it isn't even possible to close the SQL
Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
ILLEGAL.
In this situation there is only two things you can do: either you force the
SQL Server management studio to shut down using the task manager, or you
start adding records to the dependency table(s). The former is not a good
solution, and the latter isn't necessarily feasible (some complex systems
have too large data hierarchies to make it workable).
I was hoping this issue would be resolved for the 2005 version, but it
isn't. There has to be a way to delete a memory-based not completed record
without the whole SQL Server crashing. And since identity seed and default
value columns aren't filled in immediately upon finishing the record (they
were in 7.0 and 2000!) my guess is that the record isn't physically saved
until later. So why create this stupid catch 22 situation? It is very, very
frustrating!Per
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
I'm little bit confused with your arguments. If you know the database's
structure , it is easy to fill a 'parent' table first and then all child
tables in order to get it filled in the right way. Have you created a
diagram of the databse to see what is a relationship between tables, and if
you define a column does not accept NULL , you must provide a value
,otherwise allow NULLs to the column
I have just starting a new application which works against a database and
need to insert sample data , so first I have all doucumenation about the
database ,
a diagram, dependencies , data intgerity and etc.... so i 've just easily
filled the data
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!
I try to avoid entering the data directly from SSMS , whu not ctreate a
script that does inserting?
"Per Bylund" <Per Bylund@.discussions.microsoft.com> wrote in message
news:8BB178FE-1C18-4B3E-8F70-38D96016A79E@.microsoft.com...
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At
> least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies
> and I
> know they can be removed (and then yet again added) to table relations.
> But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank
> (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the
> record
> altogether and start anew. Actually, it isn't even possible to close the
> SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force
> the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!|||"Uri Dimant" wrote:
> I'm little bit confused with your arguments. If you know the database's
> structure , it is easy to fill a 'parent' table first and then all child
> tables in order to get it filled in the right way. Have you created a
> diagram of the databse to see what is a relationship between tables, and i
f
> you define a column does not accept NULL , you must provide a value
> ,otherwise allow NULLs to the column
> I have just starting a new application which works against a database and
> need to insert sample data , so first I have all doucumenation about the
> database , a diagram, dependencies , data intgerity and etc... so i've ju
st
> easily filled the data
There is nothing to be confused about. I'm working in large projects with
many developers developing different parts of the application at the same
time - they all need sample data to develop and test their components or
functions. Even though I do use diagrams (and I do print them) this
limitation in SQL Server is quite frustrating - there are always people
getting stuck in the catch 22 having to forcefully shut down their SQL Serve
r
client.
Imagine a project with 20 or 30 developers (or more) who start working in
the morning and some of them (perhaps 50%) start entering sample data. Quite
a few of them will get stuck with the stupid "you have to enter data but all
data entered is illegal" catch 22.
Of course, if everybody was well structured and thought through everything
first and started doing only after thinking it through - then we would have
no problem. But you cannot develop a software requiring the user to use it i
n
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometime
s
in a rush myself and get caught by SQL Server's "catch 22 feature."
It should be fairly easy supporting a delete function for non-finished
records with dependencies. There is already such a function, but it doesn't
work (you cannot use it because SQL Server will not allow you to delete a
half finished record!) - so it must be a bug.
I believe I am a pretty good developer and I have quite a long experience
from using SQL Server's last four major versions. Even though I should avoid
this catch 22, I still get caught sometimes (but not too often). It is
something Microsoft should look into and fix.|||Per
Well , MS should perfom some internal work to make sure that the data you
entered in the right way, so why do we have CONSTRAINTS and many others
thing to pervent violation of the data integrity. Do you send a request to
MS?
So ,in your case I had also a project as you desribed, we just filled a
"central" database with sample data and then every developer had just
restored the database on his workstation, I see what is you point , so what
will be happened if the developer would want to add/remove /alter data? Well
, one thing I can say IT IS BY DESIGN
"Per Bylund" <PerBylund@.discussions.microsoft.com> wrote in message
news:3E4A1D5B-A970-4ACA-AE13-2CA14ECEAD9C@.microsoft.com...
> "Uri Dimant" wrote:
> There is nothing to be confused about. I'm working in large projects with
> many developers developing different parts of the application at the same
> time - they all need sample data to develop and test their components or
> functions. Even though I do use diagrams (and I do print them) this
> limitation in SQL Server is quite frustrating - there are always people
> getting stuck in the catch 22 having to forcefully shut down their SQL
> Server
> client.
> Imagine a project with 20 or 30 developers (or more) who start working in
> the morning and some of them (perhaps 50%) start entering sample data.
> Quite
> a few of them will get stuck with the stupid "you have to enter data but
> all
> data entered is illegal" catch 22.
> Of course, if everybody was well structured and thought through everything
> first and started doing only after thinking it through - then we would
> have
> no problem. But you cannot develop a software requiring the user to use it
> in
> only one way. There will always be people stressed out forgetting to check
> dependencies (or perhaps even not understanding dependencies). I am
> sometimes
> in a rush myself and get caught by SQL Server's "catch 22 feature."
> It should be fairly easy supporting a delete function for non-finished
> records with dependencies. There is already such a function, but it
> doesn't
> work (you cannot use it because SQL Server will not allow you to delete a
> half finished record!) - so it must be a bug.
> I believe I am a pretty good developer and I have quite a long experience
> from using SQL Server's last four major versions. Even though I should
> avoid
> this catch 22, I still get caught sometimes (but not too often). It is
> something Microsoft should look into and fix.
>|||Per Bylund wrote:
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At lea
st,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies an
d I
> know they can be removed (and then yet again added) to table relations. Bu
t I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database structur
e
> (or, perhaps someone else has!) I start entering data. Sometimes you are t
oo
> quick and start entering data in tables in the wrong order so that you end
up
> with a record half filled in with one or a few mandatory dependency keys t
o
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependenc
y
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the recor
d
> altogether and start anew. Actually, it isn't even possible to close the S
QL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force th
e
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very, ver
y
> frustrating!
Perhaps you should stop trying to use Management Studio/Enterprise
Manager to enter data into your tables as if they were spreadsheets, and
instead script your INSERT statements. Creating your test data in this
fashion, with scripts, will prevent you from getting caught up in the
GUI's error trapping, and will also give you the ability of re-using the
test data for future development.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 4 Jul 2006 02:14:02 -0700, Per Bylund <Per
Bylund@.discussions.microsoft.com> wrote:
(snip)
>It is a catch 22 situation since I cannot leave the field blank (dependency
>key required!) and I cannot enter a value. And the problem is SQL Server
>requires one of the two solutions - it is not possible to delete the record
>altogether and start anew.
Hi Per,
The reason that you can't delete the row, is that it's not inserted into
the database yet. So instead of trying to delete the row, why don't yu
just cancel the (at that moment still unfinished) attempt to insert?
Hit ESC. This should remove the new roow input. (Unless yoou have just
change the value in one field - in that case, the first ESC only undoes
the change to that field and aborting the complete row requires a second
ESC).
Hugo Kornelis, SQL Server MVP
database, and usually you have to start out with doing it manually. At least
,
I always find myself in that situation having developed an empty database
which needs to be filled with test data for the application development
process to go smoothly.
A problem with relation databases when entering data manually is
dependencies. I am well aware of not having to enforce key dependencies and
I
know they can be removed (and then yet again added) to table relations. But
I
wish to do things as quickly and smoothly as possible.
I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
2005. One problem when manually entering data seems to stick and never go
away, and it is quite frustrating. When having created a database structure
(or, perhaps someone else has!) I start entering data. Sometimes you are too
quick and start entering data in tables in the wrong order so that you end u
p
with a record half filled in with one or a few mandatory dependency keys to
other [empty] tables. And here's the problem.
It is a catch 22 situation since I cannot leave the field blank (dependency
key required!) and I cannot enter a value. And the problem is SQL Server
requires one of the two solutions - it is not possible to delete the record
altogether and start anew. Actually, it isn't even possible to close the SQL
Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
ILLEGAL.
In this situation there is only two things you can do: either you force the
SQL Server management studio to shut down using the task manager, or you
start adding records to the dependency table(s). The former is not a good
solution, and the latter isn't necessarily feasible (some complex systems
have too large data hierarchies to make it workable).
I was hoping this issue would be resolved for the 2005 version, but it
isn't. There has to be a way to delete a memory-based not completed record
without the whole SQL Server crashing. And since identity seed and default
value columns aren't filled in immediately upon finishing the record (they
were in 7.0 and 2000!) my guess is that the record isn't physically saved
until later. So why create this stupid catch 22 situation? It is very, very
frustrating!Per
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
I'm little bit confused with your arguments. If you know the database's
structure , it is easy to fill a 'parent' table first and then all child
tables in order to get it filled in the right way. Have you created a
diagram of the databse to see what is a relationship between tables, and if
you define a column does not accept NULL , you must provide a value
,otherwise allow NULLs to the column
I have just starting a new application which works against a database and
need to insert sample data , so first I have all doucumenation about the
database ,
a diagram, dependencies , data intgerity and etc.... so i 've just easily
filled the data
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!
I try to avoid entering the data directly from SSMS , whu not ctreate a
script that does inserting?
"Per Bylund" <Per Bylund@.discussions.microsoft.com> wrote in message
news:8BB178FE-1C18-4B3E-8F70-38D96016A79E@.microsoft.com...
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At
> least,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies
> and I
> know they can be removed (and then yet again added) to table relations.
> But I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database
> structure
> (or, perhaps someone else has!) I start entering data. Sometimes you are
> too
> quick and start entering data in tables in the wrong order so that you end
> up
> with a record half filled in with one or a few mandatory dependency keys
> to
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank
> (dependency
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the
> record
> altogether and start anew. Actually, it isn't even possible to close the
> SQL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force
> the
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very,
> very
> frustrating!|||"Uri Dimant" wrote:
> I'm little bit confused with your arguments. If you know the database's
> structure , it is easy to fill a 'parent' table first and then all child
> tables in order to get it filled in the right way. Have you created a
> diagram of the databse to see what is a relationship between tables, and i
f
> you define a column does not accept NULL , you must provide a value
> ,otherwise allow NULLs to the column
> I have just starting a new application which works against a database and
> need to insert sample data , so first I have all doucumenation about the
> database , a diagram, dependencies , data intgerity and etc... so i've ju
st
> easily filled the data
There is nothing to be confused about. I'm working in large projects with
many developers developing different parts of the application at the same
time - they all need sample data to develop and test their components or
functions. Even though I do use diagrams (and I do print them) this
limitation in SQL Server is quite frustrating - there are always people
getting stuck in the catch 22 having to forcefully shut down their SQL Serve
r
client.
Imagine a project with 20 or 30 developers (or more) who start working in
the morning and some of them (perhaps 50%) start entering sample data. Quite
a few of them will get stuck with the stupid "you have to enter data but all
data entered is illegal" catch 22.
Of course, if everybody was well structured and thought through everything
first and started doing only after thinking it through - then we would have
no problem. But you cannot develop a software requiring the user to use it i
n
only one way. There will always be people stressed out forgetting to check
dependencies (or perhaps even not understanding dependencies). I am sometime
s
in a rush myself and get caught by SQL Server's "catch 22 feature."
It should be fairly easy supporting a delete function for non-finished
records with dependencies. There is already such a function, but it doesn't
work (you cannot use it because SQL Server will not allow you to delete a
half finished record!) - so it must be a bug.
I believe I am a pretty good developer and I have quite a long experience
from using SQL Server's last four major versions. Even though I should avoid
this catch 22, I still get caught sometimes (but not too often). It is
something Microsoft should look into and fix.|||Per
Well , MS should perfom some internal work to make sure that the data you
entered in the right way, so why do we have CONSTRAINTS and many others
thing to pervent violation of the data integrity. Do you send a request to
MS?
So ,in your case I had also a project as you desribed, we just filled a
"central" database with sample data and then every developer had just
restored the database on his workstation, I see what is you point , so what
will be happened if the developer would want to add/remove /alter data? Well
, one thing I can say IT IS BY DESIGN
"Per Bylund" <PerBylund@.discussions.microsoft.com> wrote in message
news:3E4A1D5B-A970-4ACA-AE13-2CA14ECEAD9C@.microsoft.com...
> "Uri Dimant" wrote:
> There is nothing to be confused about. I'm working in large projects with
> many developers developing different parts of the application at the same
> time - they all need sample data to develop and test their components or
> functions. Even though I do use diagrams (and I do print them) this
> limitation in SQL Server is quite frustrating - there are always people
> getting stuck in the catch 22 having to forcefully shut down their SQL
> Server
> client.
> Imagine a project with 20 or 30 developers (or more) who start working in
> the morning and some of them (perhaps 50%) start entering sample data.
> Quite
> a few of them will get stuck with the stupid "you have to enter data but
> all
> data entered is illegal" catch 22.
> Of course, if everybody was well structured and thought through everything
> first and started doing only after thinking it through - then we would
> have
> no problem. But you cannot develop a software requiring the user to use it
> in
> only one way. There will always be people stressed out forgetting to check
> dependencies (or perhaps even not understanding dependencies). I am
> sometimes
> in a rush myself and get caught by SQL Server's "catch 22 feature."
> It should be fairly easy supporting a delete function for non-finished
> records with dependencies. There is already such a function, but it
> doesn't
> work (you cannot use it because SQL Server will not allow you to delete a
> half finished record!) - so it must be a bug.
> I believe I am a pretty good developer and I have quite a long experience
> from using SQL Server's last four major versions. Even though I should
> avoid
> this catch 22, I still get caught sometimes (but not too often). It is
> something Microsoft should look into and fix.
>|||Per Bylund wrote:
> When developing a system one usually has to enter test data into the
> database, and usually you have to start out with doing it manually. At lea
st,
> I always find myself in that situation having developed an empty database
> which needs to be filled with test data for the application development
> process to go smoothly.
> A problem with relation databases when entering data manually is
> dependencies. I am well aware of not having to enforce key dependencies an
d I
> know they can be removed (and then yet again added) to table relations. Bu
t I
> wish to do things as quickly and smoothly as possible.
> I started out with SQL Server 6.5 and have worked with 7.0, 2000 and now
> 2005. One problem when manually entering data seems to stick and never go
> away, and it is quite frustrating. When having created a database structur
e
> (or, perhaps someone else has!) I start entering data. Sometimes you are t
oo
> quick and start entering data in tables in the wrong order so that you end
up
> with a record half filled in with one or a few mandatory dependency keys t
o
> other [empty] tables. And here's the problem.
> It is a catch 22 situation since I cannot leave the field blank (dependenc
y
> key required!) and I cannot enter a value. And the problem is SQL Server
> requires one of the two solutions - it is not possible to delete the recor
d
> altogether and start anew. Actually, it isn't even possible to close the S
QL
> Server management studio - you HAVE TO enter a value yet ALL VALUES ARE
> ILLEGAL.
> In this situation there is only two things you can do: either you force th
e
> SQL Server management studio to shut down using the task manager, or you
> start adding records to the dependency table(s). The former is not a good
> solution, and the latter isn't necessarily feasible (some complex systems
> have too large data hierarchies to make it workable).
> I was hoping this issue would be resolved for the 2005 version, but it
> isn't. There has to be a way to delete a memory-based not completed record
> without the whole SQL Server crashing. And since identity seed and default
> value columns aren't filled in immediately upon finishing the record (they
> were in 7.0 and 2000!) my guess is that the record isn't physically saved
> until later. So why create this stupid catch 22 situation? It is very, ver
y
> frustrating!
Perhaps you should stop trying to use Management Studio/Enterprise
Manager to enter data into your tables as if they were spreadsheets, and
instead script your INSERT statements. Creating your test data in this
fashion, with scripts, will prevent you from getting caught up in the
GUI's error trapping, and will also give you the ability of re-using the
test data for future development.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 4 Jul 2006 02:14:02 -0700, Per Bylund <Per
Bylund@.discussions.microsoft.com> wrote:
(snip)
>It is a catch 22 situation since I cannot leave the field blank (dependency
>key required!) and I cannot enter a value. And the problem is SQL Server
>requires one of the two solutions - it is not possible to delete the record
>altogether and start anew.
Hi Per,
The reason that you can't delete the row, is that it's not inserted into
the database yet. So instead of trying to delete the row, why don't yu
just cancel the (at that moment still unfinished) attempt to insert?
Hit ESC. This should remove the new roow input. (Unless yoou have just
change the value in one field - in that case, the first ESC only undoes
the change to that field and aborting the complete row requires a second
ESC).
Hugo Kornelis, SQL Server MVP
Monday, March 19, 2012
deleting a sql backup file
My backups job are failing at the delete old backup step
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa|||Thank you so much Carlos. The "verify integrity of backup
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because
sql server was still
>verifying the backup file. When you use database
maintenance plans, one of
>the options is to verify the file after the backup is
taken. If this is not
>the case you're going to have to find a windows utility
to find which
>windows process has the file locked, i think i found one
of these utilities
>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> My backups job are failing at the delete old backup step
>> because of not being to delete a backup file. When I
try
>> to manually delete this file I get the sharing violation
>> error message - that someone or something process may be
>> using the file. I know for a fact that it is not
someone
>> and I need to delete this file. I take ownership of the
>> file with full control permissions but still can't
delete
>> this file. I hate to bounce a server just to be able to
>> delete a file. Does anyone know what I can do to delete
>> this file? Is there a way to have sql server or agent
>> take control of the files I want the jobs to delete? I
>> appreciate your help. Thanks
>> Lisa
>
>.
>|||That option is IMO not that useful. To check out what it does, read about
the VERIFYONLY option to the RESTORE in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> >--Original Message--
> >Lisa,
> >This happened to me once a while ago and it was because
> sql server was still
> >verifying the backup file. When you use database
> maintenance plans, one of
> >the options is to verify the file after the backup is
> taken. If this is not
> >the case you're going to have to find a windows utility
> to find which
> >windows process has the file locked, i think i found one
> of these utilities
> >at www.sysinternals.com a while ago, but i'm not sure.
> >
> >--
> >Carlos E. Rojas
> >SQL Server MVP
> >Co-Author SQL Server 2000 Programming by Example
> >
> >
> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> >> My backups job are failing at the delete old backup step
> >> because of not being to delete a backup file. When I
> try
> >> to manually delete this file I get the sharing violation
> >> error message - that someone or something process may be
> >> using the file. I know for a fact that it is not
> someone
> >> and I need to delete this file. I take ownership of the
> >> file with full control permissions but still can't
> delete
> >> this file. I hate to bounce a server just to be able to
> >> delete a file. Does anyone know what I can do to delete
> >> this file? Is there a way to have sql server or agent
> >> take control of the files I want the jobs to delete? I
> >> appreciate your help. Thanks
> >>
> >> Lisa
> >
> >
> >.
> >|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> > Thank you so much Carlos. The "verify integrity of backup
> > upon completion" was checked. If this is locking the
> > files and uncheck that box, isn't that a bad thing? What
> > is the impact on the integrity of my backups. I will
> > check the winternals website also. Thanks.
> >
> > Lisa.
> >
> >
> > >--Original Message--
> > >Lisa,
> > >This happened to me once a while ago and it was because
> > sql server was still
> > >verifying the backup file. When you use database
> > maintenance plans, one of
> > >the options is to verify the file after the backup is
> > taken. If this is not
> > >the case you're going to have to find a windows utility
> > to find which
> > >windows process has the file locked, i think i found one
> > of these utilities
> > >at www.sysinternals.com a while ago, but i'm not sure.
> > >
> > >--
> > >Carlos E. Rojas
> > >SQL Server MVP
> > >Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> > wrote in message
> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> > >> My backups job are failing at the delete old backup step
> > >> because of not being to delete a backup file. When I
> > try
> > >> to manually delete this file I get the sharing violation
> > >> error message - that someone or something process may be
> > >> using the file. I know for a fact that it is not
> > someone
> > >> and I need to delete this file. I take ownership of the
> > >> file with full control permissions but still can't
> > delete
> > >> this file. I hate to bounce a server just to be able to
> > >> delete a file. Does anyone know what I can do to delete
> > >> this file? Is there a way to have sql server or agent
> > >> take control of the files I want the jobs to delete? I
> > >> appreciate your help. Thanks
> > >>
> > >> Lisa
> > >
> > >
> > >.
> > >
>|||Thank you very much.
>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
>> That option is IMO not that useful. To check out what
it does, read about
>> the VERIFYONLY option to the RESTORE in Books Online.
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> > Thank you so much Carlos. The "verify integrity of
backup
>> > upon completion" was checked. If this is locking the
>> > files and uncheck that box, isn't that a bad thing?
What
>> > is the impact on the integrity of my backups. I will
>> > check the winternals website also. Thanks.
>> >
>> > Lisa.
>> >
>> >
>> > >--Original Message--
>> > >Lisa,
>> > >This happened to me once a while ago and it was
because
>> > sql server was still
>> > >verifying the backup file. When you use database
>> > maintenance plans, one of
>> > >the options is to verify the file after the backup is
>> > taken. If this is not
>> > >the case you're going to have to find a windows
utility
>> > to find which
>> > >windows process has the file locked, i think i found
one
>> > of these utilities
>> > >at www.sysinternals.com a while ago, but i'm not
sure.
>> > >
>> > >--
>> > >Carlos E. Rojas
>> > >SQL Server MVP
>> > >Co-Author SQL Server 2000 Programming by Example
>> > >
>> > >
>> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> > wrote in message
>> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> > >> My backups job are failing at the delete old
backup step
>> > >> because of not being to delete a backup file.
When I
>> > try
>> > >> to manually delete this file I get the sharing
violation
>> > >> error message - that someone or something process
may be
>> > >> using the file. I know for a fact that it is not
>> > someone
>> > >> and I need to delete this file. I take ownership
of the
>> > >> file with full control permissions but still can't
>> > delete
>> > >> this file. I hate to bounce a server just to be
able to
>> > >> delete a file. Does anyone know what I can do to
delete
>> > >> this file? Is there a way to have sql server or
agent
>> > >> take control of the files I want the jobs to
delete? I
>> > >> appreciate your help. Thanks
>> > >>
>> > >> Lisa
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>Lis|||Thanks Tibor
>--Original Message--
>That option is IMO not that useful. To check out what it
does, read about
>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> Thank you so much Carlos. The "verify integrity of
backup
>> upon completion" was checked. If this is locking the
>> files and uncheck that box, isn't that a bad thing?
What
>> is the impact on the integrity of my backups. I will
>> check the winternals website also. Thanks.
>> Lisa.
>>
>> >--Original Message--
>> >Lisa,
>> >This happened to me once a while ago and it was because
>> sql server was still
>> >verifying the backup file. When you use database
>> maintenance plans, one of
>> >the options is to verify the file after the backup is
>> taken. If this is not
>> >the case you're going to have to find a windows utility
>> to find which
>> >windows process has the file locked, i think i found
one
>> of these utilities
>> >at www.sysinternals.com a while ago, but i'm not sure.
>> >
>> >--
>> >Carlos E. Rojas
>> >SQL Server MVP
>> >Co-Author SQL Server 2000 Programming by Example
>> >
>> >
>> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> >> My backups job are failing at the delete old backup
step
>> >> because of not being to delete a backup file. When I
>> try
>> >> to manually delete this file I get the sharing
violation
>> >> error message - that someone or something process
may be
>> >> using the file. I know for a fact that it is not
>> someone
>> >> and I need to delete this file. I take ownership of
the
>> >> file with full control permissions but still can't
>> delete
>> >> this file. I hate to bounce a server just to be
able to
>> >> delete a file. Does anyone know what I can do to
delete
>> >> this file? Is there a way to have sql server or
agent
>> >> take control of the files I want the jobs to
delete? I
>> >> appreciate your help. Thanks
>> >>
>> >> Lisa
>> >
>> >
>> >.
>> >
>
>.
>
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa|||Thank you so much Carlos. The "verify integrity of backup
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because
sql server was still
>verifying the backup file. When you use database
maintenance plans, one of
>the options is to verify the file after the backup is
taken. If this is not
>the case you're going to have to find a windows utility
to find which
>windows process has the file locked, i think i found one
of these utilities
>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> My backups job are failing at the delete old backup step
>> because of not being to delete a backup file. When I
try
>> to manually delete this file I get the sharing violation
>> error message - that someone or something process may be
>> using the file. I know for a fact that it is not
someone
>> and I need to delete this file. I take ownership of the
>> file with full control permissions but still can't
delete
>> this file. I hate to bounce a server just to be able to
>> delete a file. Does anyone know what I can do to delete
>> this file? Is there a way to have sql server or agent
>> take control of the files I want the jobs to delete? I
>> appreciate your help. Thanks
>> Lisa
>
>.
>|||That option is IMO not that useful. To check out what it does, read about
the VERIFYONLY option to the RESTORE in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> >--Original Message--
> >Lisa,
> >This happened to me once a while ago and it was because
> sql server was still
> >verifying the backup file. When you use database
> maintenance plans, one of
> >the options is to verify the file after the backup is
> taken. If this is not
> >the case you're going to have to find a windows utility
> to find which
> >windows process has the file locked, i think i found one
> of these utilities
> >at www.sysinternals.com a while ago, but i'm not sure.
> >
> >--
> >Carlos E. Rojas
> >SQL Server MVP
> >Co-Author SQL Server 2000 Programming by Example
> >
> >
> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> >> My backups job are failing at the delete old backup step
> >> because of not being to delete a backup file. When I
> try
> >> to manually delete this file I get the sharing violation
> >> error message - that someone or something process may be
> >> using the file. I know for a fact that it is not
> someone
> >> and I need to delete this file. I take ownership of the
> >> file with full control permissions but still can't
> delete
> >> this file. I hate to bounce a server just to be able to
> >> delete a file. Does anyone know what I can do to delete
> >> this file? Is there a way to have sql server or agent
> >> take control of the files I want the jobs to delete? I
> >> appreciate your help. Thanks
> >>
> >> Lisa
> >
> >
> >.
> >|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> > Thank you so much Carlos. The "verify integrity of backup
> > upon completion" was checked. If this is locking the
> > files and uncheck that box, isn't that a bad thing? What
> > is the impact on the integrity of my backups. I will
> > check the winternals website also. Thanks.
> >
> > Lisa.
> >
> >
> > >--Original Message--
> > >Lisa,
> > >This happened to me once a while ago and it was because
> > sql server was still
> > >verifying the backup file. When you use database
> > maintenance plans, one of
> > >the options is to verify the file after the backup is
> > taken. If this is not
> > >the case you're going to have to find a windows utility
> > to find which
> > >windows process has the file locked, i think i found one
> > of these utilities
> > >at www.sysinternals.com a while ago, but i'm not sure.
> > >
> > >--
> > >Carlos E. Rojas
> > >SQL Server MVP
> > >Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> > wrote in message
> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> > >> My backups job are failing at the delete old backup step
> > >> because of not being to delete a backup file. When I
> > try
> > >> to manually delete this file I get the sharing violation
> > >> error message - that someone or something process may be
> > >> using the file. I know for a fact that it is not
> > someone
> > >> and I need to delete this file. I take ownership of the
> > >> file with full control permissions but still can't
> > delete
> > >> this file. I hate to bounce a server just to be able to
> > >> delete a file. Does anyone know what I can do to delete
> > >> this file? Is there a way to have sql server or agent
> > >> take control of the files I want the jobs to delete? I
> > >> appreciate your help. Thanks
> > >>
> > >> Lisa
> > >
> > >
> > >.
> > >
>|||Thank you very much.
>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
>> That option is IMO not that useful. To check out what
it does, read about
>> the VERIFYONLY option to the RESTORE in Books Online.
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> > Thank you so much Carlos. The "verify integrity of
backup
>> > upon completion" was checked. If this is locking the
>> > files and uncheck that box, isn't that a bad thing?
What
>> > is the impact on the integrity of my backups. I will
>> > check the winternals website also. Thanks.
>> >
>> > Lisa.
>> >
>> >
>> > >--Original Message--
>> > >Lisa,
>> > >This happened to me once a while ago and it was
because
>> > sql server was still
>> > >verifying the backup file. When you use database
>> > maintenance plans, one of
>> > >the options is to verify the file after the backup is
>> > taken. If this is not
>> > >the case you're going to have to find a windows
utility
>> > to find which
>> > >windows process has the file locked, i think i found
one
>> > of these utilities
>> > >at www.sysinternals.com a while ago, but i'm not
sure.
>> > >
>> > >--
>> > >Carlos E. Rojas
>> > >SQL Server MVP
>> > >Co-Author SQL Server 2000 Programming by Example
>> > >
>> > >
>> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> > wrote in message
>> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> > >> My backups job are failing at the delete old
backup step
>> > >> because of not being to delete a backup file.
When I
>> > try
>> > >> to manually delete this file I get the sharing
violation
>> > >> error message - that someone or something process
may be
>> > >> using the file. I know for a fact that it is not
>> > someone
>> > >> and I need to delete this file. I take ownership
of the
>> > >> file with full control permissions but still can't
>> > delete
>> > >> this file. I hate to bounce a server just to be
able to
>> > >> delete a file. Does anyone know what I can do to
delete
>> > >> this file? Is there a way to have sql server or
agent
>> > >> take control of the files I want the jobs to
delete? I
>> > >> appreciate your help. Thanks
>> > >>
>> > >> Lisa
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>Lis|||Thanks Tibor
>--Original Message--
>That option is IMO not that useful. To check out what it
does, read about
>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> Thank you so much Carlos. The "verify integrity of
backup
>> upon completion" was checked. If this is locking the
>> files and uncheck that box, isn't that a bad thing?
What
>> is the impact on the integrity of my backups. I will
>> check the winternals website also. Thanks.
>> Lisa.
>>
>> >--Original Message--
>> >Lisa,
>> >This happened to me once a while ago and it was because
>> sql server was still
>> >verifying the backup file. When you use database
>> maintenance plans, one of
>> >the options is to verify the file after the backup is
>> taken. If this is not
>> >the case you're going to have to find a windows utility
>> to find which
>> >windows process has the file locked, i think i found
one
>> of these utilities
>> >at www.sysinternals.com a while ago, but i'm not sure.
>> >
>> >--
>> >Carlos E. Rojas
>> >SQL Server MVP
>> >Co-Author SQL Server 2000 Programming by Example
>> >
>> >
>> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> >> My backups job are failing at the delete old backup
step
>> >> because of not being to delete a backup file. When I
>> try
>> >> to manually delete this file I get the sharing
violation
>> >> error message - that someone or something process
may be
>> >> using the file. I know for a fact that it is not
>> someone
>> >> and I need to delete this file. I take ownership of
the
>> >> file with full control permissions but still can't
>> delete
>> >> this file. I hate to bounce a server just to be
able to
>> >> delete a file. Does anyone know what I can do to
delete
>> >> this file? Is there a way to have sql server or
agent
>> >> take control of the files I want the jobs to
delete? I
>> >> appreciate your help. Thanks
>> >>
>> >> Lisa
>> >
>> >
>> >.
>> >
>
>.
>
deleting a sql backup file
My backups job are failing at the delete old backup step
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
sql server was still
maintenance plans, one of
taken. If this is not
to find which
of these utilities
wrote in message
the VERIFYONLY option to the RESTORE in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...[QUOTE]
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> sql server was still
> maintenance plans, one of
> taken. If this is not
> to find which
> of these utilities
> wrote in message
> try
> someone
> delete|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
http://groups.google.com/groups?oi=...ublic.sqlserver
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
oi=djq&as_ugroup=microsoft.public.sqlserver
does, read about
oi=djq&as_ugroup=microsoft.public.sqlserver
wrote in message
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
quote:|||Thank you so much Carlos. The "verify integrity of backup
> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
quote:
>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because
sql server was still
quote:
>verifying the backup file. When you use database
maintenance plans, one of
quote:
>the options is to verify the file after the backup is
taken. If this is not
quote:
>the case you're going to have to find a windows utility
to find which
quote:
>windows process has the file locked, i think i found one
of these utilities
quote:
>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
quote:|||That option is IMO not that useful. To check out what it does, read about
>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
try[QUOTE]
someone[QUOTE]
delete[QUOTE]
>
>.
>
the VERIFYONLY option to the RESTORE in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...[QUOTE]
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> sql server was still
> maintenance plans, one of
> taken. If this is not
> to find which
> of these utilities
> wrote in message
> try
> someone
> delete|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
quote:
> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:|||Thank you very much.
>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>
quote:
>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 programming by Example
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
quote:
>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
it does, read about[QUOTE]
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:|||Thanks Tibor
wrote in message[QUOTE]
backup[QUOTE]
What[QUOTE]
because[QUOTE]
utility[QUOTE]
one[QUOTE]
sure.[QUOTE]
backup step[QUOTE]
When I[QUOTE]
violation[QUOTE]
may be[QUOTE]
of the[QUOTE]
able to[QUOTE]
delete[QUOTE]
agent[QUOTE]
delete? I[QUOTE]
>
>.
>Lis
quote:
>--Original Message--
>That option is IMO not that useful. To check out what it
does, read about
quote:
>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
quote:
>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
backup[QUOTE]
What[QUOTE]
one[QUOTE]
step[QUOTE]
violation[QUOTE]
may be[QUOTE]
the[QUOTE]
able to[QUOTE]
delete[QUOTE]
agent[QUOTE]
delete? I[QUOTE]
>
>.
>
Saturday, February 25, 2012
delete tables automatically
How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually)
But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm.
thanks in advance,
mulata
Does Automatically means just sending a query from you program? DELETE FROM TABLENAME
|||yes, but how can I implement that in my .NET program?
The Database should simply be cleared totally, so all tables should be deleted.
|||You would need to explain a little bit more rather than one liners. Do you need to delete all the tables or some of them or just one of them?
You could write a stored proc to drop all the tables and just call the proc from your .NET code.
|||okay, I just want to delete all tables in a database.
Im sorry but I have no idea how I can write such a proc, which I can call from my .NET code.
It would be great if someone can give me an example of such a proc.
Actually I think what the proc has to do, is just connect to the database, and the make a simple call, but I dont know what there is to do in detail.
thanks in advance
|||SqlConnection connection =
new
SqlConnection (ConfigurationSettings.AppSettings[
"ConnectionString"]);
try
{
connection.Open ();
SqlCommand cmd =
new SqlCommand ("delete from (select * from sys.tablenames)" )
cmd.ExecuteNonQuery ();
}
finally
{
connection.Close ();
}
|||Depending on your business requirements if you dont need the data ever, you might look into truncatng the tables. DELETE'ing can bloat the logs. TRUNCATE is a non-logged operation.
Read up the documentation regarding DELETE, TRUNCATE and it wil lhelp you decide which one worls best for you.
CREATE PROC dbo.prcDeleteAllTables
AS
BEGIN
TRUNCATE TABLE tableA
END
Subscribe to:
Posts (Atom)