DENY permission,SQL server: March 2012

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 Srv 2K5 publication with no Agents

I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.

The error is "The specified @.job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)

Is there any way to remove publication?

Help will be greatly appreciated!

Vladimir

Do you have trouble in dropping subscription/publication? Error looks like to happen when you drop a remote pull subscription.

Anyway, you may call SP directly to drop the subscription, then publication. Please refer to BOL.

Similar call will be like:

use [PubDB]
exec sp_dropmergesubscription

exec sp_mergesubscription_cleanup

exec sp_dropmergepublication

Hope it will be helpful.

Thanks

Yunjing

|||

Hello,

I am dropping xactional publication, not merge subscription. Normally, it is not a problem, but in this case all three agent jobs are gone.

When I try to delete it, I am getting message about absent snapshot agent, Error 20678 "|Could not find the regular snapshot job for the specified publication <publication name>. Supply either @.job_id or @.job name to idnetify the job."

But the jobs like I said is gone.

I have tried sp_droppublication. Got error: "Message 14013, This database is not enabled for publication", which does not make sense since publication is there already.

Thanks for trying.

Vladimir

|||

Hello, Vladimir,

I think meta data got messed up somehow. Could you check whether publications are still there?

please call the below two sql to see if you can get any thing.

On publisher server, select * from [PublicationDB]..syspublications

On distributor server, select * from [distributionDB]..MSpublications

Thanks

Yunjing

|||

Hi Yunjing,

The syspublications does not have any rows.

MSpublications does not list this "orphaned" publication, but it lists all other "healthy" publication I created.

Yesterday I backed up and restored the publisher database. There is the switch during restore 'keep_publication' which gave me hope that

if I will not set it during restore that will break link b/w db and publication. But after restore error still percists.

R-clicking on publication allows to create publication drop script. I ran it, and against every article in publication the message was "the publication does not exist'. This is while I see that publication in GUI.

Also, r-clicking, I tried to start Snapshot agent to create snapshot - the absent snapshot may be the root of the problem.

I guess it was erase together with erasure of snapshot agent.

It failed at the end with error "Cannot insert NULL into column "publisher_database_id", table "distribution.dbo.MSrepl_transactions".

Thanks Yunjing for keeping up.

Vladimir

sql

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 Spaces!

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.Try this query on your table:

UPDATE [Table 1] SET Loc_Code = Replace([Loc_Code], (Chr(32)), "");

This should get rid of that space.|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)
!!Cough!!Bullsht!!Cough!!|||aw, c'mon, nocopy, be nice, show the poor guy the right way

since this is the SQL forum, for the SQL language and not any specific implementation thereof, i shall give an SQL solution

estefex, here's your join --
select Table1.foo
, Table2.bar
from Table1
inner
join Table2
on substring(Table1.Loc_Code from 1 for 1)
|| substring(Table1.Loc_Code from 3 for 5)
= Table2.Loc_Code|||Mmm, sorry r937 I'll tone it down. ;)

dj982020 already gave a solution, the replace thing it is.

By the way, your code won't run on my DB. The replace will though.

ss659 No hard feelings mmmkay? ;)|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:|||r937, your code is not robust also.
The replace will plow through as many spaces as you throw at it.
Well I would use it in a join instead of updatin' cause maube the other table wants it this way.

I feel mighty feisty today.|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:
You being bad too.

trim takes one character only 'tsup with dat?
rtrim ltrim kicks bigger A$$.|||maybe estefex's database does not have the replace or trim functions, did you ever consider that?

do you even know what database estefex is running?

no

therefore standard sql is the best solution

and trim will not remove a space from inside a value|||and trim will not remove a space from inside a value

Cough!!True!!Cough!!

Now I KNOW you know standard SQL better than me.
Is this the best standard SQL can do then? :eek:|||well, i don't really want to get into a discussion of whether standard sql is any good or not, or "the best it can do"

all i wanted to do was point out that in this forum, standard sql should be used

especially if the poster does not indicate which database system they're using

i mean, if somebody wanted an oracle solution, there's a forum for oracle

if somebody wanted an access solution, there's a forum for access

if somebody wanted an sql server solution, there's a forum for sql server

if somebody wanted a mysql solution, there's a forum for mysql

what do you think this forum is for?|||R937
Chill, chill. You right. :cool:

Maybe folks ought to mention what DB or DBs they are running.
Then there would be no confusion.
Who needs to read the crystal reports err.. bowl, right? ;)|||!!Cough!!Bullsht!!Cough!!

Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!|||Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!
Hah, if you got a high post count you think you are the shit? :D
Maybe you ought to start reading the question before you respond.
Now, I am not going to plow through the millions of your posts and see if they are of the similar quality as the one here. And unlike you I am not going to call you names.

By the way, did you ask r937 if he was offended by my posts? I think not.
Have a nice life.
Apologies for the Idiot will be accepted. :rolleyes:

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 semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

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 Publisher and from Subscriber

Hello
I have an issue on Deleting rows on Publisher.
I want to keep a 2 months old transaction data both on my Publisher
and on the Subscribers as well,
so eatch night a process is run on the publisher that deletes 2 months
old data, so actually eatch night
I have a deletion of 1 days data (the 61st day).
Now the problem is that the next day, in the morning synchronisation,
eatch of my subscriber gets about 100.000 or even more deletions and
that makes synchronization slow.Although i have filters on my
replication,somehow deletions are send to all subscribers!!!
Now I am looking for solutions!!!
One thought of mine is to delete 2 months old transactions from eatch
subscriber, but because some subscribers share data then I belive that
if a deletion occured in a subscriber then when it synchronises the
deletion is passed to the publisher and then this deletion again is
going to be transfered to other subscribers when they try to
syncronise after.Am I right? So the issue will still be an issue.
Any Ideas of how to delete records in my case?
Please help!
You could replicate the execution of a stored procedure which does the
delete, and this way only the stored proc will be executed on the subscriber.
Rgds,
Paul Ibison
|||Thanks for the quick answer ... can u please be more specific.
How can I do this?
And if the deletion ocurs on the sunscriber wont that be transfer to
the publisher and then to other subscribers
that share data?
Thanks
|||In sp_addarticle there is the option to use @.type = 'proc exec' and this
option also exists on the article properties when using the gui to replicate
a stored procedure using transactional replication. Note that this applies
only when running the stored proc on the publisher so you'd have to remove
the rows in this direction only.
Rgds,
Paul Ibison
|||I am using Merge Replication does this applies to Merge Replication as
well ?
|||No - the replication of the execution of stored procedures is a transactional
attribute.
Rgds,
Paul Ibison
|||Any Ideas about Merge Replication ?
Because I am using merge replication and my system is live, I cannot
change to other type of replication,
i am searching for a solution, something i can implement fast.
Thanks
|||There's nothing obvious because what you need is some way of bypassing the
normal functioning of merge. i suppose you could disable the merge triggers
then send down a delete command using sp_addscriptexec to remove the rows
then reenable the triggers. However then you'll still have the replication
metadata hanging around for these rows until it gets cleaned up. Also if
there are any concurrent updates to these rows then there'll be problems
because the merge agent will be looking for missing rows when it
synchrionizes.
There might be some way of modifying a column on the rows so they fall
outside of a filter you place on the article and are therefore not
replicated. Then you could manually remove them on the publisher and via a
script on the subscriber - this is what I'd investigate.
Rgds,
Paul Ibison
"savvaschr@.nodalsoft.com.cy" wrote:

> Any Ideas about Merge Replication ?
> Because I am using merge replication and my system is live, I cannot
> change to other type of replication,
> i am searching for a solution, something i can implement fast.
> Thanks
>
|||Thanks for your time and Answers,
Finaly I found the answer.
You can specify at the publisher the @.delete_tracking variable of
sp_addmergearticle to 'false'
By doing this the Publisher deletes records and does not send them for
synchronisation.
So in my case, I delete 2 months old rows from my transactions table
at the Publisher, those deletions
are not send in the Synchronisation, and then I delete individually 2
month old transactions from my subscribers.
In this way my synchronisation is faster because Subscribers dont have
to process 100.000 deletions or more
every morning that they Synchronise.
Regards
Savvas

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:

> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove an
y
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited b
ut
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>sql

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:
> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove any
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited but
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>

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

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>
When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
sql

Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?

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 restored dB that is read-only

I restored a dB and set it as read-only. The dB is being
used for replication, so in my Replication Monitor in EP,
all the publications are listed numerous times. I cannot
detach the dB because it is being used for replication.
I cannot use sp_removedbreplication because it is read-
only. So the question is, how do I remove the dB? How
do I change the read-only flag off?
Larry,
this should remove the database for you (just need to replace xxx with your
database name).
alter database xxx set read_write with rollback immediate
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
Regards,
Paul Ibison
|||Paul,
I ran the commands you suggested and received the
following errors...
Server: Msg 5063, Level 16, State 1, Line 1
Database 'StoreMain-lpr10' is in warm standby. A warm-
standby database is read-only.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 3906, Level 16, State 1, Procedure
sp_dropsubscription, Line 441
Could not run BEGIN TRANSACTION in database 'StoreMain-
lpr10' because the database is read-only.
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'StoreMain-lpr10' because it is
being used for replication.
HELP!!!
Larry...
|||Larry,
this database is in RO mode because it has been restored without recovery.
The following script should be run before my original one:
alter database StoreMain-lpr10 set single_user with rollback immediate
go
restore database StoreMain-lpr10 with recovery
go
Regards,
Paul Ibison

Deleting ReportServer TempDB

Hi, I need to free up some space in my harddrive and was wondering what the effects of deleting the TempDB would be. I am not using this machine to host any important reports. (I am using it as a development machine). my question is what happens when i delete the tempdb will it break the system and will i have to reinstall reporting services to get my reports working again?

You should never delete the ReportServerTempDB - RS will no longer work afterwards.

The RS windows service should automatically cleanup entries in the ReportServerTempDB after they are expired (e.g. sessions expire after 10 minutes).

If the ReportServerTempDB allocates a lot of disk space, but the database is actually almost empty, you can try to compact / shrink the database (through SQL Server Management Studio) to reduce its footprint on the hard disk.

-- Robert

sql

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 repeated data

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Quote:

Originally Posted by venkat81

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable|||

Quote:

Originally Posted by Jim Doherty

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

Thursday, March 29, 2012

deleting table phisically

Deleting Table

Deleting Table

Deleting Table

Deleting Table

Deleting stored procedures through query analyzer

deleting SQL Srv 2K5 publication with no Agents

deleting sql server 2005 deployed packages?

Deleting Spaces!

Deleting SP syntax check please...

Deleting SP syntax check please...

Deleting semi duplicates

DELETING ROWS with REFERENTIAL INTEGRITY

Deleting rows with OPENXML

Deleting Rows from Publisher and from Subscriber

deleting rows from one table when they exist in another

deleting rows from one table when they exist in another

Deleting rows from many tables

Invalid column name 'UserID'.

deleting rows based on nvarchar data

Deleting rows

Deleting rows

Deleting rows

deleting rows

DELETING ROW DATA

deleting restored dB that is read-only

Deleting ReportServer TempDB

Deleting Reports in Report Server without stopping Report Service

Deleting reports

Deleting replication, leaves data in distribution database

Deleting repeated data

DENY permission,SQL server

Blog Archive

About Me