Deleting old unused database issue

I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.
can any one suggest me how to do this?
thanksSimplest is execute sp_who2. That will tell you if there are any current connections. If you need something more bullet proof then you will need to audit connections over a time period. Or you could try a scream test if it is not critical - which presumably it is not since you are contemplating dropping it.|||Use sql profiler and let it dump the results to a table and query that

Most of my servers are dedicated to a single database though

Just make sure you have a current dump

Now a soapbox issue

I like to have sproc only access to my databases, and each sproc has sproc logging, so I know everything that's going on|||...Or you could try a scream test...One of my phrases has entered the public lexicon!|||There'll be scads of them soon I'm sure.|||The "scream test" is one of my best, but the "Boxers vs Briefs solution" is more colorful.sql

deleteing small amount of records from a view causes IX lock on all the base tables.

Sorry that I had to post it as new message instead of replying since I got server application error.
Kalen, this is a different issue. I wonder why other 4 base tables got IX TAB lock as well since the partitioned view is supposed to look up the relevant tables only by querying on the constraint column.Tom
Can you please include relevant portions of the original message, so I can
know what I am replying to without having to search the archives?
If this is a question about partitioned views, did you supply the view
definition, and the version you are using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:29346002-6963-4D4E-B63C-C6A5C5E292CD@.microsoft.com...
> Sorry that I had to post it as new message instead of replying since I got
server application error.
> Kalen, this is a different issue. I wonder why other 4 base tables got IX
TAB lock as well since the partitioned view is supposed to look up the
relevant tables only by querying on the constraint column.

Deleted row information cannot be accessed through the row

Hi,
I got following exception when accessing database using release version of
my C# application:
System.Data.DeletedRowInaccessibleException: Deleted row information cannot
be accessed through the row.
at System.Data.DataRow.GetDefaultRecord()
at System.Data.DataRow.get_Item(String columnName)
at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
Database\TwsUser.cs:line 185
at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
e) in C:\ZCAP\TWS\Tws.cs:line 2068
at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
levent)
at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
at System.Windows.Forms.Control.PerformLayout()
at ZCAP.ZAPP.Tws.InitializeComponent() in
C:\ZCAP\TWS\Tws.designer.cs:line 4025
at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
In following 2 situations, there is no exception:
(1) Running under debug version
(2) In visual studio, if I select "Release", then click "Debug" -> "Start
Debugging"
Your help is highly appreciated!
Hank"Hang" <hyuan@.zcap.net> wrote in message
news:#LcxwJBaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I got following exception when accessing database using release version of
> my C# application:
> System.Data.DeletedRowInaccessibleException: Deleted row information
> cannot be accessed through the row.
> at System.Data.DataRow.GetDefaultRecord()
> at System.Data.DataRow.get_Item(String columnName)
> at ZCAP.Database.ZappUser.get_ZappRole() in C:\ZCAP\ZCAP
> Database\TwsUser.cs:line 185
> at ZCAP.ZAPP.Tws.tabPageAccounting_Layout(Object sender, LayoutEventArgs
> e) in C:\ZCAP\TWS\Tws.cs:line 2068
> at System.Windows.Forms.Control.OnLayout(LayoutEventArgs levent)
> at System.Windows.Forms.ScrollableControl.OnLayout(LayoutEventArgs
> levent)
> at System.Windows.Forms.Control.PerformLayout(LayoutEventArgs args)
> at System.Windows.Forms.Control.PerformLayout()
> at ZCAP.ZAPP.Tws.InitializeComponent() in
> C:\ZCAP\TWS\Tws.designer.cs:line 4025
> at ZCAP.ZAPP.Tws..ctor(ZappUser user) in C:\ZCAP\TWS\Tws.cs:line 33
> at ZCAP.ZAPP.TwsMain.Main() in C:\ZCAP\TWS\TwsMain.cs:line 38
> In following 2 situations, there is no exception:
> (1) Running under debug version
> (2) In visual studio, if I select "Release", then click "Debug" -> "Start
> Debugging"
>
This is an ADO.NET question, not a SQL Server question. Try posting in the
ADO.NET groups.
David

Delete's Value

Has anyone ever had an issue when they try to commit changes back to the database based on a value they enter in a application...the value that u would use (i.e Select * From Customers Where CustomerID = 'value') to get the rest of the value's pertaining to that value you enter gets deleted from the database....its really wierd but i have that problemYou mean that rows have been deleted by selecting them ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I mean the one value i enter in my form gets deleted all the information from that row stay's except for the search value that i use...|||

"My form" is not really descriptive enough. Is this a tool issue? Or a SQL issue with code you have written to support a form?

If it is SQL, can you post some of the table structures? If it is the former, I would probably suggest you use profiler to get a view of the queries being sent and try to match that to what you expect to happen.

|||How would i use the SQL profiler to see whats being sent...I have not used it before but ill check the net too...thanks

Delete subscription problem

Hi,
I create both non-data-driven subscription and data-driven subscription in
my application for one report. Is there any way to distinguish those two
subscriptions? So I can delete them, since Reporting Services uses different
functions to delete the non-data-driven subscription and data-driven
subscription.
Thank you very much for your help!
MichelleListSubscriptions returns both types and has a bool which tells you if the
subscription is a data driven subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:E44A9504-2058-4F74-9D47-7473DB69FE94@.microsoft.com...
> Hi,
> I create both non-data-driven subscription and data-driven subscription in
> my application for one report. Is there any way to distinguish those two
> subscriptions? So I can delete them, since Reporting Services uses
> different
> functions to delete the non-data-driven subscription and data-driven
> subscription.
> Thank you very much for your help!
> Michelle|||Thank you!!!
"Daniel Reib (MSFT)" wrote:
> ListSubscriptions returns both types and has a bool which tells you if the
> subscription is a data driven subscription.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:E44A9504-2058-4F74-9D47-7473DB69FE94@.microsoft.com...
> > Hi,
> >
> > I create both non-data-driven subscription and data-driven subscription in
> > my application for one report. Is there any way to distinguish those two
> > subscriptions? So I can delete them, since Reporting Services uses
> > different
> > functions to delete the non-data-driven subscription and data-driven
> > subscription.
> >
> > Thank you very much for your help!
> >
> > Michelle
>
>

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
ontario, canada
I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>
|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
ontario, canada
"Tibor Karaszi" wrote:

> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
>
>

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
--
ontario, canada> What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
3. Remove that now empty file from the database:
ALTER DATABASE dbname REMOVE FILE logical_file_name
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> SQL server 2000 sp3
> Hi
> I have a sql server 2000 database. In one application initial database size
> was 13 gb. We created primary data file for required size. Application later
> required 430 gb more space. We added another datafile (secondary) with 430
> gb. The problem is the application does not allow us to specify where to put
> the file. Looks like it just load the data in the primary datafile and then
> does not try to load files in secondary datafile and secondary datafile is
> not being used.
> Application developer wants me to just have one datafile (Primary data file
> with 450 gb). What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
>
> --
> ontario, canada|||I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database
>> size
>> was 13 gb. We created primary data file for required size. Application
>> later
>> required 430 gb more space. We added another datafile (secondary) with
>> 430
>> gb. The problem is the application does not allow us to specify where to
>> put
>> the file. Looks like it just load the data in the primary datafile and
>> then
>> does not try to load files in secondary datafile and secondary datafile
>> is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data
>> file
>> with 450 gb). What is the best method to delete the secondary datafile
>> and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada
>|||DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
with the transaction log if lots of pages will be "moved" to other files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player" <bass_player@.mvps.org> wrote in message
news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> I'm just curious as to what happens when you empty the database files
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
>> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
>> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
>> 3. Remove that now empty file from the database:
>> ALTER DATABASE dbname REMOVE FILE logical_file_name
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database size
>> was 13 gb. We created primary data file for required size. Application later
>> required 430 gb more space. We added another datafile (secondary) with 430
>> gb. The problem is the application does not allow us to specify where to put
>> the file. Looks like it just load the data in the primary datafile and then
>> does not try to load files in secondary datafile and secondary datafile is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data file
>> with 450 gb). What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
--
ontario, canada
"Tibor Karaszi" wrote:
> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> > I'm just curious as to what happens when you empty the database files
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
> >> What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
> >> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> >>
> >> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> >>
> >> 3. Remove that now empty file from the database:
> >> ALTER DATABASE dbname REMOVE FILE logical_file_name
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> >> SQL server 2000 sp3
> >> Hi
> >> I have a sql server 2000 database. In one application initial database size
> >> was 13 gb. We created primary data file for required size. Application later
> >> required 430 gb more space. We added another datafile (secondary) with 430
> >> gb. The problem is the application does not allow us to specify where to put
> >> the file. Looks like it just load the data in the primary datafile and then
> >> does not try to load files in secondary datafile and secondary datafile is
> >> not being used.
> >>
> >> Application developer wants me to just have one datafile (Primary data file
> >> with 450 gb). What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >>
> >> --
> >> ontario, canada
> >>
>
>

delete multiple tables

i have a inherited adatabase application that creates a table everyday.
because a table is created evryday the database is becoming huge. after muc
h
deliberation i have decided that i only need to keep 30 days of tables. how
can i delete all tables older than 30 days inside the database? i am fairly
new to sql and i have not been able to figure out how to delete multiple
tables based on creation date.
ronnieHi Ronnie
Ur idea in deleting old tables looks good but never try to delete a table
based on the date they were created.
This might also delete your production tables in the database which are
important
create your tables for ex: ForDelete_<Table_name>_<date>
and now u can write a stored procedure to loop arround and delete these kind
of tables.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||See if this helps, but first be sure to have a backup of the db before using
it in production.
use your_db
go
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cd datetime
declare tables_older_than_30_days cursor local fast_forward
for
select
user_name(uid) as table_schema,
[name] as table_name,
crdate
from
sysobjects
where
xtype = 'U'
and objectproperty([id], 'IsMSShipped') = 0
and datediff(day, crdate, getdate()) > 30
open tables_older_than_30_days
while 1 = 1
begin
fetch next from tables_older_than_30_days into @.ts, @.tn, @.cd
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'drop table ' + quotename(@.ts) + N'.' + quotename(@.tn)
print @.sql
-- uncomment this line to drop the table
-- exec sp_executesql @.sql
end
close tables_older_than_30_days
deallocate tables_older_than_30_days
go
AMB
"Ronnie" wrote:

> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after m
uch
> deliberation i have decided that i only need to keep 30 days of tables. h
ow
> can i delete all tables older than 30 days inside the database? i am fair
ly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||Ronny
Wow , it looks to me you have a poor designed database
Why do you/someone else need to create a table every day?
In sysobjects system table there is a cdate column .
"Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> i have a inherited adatabase application that creates a table everyday.
> because a table is created evryday the database is becoming huge. after
much
> deliberation i have decided that i only need to keep 30 days of tables.
how
> can i delete all tables older than 30 days inside the database? i am
fairly
> new to sql and i have not been able to figure out how to delete multiple
> tables based on creation date.
> ronnie|||the database is for an automated vehicle location tacking. each day becomes
its own table. the table is populated with gps points for multiple vehicles
.
i am not sure why they set it up this way. i have not given it a lot of
thought yet. first i want to clean it up then look at redsigning it. thank
s
for the tip.
ronnie
"Uri Dimant" wrote:

> Ronny
> Wow , it looks to me you have a poor designed database
> Why do you/someone else need to create a table every day?
> In sysobjects system table there is a cdate column .
>
>
> "Ronnie" <Ronnie @.discussions.microsoft.com> wrote in message
> news:BC19E550-1D16-4B6D-952A-31CA103B2FE3@.microsoft.com...
> much
> how
> fairly
>
>

Delete matched query

I'm working with a legacy application that stores its data in a SQL Server 2
K
database. I want to write a delete query to delete items from table A, wher
e
there is no match in table B, where the match is based on matches of the two
PK fields.
I tried the following, but got an error: Incorrect syntax near the keyword
'LEFT'
DELETE FROM A
LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
Any help would be greatly appreciated.
DaleHow about this?
DELETE A
FROM A
INNER JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL|||Try
DELETE A
From A LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
the Tables are actually name dTableA and TableB, then the First line has t
ouse the Alias, not the actual Table Name,. i.e.,
DELETE A
From TableA A Left Join TableB B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
A Much clearer way to code this is to use SQL that mirrors exactly what you
want
Delete TableA
Where Not Exists
(Select * From TableB
Where PK = TableA.PK)
"Dale Fye" wrote:

> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||I meant
Delete TableA
Where Not Exists
(Select * From TableB
Where ExerciseID = TableA.ExerciseID
And UserID = TableA.UserID)
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>|||Try this
Delete From A
Where Not Exists(
Select * From B Where B.Col1=A.Col1 And B.Col2=A.Col2
)
Dmitriy
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:B16B3312-7EDD-4D85-A2D0-7806F2064BEE@.microsoft.com...
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A,
where
> there is no match in table B, where the match is based on matches of the
two
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||Dale Fye wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
DELETE FROM A
WHERE NOT EXISTS (SELECT * FROM B
WHERE B.ExerciseID = A.ExerciseID
AND B.UserID = A.UserID)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjX6lIechKqOuFEgEQIHQQCfe67DnoBuMAKw
lKtCX8+H7Wd9ANAAmwS2
sbyoMnwLgSk2DmyMUtxtgESf
=hYSb
--END PGP SIGNATURE--|||Thanks to all who responded. I've been working in Access so long, I forgot
about Exists and Not Exists.
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>

Delete Lock

Hi,
I'm trying to clean up some old mess. We've got an sql server that keeps
reporting deadlocks. There is a loging application that keeps inserting
and deleting records from a table. The delete statement will always delete
only 1 row. The table is without primary keys and indexes. My question is
this: How will the delete statement lock the rows ? Will it apply a
rowlock, pagelock or table lock ?
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/If the table does not have primary key or indexes, then sql server have to d
o
a table scan to find the row and may be it is scalating the lock. See "Lock
Escalation" in BOL. If possible, create a pk.
AMB
"kfu" wrote:

> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||Who is blocking who and under what circumstances. Even a query will acquire
shared locks, meaning that a long running query can block an
insert/update/delete for the duration of it's select or an i/u/d can block a
query for the duration of it's transaction. Read up on options for "set
transaction isolation level". Perhaps allowing queries to the log table to
read uncommitted will help resolve you issue. Also, if you are logging
things like user activity (for example logging the last time a record was
viewed by the user), then do not do this in a fact table, instead use a
seperate log table just for that purpose.
"kfu" <kfurnes@.netcom.no> wrote in message
news:op.sraov9ia0gg7rt@.kjafur2.sense.sensetech.no...
> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/