Deleting from a list.

Hi;

I am still improving my sql/tsql skills.

I was wondering if someone could give me some advice on how to do some
high powered deletes.

I have two tables, A and B.

"A" is the table I want to delete records from.

"B" is a table where each record is 4 keys ( fields ) that I want to
match with one or more records in "A" to remove.

Could I accomplish this with something like

delete from A ( select ... from B ) ?

SteveHi

Books online should be the first option when wanting to find out information
regarding syntax etc. and example D on the delete definition page gives you
exactly what you want.

http://msdn.microsoft.com/library/d...asp?frame=true

John

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0310311713.1be6dc2e@.posting.google.c om...
> Hi;
> I am still improving my sql/tsql skills.
> I was wondering if someone could give me some advice on how to do some
> high powered deletes.
> I have two tables, A and B.
> "A" is the table I want to delete records from.
> "B" is a table where each record is 4 keys ( fields ) that I want to
> match with one or more records in "A" to remove.
> Could I accomplish this with something like
>
> delete from A ( select ... from B ) ?
> Steve

Deleting a Primary Key Constraint with TSQL

How do i delete a Primary Key constraint on a table using TSQL?You can use the alter table statement. For example:

ALTER TABLE TableName DROP CONSTRAINT PK_NAME

More info is located here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

Regards
Basia|||And what if the contraint has no name?
And what about IDENTITY, how do you drop that?

Thanks in advance!|||The constraint should have a name, use sp_help TableName to look it up.

delete sql server registration in tsql

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

Thanks!
Jennifer(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.

In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.

If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.

Thanks,
Jennifer

Erland Sommarskog wrote:

Quote:

Originally Posted by

(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

>
There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.
>
In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.
>
If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||I could be totally wrong here but . . .

As far as I know there is a MMC folder that has the data you want.

<drive>:\Documents and Setting\<your logon name>\Application
Data\Microsoft\MMC

Adrian

Jennifer wrote:

Quote:

Originally Posted by

Erland,
>
Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.
>
Thanks,
Jennifer
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

>(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

>>Is there any way in tsql to delete a sql server registration? (I know
>>how to do this from Enterprise Manager).

>There are undocumented extended stored procedures to manipulate the
>registry. However, starting with SQL 2000 SP4, they were changed to
>permit changes only within the tree of the SQL Server instance, if
>memory serves.
>>
>In any case, I would say that it does not really make sense. Enterprise
>Manager is a client, and SQL Server is a server. A server sends data to
>its clients on requests. It does not send requests to the clients.
>>
>If you want manipulate the registry on remote machines, Windows offers
>more direct ways to do this, and that is far better than sneaking around
>through SQL Server.
>>
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>

|||Adrian,

Thank you for the response. I found the folder and while the files did
look interesting, they did not appear to have the info in there.

Thanks,
Jennifer
abc wrote:

Quote:

Originally Posted by

I could be totally wrong here but . . .
>
As far as I know there is a MMC folder that has the data you want.
>
<drive>:\Documents and Setting\<your logon name>\Application
Data\Microsoft\MMC
>
>
Adrian
>
>
Jennifer wrote:

Quote:

Originally Posted by

Erland,

Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.

Thanks,
Jennifer

Erland Sommarskog wrote:

Quote:

Originally Posted by

(J.Evans.1970@.gmail.com) writes:
>Is there any way in tsql to delete a sql server registration? (I know
>how to do this from Enterprise Manager).
There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.
>
In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.
>
If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Jennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

All I wanted to do was get those unregistered from the Enterprise
Manager so I don't have to see them. I was hoping I could select all of
them in Enterprise Manager, right click and delete the registration.
But it doesn't work like that. I don't really see myself doing that 400
times for each individual server. :) So I was just wondering if I could
do it with a loop or something along those lines from Query Analyser. I
see now that is really not possible.

Nah, it's possible. But I doubt that it is any faster than to delete
all those servers by hand. You would learn about registry hacking in
the most inconvient way.

Anyway, the brute method is to run RegEdit, and go to
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X
and then go on a delete craze there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Totally awesome! Thanks! It took all of 2 seconds. Much much faster.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Jennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

All I wanted to do was get those unregistered from the Enterprise
Manager so I don't have to see them. I was hoping I could select all of
them in Enterprise Manager, right click and delete the registration.
But it doesn't work like that. I don't really see myself doing that 400
times for each individual server. :) So I was just wondering if I could
do it with a loop or something along those lines from Query Analyser. I
see now that is really not possible.

>
Nah, it's possible. But I doubt that it is any faster than to delete
all those servers by hand. You would learn about registry hacking in
the most inconvient way.
>
Anyway, the brute method is to run RegEdit, and go to
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X
and then go on a delete craze there.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Delete Replication per TSQL

Hello,
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart