sql delete multiple tables: March 2012

Thursday, March 29, 2012

delete duplicate record problem

create table tbl1(
fld1 varchar(10),
fld2 varchar(10))
insert into tbl1 Values('joe','x')
insert into tbl1 Values('joe','y')
insert into tbl1 Values('joe','z')
insert into tbl1 Values('bill','x')
insert into tbl1 Values('sam','z')
insert into tbl1 Values('ted','x')
insert into tbl1 Values('ted','y')
insert into tbl1 Values('ed','x')
insert into tbl1 Values('mary','x')
insert into tbl1 Values('sue','z')
insert into tbl1 Values('tom','x')
insert into tbl1 Values('tom','y')
insert into tbl1 Values('tom','z')
insert into tbl1 Values('frank','x')
insert into tbl1 Values('samir','x')
insert into tbl1 Values('li','x')
insert into tbl1 Values('li','z')
insert into tbl1 Values('cindy','z')
insert into tbl1 Values('bert','x')
insert into tbl1 Values('jon','x')
--the statement below contains duplicate records.
SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 )
--I want to remove/delete the records from tbl1 where fld1 has count(fld1)
> 1 and fld2 = 'z' -- joe, x, joe, y, joe, z| tom, x, tom, y, tom, z| li, x,[/color
]
li, z.
joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld2.
I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
remove rows where count(fld1)>1 and fld2 = 'z'.
--Here is what I tried that did not work:
Delete from tbl1
Where fld2 in
(SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 ))
and fld2 = 'z'
Any suggestions appreciated how this could be accomplished.
Thanks,
RichTry THis:
Delete tbl1
Where fld1 In
(SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING Count(*)>1 )
And Fld2 = 'z'
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich|||Thanks very much.
"CBretana" wrote:
> Try THis:
> Delete tbl1
> Where fld1 In
> (SELECT fld1
> FROM tbl1
> GROUP BY fld1
> HAVING Count(*)>1 )
> And Fld2 = 'z'
> "Rich" wrote:
>|||And for the future, use UNIQUE constraints to ensure your data doesn't get
this way in the first place.
-- Alex
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich

Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete Duplicate Rec

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

Quote:

Originally Posted by amitmant

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

this is the Qry to delete duplicate records from table(Database)

===================QRY======================
delete from table1 where Field1=(select field1 from table1 group by field1 having count(field1)>1)|||This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.|||

Quote:

Originally Posted by iburyak

This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.

Can you explain the concept of single instance it is dangerous proposition.sql

delete duplicate orders

I have an order with multiple line items. When the order comes in and put into a table I would like to check that they haven't submitted it twice. Right now I have a stored procedure that can find duplicate lines, but I really need if the whole order ha
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
hi ashley,
See following example:
--sample data.
create table #cartype(manufacturer varchar(500), itemnumber int)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',3)
insert into #cartype values('GE',3)
--deleting all duplicate rows from the table, query will be.
delete a
from #cartype a join
(select manufacturer, itemnumber
from #cartype
group by manufacturer, itemnumber
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber
--if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.
Ex:
alter table #cartype add idd int identity
--deleting duplicate rows from the table except one
delete from #cartype
where not exists
(Select * from #cartype a
where a.manufacturer = #cartype.manufacturer
and a.itemnumber = #cartype.itemnumber
having min(idd) = #cartype.idd)
--drop the temporary added column
alter table #cartype drop column idd
Vishal Parkar
vgparkar@.yahoo.co.in

Delete duplicate entry

Hi guys,
How can we delete duplicate entries in a table. please send me a
query which delete duplicate entry from table.Manish Sukhija wrote:

> How can we delete duplicate entries in a table. please
> send me a query which delete duplicate entry from table.
This has been discussed a couple of times the last w

s:
http://shurl.org/BrNkD
HTH,
Stijn Verrept.

Delete duplicate entries from tables in my database using Query Analyzer

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.Does this table contains any unique key or any other key field?|||Hi,

There is seperate id for each entries but duplicate entries have the same id number.

Thanks in advance,
Uday.|||One solution could be adding identity column to this and then deleting the non relevent data.|||You can move all the duplicate ones into a separate temp table using GROUP BY HAVING COUNT(*)>1

Then you delete them using the same clause can use a SELECT DISTINCT to copy them back from the temp table.

Of course if your table is small you can just copy the lot and do a SELECT DISTINCT back!

delete duplicate email addresses

I have an email list that I need to delete duplicates from. How is this
done easily?
This query will tell me that I have duplicates:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) > 1)
This tells me how many clean addresses I have:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) < 2)
How can I delete duplicates? Sometimese there are 3 or 4 of the same
email address in the file.William,
Does the Table have a unique number to identify the Email Addresses?
If so, you could use something like this..
delete from Table
where EmailID in (select max(EmailID)
from Table
group by Email
having (Count(Email)) > 1)
Thanks
Barry|||> I have an email list that I need to delete duplicates from. How is this
> done easily?
The easiest way is to SELECT DISTINCT into another table, and add a primary
key or unique index/constraint to that column so that this doesn't happen.
http://www.aspfaq.com/2431
http://www.aspfaq.com/2509|||First, make sure that the entire record is a duplicate and not just the
email. Otherwise, deleting records containing duplicate emails may result in
losing information. For example, if you have 2 records for John Doe with
same email but different phone numbers, then which record should you delete?
If it is just a list of emails, then you can select distinct from one table
into another table. For example:
select distinct email into TableB from TableA
If you do not want to deal with inserting into a 2nd table but rather
perform regular maintenance on the existing table, then you can delete out
duplicates using the method below. This requires that the table have a
unique key column. Test in the NorthWind sample database:
begin transaction
select count(*) from [Order Details]
delete
from
[Order Details]
where
OrderID <> (select max(X.OrderID) from [Order Details] as X where
X.ProductID = [Order Details].ProductID)
select count(*) from [Order Details]
rollback transaction
"William" <da@.northernit.net> wrote in message
news:NmHTd.40252$H05.29776@.twister.nyroc.rr.com...
> I have an email list that I need to delete duplicates from. How is this
> done easily?
> This query will tell me that I have duplicates:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) > 1)
> This tells me how many clean addresses I have:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) < 2)
> How can I delete duplicates? Sometimese there are 3 or 4 of the same
> email address in the file.
>
>|||Thanks. I did have an autonumber key field. The first method works well if
I execute the query several times to get rid of folks who have their emails
2-4 times in that database.
The addresses are collected when users take a diagnostics test on our
website. Duplicate come about because users find our content really useful.
They take the test or assessment quickly the first time and then 50% of the
time take the assessment again answering the questions with more thought.
We ask the persons age and usually the women lie about their age on the
first pass. When they find out that their results are compared to women
their age they take the test again usually answering the age question 2-5
years older (go figure...). We keep all of the data on every customer over
time. Lots of duplicats...
Anyway, thanks for the help. Worked great.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23a9Or90GFHA.2804@.TK2MSFTNGP10.phx.gbl...
> The easiest way is to SELECT DISTINCT into another table, and add a
primary
> key or unique index/constraint to that column so that this doesn't happen.
> http://www.aspfaq.com/2431
> http://www.aspfaq.com/2509
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @.@.ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Delete doesn''t delete rows, but @@ROWCOUNT says it did

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

Does anyone have suggestions on what might cause this sort of behavior?
just wondering if any of these options were enable

1- SET ROWCOUNT=1
2- Any Trigger on 2005 tables?
|||

Most likely this effect is due to triggers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Delete does not work.

Hi all.
I have used a SqlDataSource in my page with this delete command:
DELETE FROM tblPersonnel WHERE (ID = @.original_ID)
and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".
and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work? Hmm

Thanks in advance.

Check whether the grid's delete command event is triggered by debugging.

Also, check the parameter value that is passed

|||

Yes. It is triggered. RowDeleting and Even RowDeleted but e.AffectedRows is 0.

|||

Do you have DeleteParameter Original_ID:

<DeleteParameters>
<asp:Parameter Name="original_ID" Type="Int32" />
</DeleteParameters>

|||

Monitor the parameter when deleteing, is it null?

|||

Yes. I have DeleteParameter.
parameters for deleting are not null.
What incomprehensive error! Crying

|||

Can you post your code?

|||

My codes are very long but the main code is here:

<asp:SqlDataSourceID="sdsPersonnel"runat="server"ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:UniversityDBConnectionString %>"DeleteCommand="DELETE FROM tblPersonnel WHERE (ID = @.original_ID)"
InsertCommand="INSERT INTO tblPersonnel(Name, Family, NationalCode, IdentityCardNumber, FatherName, BirthDay, BirthPlace, IsMale, Marital, ServicePlace, EmploymentType, WorkBeginDate, Saved, Education, WorkingField, Duty, IntPhone, ExtPhone, Fax, Email, Image, Type, Culture) VALUES (@.Name, @.Family, @.NationalCode, @.IdentityCardNumber, @.FatherName, @.BirthDay, @.BirthPlace, @.IsMale, Marital, @.ServicePlace, @.EmploymentType, @.WorkBeginDate, @.Saved, @.Education, @.WorkingField, @.Duty, @.IntPhone, @.ExtPhone, @.Fax, @.Email, @.Image, 'AcademicUnit', @.Culture)"
OldValuesParameterFormatString="original_{0}"OnInserted="sdsPersonnel_Inserted"
SelectCommand="SELECT ID, Name, Family, NationalCode, IdentityCardNumber, FatherName, BirthDay, BirthPlace, IsMale, Marital, ServicePlace, EmploymentType, WorkBeginDate, Saved, Education, WorkingField, Duty, IntPhone, ExtPhone, Fax, Email, Image, Type, Culture FROM tblPersonnel WHERE (Type='AcademicUnit') AND (Culture=@.Culture)"
UpdateCommand="UPDATE tblPersonnel SET Name = @.Name, Family = @.Family, NationalCode = @.NationalCode, IdentityCardNumber = @.IdentityCardNumber, FatherName = @.FatherName, BirthDay = @.BirthDay, BirthPlace = @.BirthPlace, IsMale = @.IsMale, Marital=@.Marital, ServicePlace = @.ServicePlace, EmploymentType = @.EmploymentNumber, WorkBeginDate = @.WorkBeginDate, Saved = @.Saved, Education = @.Education, WorkingField = @.WorkingField, Duty = @.Duty, IntPhone = @.IntPhone, ExtPhone = @.ExtPhone, Fax = @.Fax, Email = @.Email WHERE (ID = @.original_ID)">
<DeleteParameters>
<asp:ParameterName="original_ID"Type="Int32"/>
</DeleteParameters>
</asp:SqlDataSource>

<asp:GridViewID="GridView2"runat="server"AutoGenerateColumns="False"DataSourceID="sdsPersonnel"
Width="100%"OnRowDeleted="GridView2_RowDeleted"OnRowDeleting="GridView2_RowDeleting">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:ButtonID="btnDeletePersonnel"runat="server"CommandArgument='<%# Eval("Image") %>'
CommandName="Delete"CausesValidation="false"OnCommand="btnDeletePersonnel_Command"
Text="<%$ Resources:Resource, Delete %>"/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

protectedvoid btnDeletePersonnel_Command(object sender,CommandEventArgs e)
{
FileInfo fi =newFileInfo(Server.MapPath("~/Upload/Images/Personnels/" + e.CommandArgument.ToString()));
if (fi.Exists)
fi.Delete();
}

|||

I tried to use your code with my database values and everything works like it should. Can you post yourGridView2_RowDeleting andGridView2_RowDeleted events?

Delete directory

Can any one let me know like how can I remove one directory, which contains
couple of file, through xp_cmdshell extended stored procedure.
Thanks in advance.
xp_cmdshell 'rd C:\Somedirectory /s' ?
Remember that fixed Drive letters are on the SQl Server only.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Can any one let me know like how can I remove one directory, which
> contains
> couple of file, through xp_cmdshell extended stored procedure.
> Thanks in advance.
>
|||Thanks Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Can I see all the dos commands like if i write Help in dos command so it
will show all the commands, is there any commands available ?
Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Yes through HELP it's possible.....
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
Thanks in advance,
sck10
Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah
|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>sql

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,
Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete Dates > 6 months

Hello,

I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.

I am unable to find the statement that will execute this operation.

Anyone has an idea please.

Thanks

Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())

Assuming you did record the datetime in your table.|||You mean like:

DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())

Assuming you did record the datetime in your table.

Be careful about months...anywhere in a month counts...

I like days better...

No?|||Be careful about months...anywhere in a month counts...

I like days better...

No?

But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.

When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())

You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:

where record_date < dateadd (month, -6, getdate())

...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.

I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.

Thanks

Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.

I'd still go with Days...

But the above is THE most important point...

My Original would cause a stage 2 predicat (nonsargable).

Very Bad

Would cause a scan...

What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)

* JBuffet

Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.

By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.

DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6

Thanks a lot to all of you who posted.

Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())

Putting the column in the function call would invalidate any index on record_date.

Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?

I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?

*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.

DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6

Thanks a lot to all of you who posted.

Mark

Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:

DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.

"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?

dates are stored as integers

an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"

dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!

But now I need to go back and look at all the query work in my project *LOL*sql

Delete Database User w/o Login name

I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:

Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

How do I remove this user?

It seems like your user owns some schema, most likely the default schema that gets created for the user. You will need to use the "DROP SCHEMA" statement to first drop the user's schema (if it is the default schema, the schema's name will most likely be the same as the user's name). Otherwise you can use "ALTER AUTHORIZATION" to change to the owner of the schema to someone else if you want to keep it. After that you should be able to drop the user.

Thanks,

Sung

|||

You can use the following query to help you identify the schema you need to drop.

select schm.name as 'schema_name'

FROM sys.schemas schm, sys.database_principals usrs

WHERE schm.principal_id = usrs.principal_id AND usrs.name = 'dbo'

-Raul Garcia

SDE/T

SQL Server Engine

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
Dan
I believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

delete database that participates in replication

Does anyone know of abbreviated T-SQL that would sure-fire delete a set of
databases that participate in replication ?
I would like to do this without writing T-SQL to
-- Drop the transactional subscription
-- Drop the transactional articles (every table in publication database)
-- Drop the transactional publication
-- Disable the replication database
-- Drop the registered subscriber
-- Uninstall the server as a Distributor.
John,
you could try something like this:
alter database distribution set single_user with rollback immediate
exec sp_dropdistributor @.no_checks = 1
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
Tha
If you don't care about the database, sure.
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha
|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,ms db..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
ThaIf you don't care about the database, sure.
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx
.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,msdb..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx
.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
ThaIf you don't care about the database, sure.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,msdb..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
--
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

Delete Database Maintenance Plan

If I delete the database maintenance plan, will the jobs
(Like DB Backup Job) be deleted automatically ?
Thank you for your advice.Yes! Just try it out to confirm.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:055f01c39cfa$ce8b0440$a501280a@.phx.gbl...
> If I delete the database maintenance plan, will the jobs
> (Like DB Backup Job) be deleted automatically ?
> Thank you for your advice.

delete database file on server -> sharing violation

Hi,

Since I am new to the environment I don't know where the files come
from and where they might be used.

Can anybody tell me what to do to delete those files?

Thank you.pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

I have some database files (.MDF, .LDF,...) on the server. When I try
to delete them, the warning "Cannot delete file: There has been a
sharing violation. The source or destination file may be in use."
appears.
>
Since I am new to the environment I don't know where the files come
from and where they might be used.
>
Can anybody tell me what to do to delete those files?

Assuming this is MS SQL (I don't know what extensions Oracle uses), you
need to detach the database from SQL. Can be done via GUI or CHUI. For
a GUI, use Enterprise Manager (SQL 2000) or Management Studio (2005).|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I have some database files (.MDF, .LDF,...) on the server. When I try
to delete them, the warning "Cannot delete file: There has been a
sharing violation. The source or destination file may be in use."
appears.
>
Since I am new to the environment I don't know where the files come
from and where they might be used.
>
Can anybody tell me what to do to delete those files?
>
Thank you.

Oracle is not a Microsoft product.

Given that this is Windows reboot the machine. Then delete the files.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||see sqlservr.exe

<pearl146@.hotmail.coma crit dans le message de news:
1192646183.822289.234660@.e34g2000pro.googlegroups. com...

Quote:

Originally Posted by

|||On Oct 17, 3:02 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

>
Assuming this is MS SQL (I don't know what extensions Oracle uses), you
need to detach the database from SQL. Can be done via GUI or CHUI. For
a GUI, use Enterprise Manager (SQL 2000) or Management Studio (2005).

Thanks Ed,
but what do I need to do in Enterprise Manager? How can I delete the
file from there?|||On Oct 17, 1:36 pm, pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I have some database files ... on the server. When I try
to delete them. . .
>
Since I am new to the environment I don't know where the files come
from and where they might be used.
>

<snip>

Let's see, you have files that you don't know where they come from,
you don't know where they might be used, and yet you are still trying
to delete them . . . .|||pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

If you were running on Linux, the operating system would dutifully
obey your command.
The files would remain accessible to existing processes which already
had handles.
After the last handle on the file(s) has been released, the file(s)
would no longer exist.

Be glad that the OS that you are using has training wheels.

There are utilities from SysInternals (since acquired by Microsoft)
for locating processes holding handles on files. Handle.exe would be
one, process monitor is another.

try here:
http://www.microsoft.com/technet/sy...ls/default.mspx
http://www.microsoft.com/technet/sy...wt.svl=featured
You have backups of the databases of interest on this server, right?

hth.

-bdbafh|||On Oct 17, 4:22 pm, EdStevens <quetico_...@.yahoo.comwrote:

Quote:

Originally Posted by

On Oct 17, 1:36 pm, pearl...@.hotmail.com wrote:Hi,
>

Quote:

Originally Posted by

I have some database files ... on the server. When I try
to delete them. . .

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

>
<snip>
>
Let's see, you have files that you don't know where they come from,
you don't know where they might be used, and yet you are still trying
to delete them . . . .

Yes, because my chef wants me to delete them and I don't know how.|||On Oct 17, 5:05 pm, bdbafh <bdb...@.gmail.comwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

Quote:

Originally Posted by

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

Quote:

Originally Posted by

Thank you.

>
If you were running on Linux, the operating system would dutifully
obey your command.
The files would remain accessible to existing processes which already
had handles.
After the last handle on the file(s) has been released, the file(s)
would no longer exist.
>
Be glad that the OS that you are using has training wheels.
>
There are utilities from SysInternals (since acquired by Microsoft)
for locating processes holding handles on files. Handle.exe would be
one, process monitor is another.
>
try here:http://www.microsoft.com/technet/sy...sandthreadsutil...
>
You have backups of the databases of interest on this server, right?
>
hth.
>
-bdbafh

I don't know if there are backups.

Thanks, I will try Handle.exe.|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

On Oct 17, 3:02 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

>>I have some database files (.MDF, .LDF,...) on the server. When I try
>>to delete them, the warning "Cannot delete file: There has been a
>>sharing violation. The source or destination file may be in use."
>>appears.
>>Since I am new to the environment I don't know where the files come
>>from and where they might be used.
>>Can anybody tell me what to do to delete those files?

>Assuming this is MS SQL (I don't know what extensions Oracle uses), you
>need to detach the database from SQL. Can be done via GUI or CHUI. For
>a GUI, use Enterprise Manager (SQL 2000) or Management Studio (2005).

>
Thanks Ed,
but what do I need to do in Enterprise Manager? How can I delete the
file from there?

Find the database on the left-hand side, generally like so:

Console Root
+ Microsoft SQL Servers
+ SQL Server Group
+ (name of database server)
+ Databases
+ (name of database)

then right-click on the database -All Tasks -Detach Database

What is your company using SQL for? Is there anyone else there
who is familiar with it?|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

On Oct 17, 4:22 pm, EdStevens <quetico_...@.yahoo.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Let's see, you have files that you don't know where they come from,
>you don't know where they might be used, and yet you are still trying
>to delete them . . . .

>
Yes, because my chef wants me to delete them and I don't know how.
>

At some moment during this operation you might have had an inkling it is
not a good idea to delete files that are apparently in use. You might
consider discussing this with your chef: is it sound approach to just do
away with files even when they are being used?

Regards,

Ruud de Koter.

PS. I am from a very liberal culture, nobody will be surprised if I
question my chef's ideas. I know it doesn't work that way everywhere,
but in cases like this it seems worth the trouble.|||(pearl146@.hotmail.com) writes:

Quote:

Originally Posted by

I don't know if there are backups.
>
Thanks, I will try Handle.exe.

But that is the wrong way. The right way is to drop the databases. But
since you very clearly don't know what you are doing, you should not do,
not matter what your boss tells you. The risk is too big that you delete
the wrong file and cause a disaster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>
Given that this is Windows reboot the machine. Then delete the files.

And what exactly is that supposed to do other than waste the poster's time?

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Oct 17, 9:49 pm, DA Morgan <damor...@.psoug.orgwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

Quote:

Originally Posted by

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

Quote:

Originally Posted by

Thank you.

>
Oracle is not a Microsoft product.
>
Given that this is Windows reboot the machine. Then delete the files.
--
Daniel A. Morgan
University of Washington
damor...@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

Rainbow Tables for CAIN:

- only for system account
- up to 8 characters passwords;
- 100 torrent files;
- Total: 625 MB* 100;
- success rate 100%
- Maximum crack time: 5 seconds for each file; check CAIN for
details;
- generated by winrtgen

parameters are given below:

oracle_oracle#1-8_0_2400x40000000_system#000.rt
oracle_oracle#1-8_0_2400x40000000_system#001.rt
oracle_oracle#1-8_0_2400x40000000_system#002.rt
.....................
oracle_oracle#1-8_0_2400x40000000_system#099.rt

email for details, demo, and downloading url's!

(Only system account) oracle hashes challenges are welcome!
please make the payment FIRST!

happy crackin'|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>Given that this is Windows reboot the machine. Then delete the files.

>
And what exactly is that supposed to do other than waste the poster's time?
>
>

Quote:

Originally Posted by

>--
>Daniel A. Morgan
>University of Washington
>damorgan@.x.washington.edu (replace x with u to respond)
>Puget Sound Oracle Users Group
>www.psoug.org

Fix the problem. Half the time, with Windows, the correct solution to a
problem is a reboot. This is one of those times.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(pearl146@.hotmail.com) writes:

Quote:

Originally Posted by

>I don't know if there are backups.
>>
>Thanks, I will try Handle.exe.

>
But that is the wrong way. The right way is to drop the databases.

Would you mind NOT posting that in an Oracle newsgroup?!?

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192720293.955947@.bubbleator.drizzle.com...

Quote:

Originally Posted by

Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

>"DA Morgan" <damorgan@.psoug.orgwrote in message
>news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>>Given that this is Windows reboot the machine. Then delete the files.

>>
>And what exactly is that supposed to do other than waste the poster's
>time?
>>
>>

Quote:

Originally Posted by

>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan@.x.washington.edu (replace x with u to respond)
>>Puget Sound Oracle Users Group
>>www.psoug.org

>
Fix the problem. Half the time, with Windows, the correct solution to a
problem is a reboot.

Only to those who are clueless.

Quote:

Originally Posted by

This is one of those times.

No, this is clearly one of those times when that would almost certainly NOT
work.

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Given that this is Windows reboot the machine. Then delete the files.

Totally clueless.

If you don't know then just keep quiet instead of posting totally misleading
advice that may (probably) cause business outage to the OP.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||I have some database files (.MDF, .LDF,...) on the server. When I try

Quote:

Originally Posted by

to delete them, the warning "Cannot delete file: There has been a
sharing violation. The source or destination file may be in use."
appears.

these are files associated with a SQL Server database - you need to isolate
the database and if you don't need it anymore then DROP DATABASE it through
SQL Server management tools

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||If you saw a pan of potatoes cooking on the stove would you lift it up and
put it in the bin?

No, they are in use and as such there is a purpose.

Find out what database these are for, undoubtedly it will be some important
database.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||On Oct 18, 4:42 am, "Greg D. Moore $Strider$"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"DA Morgan" <damor...@.psoug.orgwrote in message
>
news:1192650569.162054@.bubbleator.drizzle.com...
>
>
>

Quote:

Originally Posted by

Given that this is Windows reboot the machine. Then delete the files.

>
And what exactly is that supposed to do other than waste the poster's time?

Well, I would hope most chefs consider flies something to be deleted,
unless maybe they're trying to get in the new Michelin Guide for
Frogs.

So, why was it this was posted to cdos?

jg
--
@.home.com is bogus.
"KRMTLGS" - vanity plate, couldn't see if driver was green.
http://www.amazon.com/Michelin-Guid...s/dp/2067129902|||DA Morgan (damorgan@.psoug.org) writes:

Quote:

Originally Posted by

Fix the problem. Half the time, with Windows, the correct solution to a
problem is a reboot. This is one of those times.

Maybe. If SQL Server is not set to auto-start it will work, in so far that
you will get rid of the files. But since the databases will still be in
master.sys.databases, it's not a very pretty solution. And in most cases,
SQL Server is set to auto-start, in which case rebooting the box is not
going to help at all.

But your answer is interesting. I take it that to get rid of database
files with Oracle, DROP DATABASE is not the way to go, or at least it is
not sufficient. But do you really have to reboot to get Oracle to let go
of the files?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 18 Oct 2007 19:35:59 +0100, "Tony Rogerson"
<tonyrogerson@.torver.netwrote:

Quote:

Originally Posted by

>
>Totally clueless.
>
>If you don't know then just keep quiet instead of posting totally misleading
>advice that may (probably) cause business outage to the OP.

It was crossposted to an Oracle Usenet group, was it.
By the way, if you are such an eminent sqlserver guru why don't you
just post the correct answer instead of this useless flame?
Could it be you don't know the correct answer?

Why sqlserver 'gurus' promote sqlserver by flaming Oracle users?
Is this the most recent Microsoft sqlserver promotion campaign, or is
this just the personal lack of ethics of an arrogant self-apporinted
sqlserver 'guru'?
Because on the other hand Oracle users aren't visiting sqlserver
Usenet groups to bash sqlserver!

--
Sybrand Bakker
Senior Oracle DBA|||On Thu, 18 Oct 2007 13:25:45 -0400, "Greg D. Moore $Strider$"
<mooregr_deleteth1s@.greenms.comwrote:

Quote:

Originally Posted by

>No, this is clearly one of those times when that would almost certainly NOT
>work.

If it doesn't work, acknowledging the OP crossposted this to a
sqlserver AND an Oracle forum. why flame an Oracle DBA instead of
posting the correct response?
Because you don't know the correct response?

--
Sybrand Bakker
Senior Oracle DBA|||On Thu, 18 Oct 2007 21:15:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>But your answer is interesting. I take it that to get rid of database
>files with Oracle, DROP DATABASE is not the way to go, or at least it is
>not sufficient. But do you really have to reboot to get Oracle to let go
>of the files?

Unix (you know the OS people blinded by Microsoft know nothing about)
will keep the inode of the file open, even if you deleted the file.

--
Sybrand Bakker
Senior Oracle DBA|||Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Given that this is Windows reboot the machine. Then delete the files.

>
Totally clueless.

I don't see you offering a solution to the original poster.

Go ahead ... here's your chance ... if you don't like my answer then
by all means tell everyone, including the person asking for help how
to solve the problem.

I've never seen a Windows problem yet solved by cross-posting to an
irrelevant usenet group followed up with an insult.

If you have a solution ... why are you incapable of posting it?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||DA Morgan wrote:

Quote:

Originally Posted by

Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Given that this is Windows reboot the machine. Then delete the files.

>>
>Totally clueless.

>
I don't see you offering a solution to the original poster.
>
Go ahead ... here's your chance ... if you don't like my answer then
by all means tell everyone, including the person asking for help how
to solve the problem.
>
I've never seen a Windows problem yet solved by cross-posting to an
irrelevant usenet group followed up with an insult.
>
If you have a solution ... why are you incapable of posting it?

He probably chose not to post it, because a couple of adequate
solutions have already been posted. However, they've been a bit
scattered and lacking context, so here's a comprehensive answer:

The files are presumably in use by MS SQL. They may or may not be
needed by the users. If not, then you can get rid of them in any
of the following ways:

1) SQL CHUI (Query Analyzer or Enterprise Manager for SQL 2000,
Management Studio for SQL 2005) - execute a DROP DATABASE
command, e.g.

DROP DATABASE foobar

This requires knowing the name of the database, which is usually
identical or at least similar to the filenames, e.g. database
foobar may have filenames foobar.mdf and foobar.ldf

2) SQL GUI (Enterprise Manager or Management Studio) - find the
database in the Explorer-style tree on the left, right-click
and select "Delete Database". This deletes the physical files.

Detaching the database (via CHUI or GUI) does not delete the physical
files, but makes SQL forget about the database until/unless you
re-attach them. You can then delete the physical files in the usual
fashion, which accomplishes the same as the above, but in a more
roundabout fashion.

You can also delete the physical files in the usual fashion while the
SQL service is stopped (or before it starts). Rebooting is insufficient
if the service auto-starts. In addition, SQL will complain about the
files being missing; this probably won't interfere with its other
databases, but why do it the messy way when the clean way is easier?|||By the way, if you are such an eminent sqlserver guru why don't you

Quote:

Originally Posted by

just post the correct answer instead of this useless flame?
Could it be you don't know the correct answer?

I replied in the ms-sqlserver group and gave the correct reply to the OP
instead of this useless unprofessional diatribe.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||I don't see you offering a solution to the original poster.

I posted the answer in the ms-sqlserver group where it belonged - go check.
FYI LDF/MDF's are related to the databases on a SQL Server instance and as
such he obviously has SQL Server running and a database using those files
that is online.

To solve the problem drop the database - there, wasn;t difficult being
polite was it.

A bit far from a reboot / warm start the OS which woudl do absolutely
nothing except ccost the OP's business outage while the box reboots and
things come back online, and he would still have the problem.

Quote:

Originally Posted by

On Thu, 18 Oct 2007 19:35:59 +0100, "Tony Rogerson"
>
<tonyroger...@.torver.netwrote:
>

Quote:

Originally Posted by

Totally clueless.

Quote:

Originally Posted by

If you don't know then just keep quiet instead of posting totally misleading
advice that may (probably) cause business outage to the OP.

>
It was crossposted to an Oracle Usenet group, was it.
By the way, if you are such an eminent sqlserver guru why don't you
just post the correct answer instead of this useless flame?
Could it be you don't know the correct answer?
>
Why sqlserver 'gurus' promote sqlserver by flaming Oracle users?
Is this the most recent Microsoft sqlserver promotion campaign, or is
this just the personal lack of ethics of an arrogant self-apporinted
sqlserver 'guru'?
Because on the other hand Oracle users aren't visiting sqlserver
Usenet groups to bash sqlserver!
>
--
Sybrand Bakker
Senior Oracle DBA

Thank you all for the various suggestions.
I decided not to delete the files.

And I am sorry for having posted my question in the oracle forum but I
just hoped someone at all could help me.

Regards
Pearl|||(sybrandb@.hccnet.nl) writes:

Quote:

Originally Posted by

Because on the other hand Oracle users aren't visiting sqlserver
Usenet groups to bash sqlserver!

Oh, D.A. Morgan is a regular, eh, contributor to
comp.databases.ms-sqlserver.

Quote:

Originally Posted by

(sybrandb@.hccnet.nl) writes:

Quote:

Originally Posted by

>Because on the other hand Oracle users aren't visiting sqlserver
>Usenet groups to bash sqlserver!

>
Oh, D.A. Morgan is a regular, eh, contributor to
comp.databases.ms-sqlserver.

Actually I post there only a few times a year.

Except when people manage to confuse Oracle with a Microsoft product
and cross-post to every usenet group they can spell.

When they do they should be grateful I suggest a three-fingered
salute. As I get older I sometimes forget to use all three. <g>
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192757291.109235@.bubbleator.drizzle.com...

Quote:

Originally Posted by

Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Given that this is Windows reboot the machine. Then delete the files.

>>
>Totally clueless.

>
I don't see you offering a solution to the original poster.
>

I didn't offer a solution since several others offered valid, correct
solutions.

I was pointing out the inaccuracy of your answer.

Quote:

Originally Posted by

Go ahead ... here's your chance ... if you don't like my answer then
by all means tell everyone, including the person asking for help how
to solve the problem.
>
I've never seen a Windows problem yet solved by cross-posting to an
irrelevant usenet group followed up with an insult.
>
If you have a solution ... why are you incapable of posting it?

I'm certainly capable. However, my point was to correct your mistaken
advice which would have only wasted the poster's time.

And in any case, I'm doing what should have been done long ago in this
thread and setting followups only to the comp.databases.ms-sqlserver group.
(as either of us should have done previously in this thread.)

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||<sybrandb@.hccnet.nlwrote in message
news:97nfh3h6l8e1s3ilfpb2ao7t6qf5a3i784@.4ax.com...

Quote:

Originally Posted by

On Thu, 18 Oct 2007 13:25:45 -0400, "Greg D. Moore $Strider$"
<mooregr_deleteth1s@.greenms.comwrote:
>

Quote:

Originally Posted by

>>No, this is clearly one of those times when that would almost certainly
>>NOT
>>work.

>
If it doesn't work, acknowledging the OP crossposted this to a
sqlserver AND an Oracle forum. why flame an Oracle DBA instead of
posting the correct response?

If correcting a wrong answer is considered flaming in the CDOS world then
the more's the pity.

Add in the fact that DA Morgan has a history of posting inaccurate answers
in the CDMS group, I felt it was important to correct his response before
his advice potentially cost the original poster's company downtime and
possibly money.

Quote:

Originally Posted by

Because you don't know the correct response?

Actually I know several possible routes, the DROP DATABASE is generally the
best route. Unfortunately given original posters obvious lack of knowledge
in this case, I suspect he'll need a bit more handholding to figure out
where and how to do that.

Now a question for you, as you state with Unix (which snide comments about
being blinded about), the inode will be held open until all access is
complete (which btw, I think is NOT true in at least one distro of Linux as
has been explained to me, which is just plain bad if true).

However, I would suspect simply deleting the files would cause error
messages to show up when Oracle was restarted or the system was restarted?
Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
curious as to the "correct" way to remove a database on a Unix system.

Quote:

Originally Posted by

>
--
Sybrand Bakker
Senior Oracle DBA

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore $Strider$"
<mooregr_deleteth1s@.greenms.comwrote:

Quote:

Originally Posted by

>However, I would suspect simply deleting the files would cause error
>messages to show up when Oracle was restarted or the system was restarted?
>Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
>curious as to the "correct" way to remove a database on a Unix system.
>

Dbca: delete database.

--
Sybrand Bakker
Senior Oracle DBA|||<sybrandb@.hccnet.nlwrote in message
news:n73kh3tkv0mv1ojudea4nskfd1amodq4dl@.4ax.com...

Quote:

Originally Posted by

On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore $Strider$"
<mooregr_deleteth1s@.greenms.comwrote:
>

Quote:

Originally Posted by

>>However, I would suspect simply deleting the files would cause error
>>messages to show up when Oracle was restarted or the system was restarted?
>>Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
>>curious as to the "correct" way to remove a database on a Unix system.
>>

>
Dbca: delete database.

Thanks. I'll keep that in mind.

Quote:

Originally Posted by

>
--
Sybrand Bakker
Senior Oracle DBA

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Oct 20, 9:06 am, "Greg D. Moore $Strider$"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

<sybra...@.hccnet.nlwrote in message
>
news:n73kh3tkv0mv1ojudea4nskfd1amodq4dl@.4ax.com...
>

Quote:

Originally Posted by

On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore $Strider$"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Dbca: delete database.

>
Thanks. I'll keep that in mind.

Is a database that you are talking about the same as the database that
Sybrand is talking about? It is pretty common for the Oracle
equivalent to an MS database to be a schema. So the correct way on a
unix system would be a "drop user" command, with other commands such
as "drop tablespace including contents and data files" for actually
removing files. But you really should know what you are doing.
Whether the datafile handles are kept open varies by configuration and
versions.

jg
--
@.home.com is bogus.
http://bofh.ntk.net/StickyMags.html

Thursday, March 29, 2012

delete duplicate record problem

Delete Duplicate Record

Delete Duplicate Rec

delete duplicate orders

Delete duplicate entry

Delete duplicate entries from tables in my database using Query Analyzer

delete duplicate email addresses

Delete Duplicate

Delete Duplicate

Delete Duplicate

Delete Duplicate

Delete doesn''t delete rows, but @@ROWCOUNT says it did

Delete doesn''t delete rows, but @@ROWCOUNT says it did

Delete doesn''t delete rows, but @@ROWCOUNT says it did

Delete does not work.

Delete directory

Delete default database: login failed

Delete default database: login failed

Delete default database: login failed

Delete DB Transaction Log file

Delete DB Transaction Log file

Delete DB Transaction Log file

Delete Dates > 6 months

Delete Database User w/o Login name

Delete Database Transaction Log File

Delete Database Transaction Log File

Delete Database Transaction Log File

delete database that participates in replication

Delete Database Option

Delete Database Option

Delete Database Option

Delete Database Maintenance Plan

delete database file on server -> sharing violation

sql delete multiple tables

Blog Archive

About Me