DENY permission,SQL server: delete

Showing posts with label delete. Show all posts

Thursday, March 29, 2012

deleting table phisically

hello
I delete a table from a DataBase in enterprise manager(drop table) ,
but it is not deleted phisically and hard's free space does not
increas.
How can I delete table phisically?
thanksYou need to shrink the database to recover disk space on the drive...
"nsh" <nsh5776@.yahoo.com> wrote in message
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||How do you know the table is not deleted phisically? because of the database
file (.mdf) file?
Most likely you just vacated space within the .mdf file.
Back up the database, then try to shrink it.
using Enterprise manager, Right mouse on the database -> All Tasks ->
Shrink dbase
You can also see how much space is being used in the .mdf file grafically
using Enterprise manager, Right mouse on the database -> View -> Task
Pad
Hope this helps
"nsh" <nsh5776@.yahoo.com> escribió en el mensaje
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||thanks for helping.
Before delete table .mdf & .ldf size are the same after delete table
(each table size is about 5GB ).
when I make Data Base ,I set Autoshrink .Is not it enough?
please help me ...
thanks
"Marcelo" <marcelo.no@.spam.santiago.cl> wrote in message news:<#YAUSvkRDHA.1324@.TK2MSFTNGP11.phx.gbl>...
> How do you know the table is not deleted phisically? because of the database
> file (.mdf) file?
> Most likely you just vacated space within the .mdf file.
> Back up the database, then try to shrink it.
> using Enterprise manager, Right mouse on the database -> All Tasks ->
> Shrink dbase
> You can also see how much space is being used in the .mdf file grafically
> using Enterprise manager, Right mouse on the database -> View -> Task
> Pad
> Hope this helps
> "nsh" <nsh5776@.yahoo.com> escribió en el mensaje
> news:534bf457.0307091048.714766fe@.posting.google.com...
> > hello
> > I delete a table from a DataBase in enterprise manager(drop table) ,
> > but it is not deleted phisically and hard's free space does not
> > increas.
> > How can I delete table phisically?
> > thanks|||> when I make Data Base ,I set Autoshrink .Is not it enough?
No, because it doesn't auto-shrink every single time the database changes --
this would cause a performance nightmare.

Deleting Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
Hi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake
|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
>news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com.. .
>
sql

Deleting Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakeHi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
> news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
>

Deleting Table

Deleting stored procedures through query analyzer

Hi All!

I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day

I've got everything except removing the stored procedure so I will really appreciate the help.

Thank you all!

Rijckewaert wrote:

Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database?

Transact-SQL ... here Oracle is notwelcome

the actual traditional code is

IF OBJECT_ID('[schema/owner].[object]') IS NOT NULL

DROP PROCEDURE [schema/owner].[object];

Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!

this "design" is not clear to me... why would you always drop an object to be recreated (if the inner code remains the same)?

is this becouse of the well known "orphaned users" problem?
this problem (perhaps the problem you are experimenting) is involved when a "restored" database user(s) is no longer mapped to a corresponding server's standard SQL Server login..
in SQL Server 2000 this means the relationship between master.dbo.syslogins is broken with database.dbo.sysusers...
actually the JOIN on master.dbo.syslogins.sid ~ database.dbo.sysusers.sid

if this is the case, you do not need to delete the object and the user... you just need to resync it/them, via the sp_change_users_logins system stored procedure http://msdn2.microsoft.com/en-us/library/ms174378.aspx ... please have a look at http://msdn2.microsoft.com/en-us/library/ms175475.aspx as well..

if this is not the case... please expand

regards

deleting sql server 2005 deployed packages?

Hi there

This is probably a dead easy question.
I have deployed 3 SSIS packages to sql server, but now i cannot find any option anywhere to delete them?
How do i delete SSIS packages from the SQL Server instance they were deployed to ?

ThanxRight click on it in Management Studio and select the Delete menu item.|||Hi Kirk

Ok my main problem was that i could not find them , but i realised i have to register it as a integration services server, then connect to it , correct?

Ok first question i have sql2000 and sql2005 instances running on my machine, when i register the SSIS server , it will not accept seanlo\sqlserver2005 , it says it does not support multiple instances, so therefore i have to register it as just seanlo, problem is that is my sql 2000 instance ? So i am not sure how to register the SSIS server as it will not accept the name of my 2005 instance?

If i try simply enter SQLSERVER2005 as the instance, it get the following error:
Connect to SSIS Service on "SQLSERVER2005" failed, The RPC server is unavailable?

Now i am not sure how SSIS servcies work in SQL Server 2005, i have chcked that the SQL Server2005 and SSIS services are started and running on my machine?

Thanx again for the help!|||Sean,
Kirk has posted on this: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

It tells you all you need to know.

-Jamie|||Great ! Worked like a charm, thanx so much for you time Jamie|||Oh, and, you're welcome for the post too Sean. :)|||

KirkHaselden wrote:
Oh, and, you're welcome for the post too Sean. :)

Oi, I'm claiming all the credit for this one :)

Deleting SP syntax check please...

I'm trying to write a SP that will delete records from a few tables and then
count to see if any records are for some unknown reason, left over. The
goal is to have the SP return "0" upon successful deletions (used and called
from asp.net code). It it return a value greater than 0 than I know
something went wrong.
Pasted below is my attempt. I keep getting a syntax error near the word
"DELETE" in the first delete command.
What am I doing wrong? Before someone suggests I create relationships
between all these tables, the answer is I can't. I'm working with another
"old-school" developer who doesn't like them and likes to do all his
relationships "programmatically" thru code. My hands are tied so I need to
delete from each table separately.
THANKS!
CREATE PROCEDURE sp_DeletelApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE ID = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count(ID) As Applications FROM Applications WHERE ID = @.intApplicationID) +
(SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GOGroove
> DELETE FROM Applications WHERE ID = @.intApplicationID
Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
> I'm trying to write a SP that will delete records from a few tables and
> then count to see if any records are for some unknown reason, left over.
> The goal is to have the SP return "0" upon successful deletions (used and
> called from asp.net code). It it return a value greater than 0 than I
> know something went wrong.
> Pasted below is my attempt. I keep getting a syntax error near the word
> "DELETE" in the first delete command.
> What am I doing wrong? Before someone suggests I create relationships
> between all these tables, the answer is I can't. I'm working with another
> "old-school" developer who doesn't like them and likes to do all his
> relationships "programmatically" thru code. My hands are tied so I need
> to delete from each table separately.
> THANKS!
>
> CREATE PROCEDURE sp_DeletelApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE ID = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count(ID) As Applications FROM Applications WHERE ID => @.intApplicationID) +
> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>|||Thanks but no luck. I enclosed all my "ID's" in brackets and still the same
error when checking the syntax:
CREATE PROCEDURE spDeleteCapitalApplication
(@.intApplicationID Integer)
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
SELECT
(SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =@.intApplicationID) +
(SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
=@.intApplicationID) +
(SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
=@.intApplicationID) As RecordsLeft
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used and
>> called from asp.net code). It it return a value greater than 0 than I
>> know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>|||:-))),Now I see , you have missed AS in the stored procedure
CREATE PROCEDURE spDeleteCapitalApplication
@.intApplicationID Integer
AS
DELETE FROM Applications WHERE [ID] = @.intApplicationID
DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
"Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
> same error when checking the syntax:
>
> CREATE PROCEDURE spDeleteCapitalApplication
> (@.intApplicationID Integer)
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
> SELECT
> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] => @.intApplicationID) +
> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
> =@.intApplicationID) +
> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
> =@.intApplicationID) As RecordsLeft
> GO
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left over.
>> The goal is to have the SP return "0" upon successful deletions (used
>> and called from asp.net code). It it return a value greater than 0 than
>> I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the word
>> "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do all
>> his relationships "programmatically" thru code. My hands are tied so I
>> need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>|||D'oh!
(slaps forehead)
Thanks!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8PDSaXXGHA.4620@.TK2MSFTNGP04.phx.gbl...
> :-))),Now I see , you have missed AS in the stored procedure
> CREATE PROCEDURE spDeleteCapitalApplication
> @.intApplicationID Integer
> AS
> DELETE FROM Applications WHERE [ID] = @.intApplicationID
> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>
>
> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:O43pKWXXGHA.196@.TK2MSFTNGP04.phx.gbl...
>> Thanks but no luck. I enclosed all my "ID's" in brackets and still the
>> same error when checking the syntax:
>>
>> CREATE PROCEDURE spDeleteCapitalApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count([ID]) As Applications FROM Applications WHERE [ID] =>> @.intApplicationID) +
>> (SELECT Count([ID]) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count([ID]) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%238pYMQXXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> Groove
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> Perhaps DELETE FROM Applications WHERE [ID] = @.intApplicationID
>> "Groove" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
>> news:%233Jc6LXXGHA.4716@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to write a SP that will delete records from a few tables and
>> then count to see if any records are for some unknown reason, left
>> over. The goal is to have the SP return "0" upon successful deletions
>> (used and called from asp.net code). It it return a value greater than
>> 0 than I know something went wrong.
>> Pasted below is my attempt. I keep getting a syntax error near the
>> word "DELETE" in the first delete command.
>> What am I doing wrong? Before someone suggests I create relationships
>> between all these tables, the answer is I can't. I'm working with
>> another "old-school" developer who doesn't like them and likes to do
>> all his relationships "programmatically" thru code. My hands are tied
>> so I need to delete from each table separately.
>> THANKS!
>>
>> CREATE PROCEDURE sp_DeletelApplication
>> (@.intApplicationID Integer)
>> DELETE FROM Applications WHERE ID = @.intApplicationID
>> DELETE FROM CapitalBudgets WHERE ApplicationID = @.intApplicationID
>> DELETE FROM Schedules WHERE ApplicationID = @.intApplicationID
>> SELECT
>> (SELECT Count(ID) As Applications FROM Applications WHERE ID =>> @.intApplicationID) +
>> (SELECT Count(ID) As TotBudgets FROM CapitalBudgets WHERE ApplicationID
>> =@.intApplicationID) +
>> (SELECT Count(ID) As Schedules FROM Schedules WHERE ApplicationID
>> =@.intApplicationID) As RecordsLeft
>> GO
>>
>>
>>
>

Deleting SP syntax check please...

DELETING ROWS with REFERENTIAL INTEGRITY

hi there!

im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?

i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?

thank...

1. You can use sys.foreign_keys view to query and follow the data constraints in your table

2. You may try to use cascading referential integrity constraints.

By using cascading referential integrity constraints, you can define the

actions that the SQL Server 2005 takes when a user tries to delete or update a

key to which existing foreign keys point.

The REFERENCES clauses of the CREATE TABLE and

ALTER

TABLE statements support the ON DELETE and ON UPDATE clauses:

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

]

|||

hi carlop!

thank you for reply.... well the table in our database are not set to ON DELETE CASCADE ON due to security reason. so there is no way for me to delete the rows easily, i guess i should track all the tables for their foreign keys and dependent tables :-(

thanks, novelle

|||

hi there...

do u want to delete data from ur selected tables, and dont want their parent tables(PK tables) , to give foreign key errors......if thats the case, u can disable the foreign keys..perform the operation, then enable them again..

else if u want to delete from primary table first...or want to know the related tables, either use database digrams...or maybe this query will help u..

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a

inner join sys.sysobjects b on b.id = a.parent_object_id

inner join sys.sysobjects c on c.id = a.referenced_object_id

|||

hi nitin!

thank u for ur quick reply!

the thing is, im copying data from server to server, after copying the data i wanted to deleted this rows i've copied to the source database and offcourse i want to delete correctly.

i dont want to disable the foreignkeys because if my delete script is wrong , i wont able to delete the data correctly.

by the way does this script works on the SQL 2000? because i've tried it and it doesnt work.

thanks novelle.

|||

hi...for 2000 it'll be like

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a -- for this pls check the table sysconstraints/sysreferences...i dont quite remember the fields..

inner join sysobjects b on b.id = a.parent_object_id

inner join sysobjects c on c.id = a.referenced_object_id

|||A sql 2k compliant view of foreign keys and primary keys is the following:

CREATE view dbo.foreign_keys as
select cast(f.name as varchar(255)) as fk_name
, r.keycnt
, cast(ft.name as varchar(255)) as foreign_table
, cast(f1.name as varchar(255)) as foreign_col1
, cast(f2.name as varchar(255)) as foreign_col2
, cast(pt.name as varchar(255)) as primary_table
, cast(p1.name as varchar(255)) as primary_col1
, cast(p2.name as varchar(255)) as primary_col2
from sysobjects f
inner join sysobjects ft on f.parent_obj = ft.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects pt on r.rkeyid = pt.id
inner join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid
inner join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid
left join syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid
left join syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid
where f.type = 'F'
GO

CREATE view dbo.primary_keys as
select distinct
tbl.name TableName,
constrId.name PkName,
col.name ColName,
sik.keyno,
case when ix.indid = 1 then 1 else 0 end IsClustered
from sysobjects tbl
join sysconstraints constr on ( tbl.id = constr.id and tbl.xtype = 'U' and constr.status & 0x0001 = 0x0001 )
join sysobjects constrId on constrId.parent_obj = tbl.id and constrId.xtype = 'PK'
join sysindexes ix on constrId.name = ix.name and ix.id = tbl.id
join sysindexkeys sik on sik.id = tbl.id and sik.indid = ix.indid
join syscolumns col on col.id = tbl.id and sik.colid = col.colid
GO

Deleting rows with OPENXML

Hi there,
I'm trying to delete some rows from a table using an xml document (in
memory) and OpenXML in SQL Server. I have the following code, but it will
only delete the first record. Any ideas?
CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
@.CompanyHeirarchy nvarchar(4000)
AS
SET NOCOUNT ON;
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
DELETE FROM CompanyBusinessUnit
WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
(CompanyBusinessUnitID Integer))
EXEC sp_xml_removedocument @.hDoc
GO
Thanks,
WesTry,
DELETE
CompanyBusinessUnit
FROM
CompanyBusinessUnit
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:

> Hi there,
> I'm trying to delete some rows from a table using an xml document (in
> memory) and OpenXML in SQL Server. I have the following code, but it will
> only delete the first record. Any ideas?
> CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
> @.CompanyHeirarchy nvarchar(4000)
> AS
> SET NOCOUNT ON;
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
> DELETE FROM CompanyBusinessUnit
> WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
> FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
> (CompanyBusinessUnitID Integer))
> EXEC sp_xml_removedocument @.hDoc
> GO
>
> Thanks,
> Wes|||Hi there,
Thanks for answering my post so quickly. Unfortunately your solution did
the same thing as the ones I've tried. It simply deleted the first row
matched from the XML. I have posted my XML below, maybe there is something
wrong with it.
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
Thanks,
Wes
"Alejandro Mesa" wrote:
> Try,
> DELETE
> CompanyBusinessUnit
> FROM
> CompanyBusinessUnit
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
>
> "Wes" wrote:
>|||Try,
DELETE
a
FROM
CompanyBusinessUnit as a
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:
> Hi there,
> Thanks for answering my post so quickly. Unfortunately your solution did
> the same thing as the ones I've tried. It simply deleted the first row
> matched from the XML. I have posted my XML below, maybe there is somethin
g
> wrong with it.
> <NewDataSet>
> <DeletedNodes>
> <CompanyBusinessUnitID>17</CompanyBusinessUnitID>
> <CompanyBusinessUnitID>18</CompanyBusinessUnitID>
> </DeletedNodes>
> </NewDataSet>
> Thanks,
> Wes
> "Alejandro Mesa" wrote:
>|||I thought the problem was with the DELETE statement, but no, it was the
"SELECT ... FROM OPENXML ..." that was selecting just the first row. Can you
try changing this select by:
Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3
AMB
"Alejandro Mesa" wrote:
> Try,
> DELETE
> a
> FROM
> CompanyBusinessUnit as a
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
> "Wes" wrote:
>|||Your firts post will also work if you change the layout of the xml document
to:
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
</DeletedNodes>
<DeletedNodes>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
AMB
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Excellent! Thank you very much as this one works perfectly. Now I'll have
to analyze the code and see what I was doing differently.
Thanks for your help.
Wes
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>

Deleting rows from many tables

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:SoderquistString %>"DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID"SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @.State)"><DeleteParameters><asp:ParameterName="UserID"Type="Int32"/></DeleteParameters><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="state"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID

DELETE FROM [photo] WHERE [UserID] = @.UserID;

DELETE FROM [album] WHERE [UserID] = @.UserID;

DELETE FROM [comment] WHERE [UserID] = @.UserID;

...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

Try this. I tested for two tables

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID;DELETE FROM [photo] WHERE [UserID] = @.UserID;DELETE FROM [album] WHERE [UserID] = @.UserID;DELETE FROM [comment] WHERE [UserID] = @.UserID"

|||

Thanks for the info. I've tried that already and I still get an error:

Invalid column name 'UserID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'UserID'.

Any suggestions?

|||

Do you have UserID column for all tables you are going to delete?

Since I tested the syntax for only two tables, I will test for more tables to confirm whether the syntax works for multiple tables.

|||

This DeleteCommand works on the dummy test tables in SQL Server 2005:

DeleteCommand="DELETE FROM [T1] WHERE [ID1] = @.ID;DELETE FROM [T2] WHERE [ID1] = @.ID;DELETE FROM [T3] WHERE [ID1] = @.ID;DELETE FROM [T4] WHERE [ID1] = @.ID;DELETE FROM [T5] WHERE [ID1] = @.ID"<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters>

But if you click delete rows too fast, you may get error message for the parameter. You should be able to delete from a stored procedure with error checking from there.

|||

Limno, thanks and I appreciate the help you have given me. I was looking over my tables and noticed that there is a difference in one of them. I'm going to write out the four tables and a few columns that I have within them and hopefully this will help clear a few things up that I'm having trouble with:

alumni - UserID (PK), FirstName, LastName, UserName

photo - PhotoID (FK), AlbumID, PhotoPath

album - AlbumID (FK), UserID, PhotoPath, AlbumName

comments - CommentID (FK), UserID, Comment, UserName, CommentDate

What I have is one primary_key, which is UserID and the other three are foreign_keys (PhotoID, AlbumID, and CommentID). All of the foreign keys have UserID in them except for the photo table because it associates with the album table that has UserID in it. That was the one difference that I forgot about.

So is it possible, with the way I have this set up for me to be able to delete a user from the alumni table along with the other information in the other tables that are associated with that user? I can delete a user if there are no albums, photos or comments connected with that person, but it's when they have something connected to them a problem comes up because of certain constraints.

Thank you in advanced!

|||

Hello:

I tested with the following DELETECommand:

DeleteCommand="DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID)); DELETE FROM [comments] WHERE [userID] = @.userID;DELETE FROM [alumni] WHERE [userID] = @.userID;DELETE FROM [album] WHERE [userID] = @.userID"

It seems that the cascading delete through a trigger would be a perfect fit for your case. However, I rearrange the delete action sequence for your case in this DeleteCommand. I tested with success. But you need to test on your data to make sure it works properly. If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion.

|||

It worked! Thank you for your time, I really appreciate it. It looks like my problem was with the way I was deleting the information from the photo table and how it was related to the albums. This peace of code here...

DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID));

...did the trick.

Just for my understanding, what did you mean by "If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion"? Thanks again!

|||

The code works but you have no way to know if something wrong with it during deletion. I did run into a few times during my test. That is what I mean. If the project becomes critical, you can go with the trigger and stored procedure. Or You can use your own error catch logic from code behind. Just my thought for your situation. I want to know what will happen if there is an error for learning purpose.

I am glad this hack works for you now. If you are interested, you can search for the topic on cascading deleting. The constrains on the foreign keys are very usful to maintain the integrity of your data set.

deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

Deleting rows

Hi there,
can you help me?
I'm trying to delete rows in a single table.
When i exec select , rows returne are 6098 (right number)
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
When i try to delete that records rows deleted are 13708 (wrong)
DELETE TBL_CHARACTERbackup WHERE FLD_USERID IN
(
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
)
Donno why, please help
TksThe DELETE statement will delete every row in your table which has a
FLD_USERID value that is duplicated, whereas the SELECT will only show
ONE row per FLD_USERID.
Possibly this is what you intended:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level < TBL_CHARACTERbackup.fld_level)
but that's only a guess.
David Portas
SQL Server MVP
--|||
"David Portas" ha scritto:

> The DELETE statement will delete every row in your table which has a
> FLD_USERID value that is duplicated, whereas the SELECT will only show
> ONE row per FLD_USERID.
> Possibly this is what you intended:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level < TBL_CHARACTERbackup.fld_level)
> but that's only a guess.
> --
> David Portas
> SQL Server MVP
Ok what i need is to delete all fld_userid with lower fld_level
just to have
only one records for each fld_userid with highest fld_level
How can i do?
Please help.
> --
>|||You haven't told us what the key of your table is. Assuming
(fld_userid, fld_level) is unique then just turn around the sign in my
first attempt:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level > TBL_CHARACTERbackup.fld_level)
(still untested)
If (fld_userid, fld_level) isn't unique that means there may be more
than one row with the same maximum value of fld_level so you will have
to add some other criteria to the WHERE clause. If you need more help
then the following article explains the best way to describe the
details of your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
Do you have any say over naming conventions in your design? Why the
useless tbl_ and fld_ prefixes? They tell us nothing (we know what
tables and columns are) and just make the names harder to read. Also,
the usual convention is to use the term "column" not "field" when
referring to relational data. Some people find this distinction more
important than others and associate the two terms with totally
different concepts but almost everyone loathes to see prefixes on
column names.
Hope this helps.
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> You haven't told us what the key of your table is. Assuming
> (fld_userid, fld_level) is unique then just turn around the sign in my
> first attempt:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level > TBL_CHARACTERbackup.fld_level)
> (still untested)
> If (fld_userid, fld_level) isn't unique that means there may be more
> than one row with the same maximum value of fld_level so you will have
> to add some other criteria to the WHERE clause. If you need more help
> then the following article explains the best way to describe the
> details of your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Do you have any say over naming conventions in your design? Why the
> useless tbl_ and fld_ prefixes? They tell us nothing (we know what
> tables and columns are) and just make the names harder to read. Also,
> the usual convention is to use the term "column" not "field" when
> referring to relational data. Some people find this distinction more
> important than others and associate the two terms with totally
> different concepts but almost everyone loathes to see prefixes on
> column names.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
I'll explain :
each row_USERID corrisponds more records
ex
FLD_USERID FLD_LEVEL CHARACTERS
daniele 1 PIPPO
daniele 27 PLUTO
daniele 37 Paperino
I must keep in table
FLD_USERID FLD_LEVEL CHARACTERS
daniele 27 PLUTO
daniele 37 Paperino
That means only 2 records for FLD_USERID wiyh 2 higher FLD_LEVEL levels
Please help
> --
>|||Try:
DELETE FROM TBL_CHARACTERbackup
WHERE fld_level =
(SELECT MIN(fld_level)
FROM TBL_CHARACTERbackup AS T
WHERE fld_userid = TBL_CHARACTERbackup.fld_userid)
David Portas
SQL Server MVP
--

deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

DELETING ROW DATA

Hi all,
How to delete one row of data from a table?
--
TSdelete from mytable where ...
"TS" <TS@.discussions.microsoft.com> wrote in message
news:85B4D023-FDA2-4B8A-B5DA-60EFE0268176@.microsoft.com...
> Hi all,
> How to delete one row of data from a table?
> --
> TS|||Thanks, how about if I want to delete all rows in the table?
--
TS
"JT" wrote:

> delete from mytable where ...
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:85B4D023-FDA2-4B8A-B5DA-60EFE0268176@.microsoft.com...
>
>|||delete from mytable
truncate table mytable
Take a look though at Truncate Table in Books on-line.
There are advantages to using this method but also some restrictions.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:39A8C7D5-282F-47C6-ABF5-C301303C8C5D@.microsoft.com...
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>|||Delete from Yourtablename
"TS" wrote:
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>|||Does the DBA know you are doing this?
"TS" <TS@.discussions.microsoft.com> wrote in message
news:39A8C7D5-282F-47C6-ABF5-C301303C8C5D@.microsoft.com...
> Thanks, how about if I want to delete all rows in the table?
> --
> TS
>
> "JT" wrote:
>

Deleting Reports in Report Server without stopping Report Service

I'd just like to know if it's okay to delete reports in the report server without having to stop the reporting service or without having to restart it? Has anybody tried this?

Thanks.SUre this is one of the administrative tasks that can be done via the Web UI.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Deleting reports

So I've got a couple of reports deployed in reporting services 2005 and
Well I want to delete them but I have no clue how. I was wondering if
anybody could either
A) walk me through how to do it.
B) point me in the direction of some sites that explain how to do it
(I'm having no luck with google this morning)
Thanks a ton for the help.
MathiasWhere do you want to delete them from?
The server or your development project?
If you want to delete them from your server, just use Report Manager. Click
on "Show Details". Mark the ones you want to delete, and click on the Delete
button.
You'll need to be Content Manager to do it though. If you don't see the
Delete button, you don't have the rights to do it, and need to log on as a
different user.
If you want to delete them from you development project, you should be able
to right click the report in the management tool, and choose Remove. This
will let you choose between Remove or Delete. Remove means to delete the
reference in your project, but still leave the rdl file in the project
folder. Delete means delete the reference and the rdl-file completely.
Kaisa M. Lindahl Lervik
"Mathias" <mathias.helbach@.gmail.com> wrote in message
news:1141308861.584476.110140@.u72g2000cwu.googlegroups.com...
> So I've got a couple of reports deployed in reporting services 2005 and
> Well I want to delete them but I have no clue how. I was wondering if
> anybody could either
> A) walk me through how to do it.
> B) point me in the direction of some sites that explain how to do it
> (I'm having no luck with google this morning)
>
> Thanks a ton for the help.
> Mathias
>|||Kaisa
Thanks for the help. That was what I was looking for.

Deleting replication, leaves data in distribution database

Hi,

>From a publisher I replicate 3 databases to a distributor/subscriber
machine. On all of them i have MS SQL 2005.
When i delete one of the replications by running
publisher => sp_dropsubscription
subscriber/distributor=>sp_removedbreplication
publisher => sp_removedbreplication
subscriber/distributor =>sp_subscription_cleanup
On the publisher and subscriptions databases all replication things
are removed.
However when looking with the replication monitor the deleted
replication is visible with a big red cross. Also the jobs are still
in the job list of the distributor!
When removing everything by dropping the distribution database all is
cleared. I know that in SQL 2000 the distributor would also be
cleared. Has this changed?
Is this a bug in SQL2005 or am i forgotting something (i have searched
a lot already, but cannot find anything)?
Hopefully someone can help me?
Marcel
I'd recommend using sp_droppublication which'll clean up the jobs as well.
sp_removedbreplication is something I run only occasionally to clean up any
remaining orphaned objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||On 1 feb, 10:28, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I'd recommend using sp_droppublication which'll clean up the jobs as well.
> sp_removedbreplication is something I run only occasionally to clean up any
> remaining orphaned objects.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.
Thanks Paul,
This looks much better! I finalized with
EXEC sp_replicationdboption
@.dbname = @.publicationDB,
@.optname = N'publish',
@.value = N'false';
This should remove the publication objects...
Marcel

Deleting records.

Anyone have ideas on how to delete records (see code below). I keep on getting the error message:

"The column prefix 'employee' does not match with a table name or alias name used in the query."

All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?

delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.

Thanks again.sql

Thursday, March 29, 2012

deleting table phisically

Deleting Table

Deleting Table

Deleting Table

Deleting Table

Deleting stored procedures through query analyzer

deleting sql server 2005 deployed packages?

Deleting SP syntax check please...

Deleting SP syntax check please...

DELETING ROWS with REFERENTIAL INTEGRITY

Deleting rows with OPENXML

Deleting rows from many tables

Invalid column name 'UserID'.

deleting rows based on nvarchar data

Deleting rows

deleting rows

DELETING ROW DATA

Deleting Reports in Report Server without stopping Report Service

Deleting reports

Deleting replication, leaves data in distribution database

Deleting records.

DENY permission,SQL server

Blog Archive

About Me