Droping a subscription

I have two orphan subscriptions on a SQL 2000 server. I need to drop both
of them. Can anyone help me out with the syntax to do that? I have been
trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
figure them out (of course I am assuming that one of these is the one I am
supposed to be using).
The two subscriptions are listed as
'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
Thanks for any help you can give.
Jeff
Jeff,
you probably need to run sp_droppullsubscription on the subscriber. If the
database is no longer involved in replication, I'd run sp_removedbreplication
instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That did the trick.
Thanks
Jeff
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4D801C50-80FF-4F84-AF2A-22421A8937A5@.microsoft.com...
> Jeff,
> you probably need to run sp_droppullsubscription on the subscriber. If the
> database is no longer involved in replication, I'd run
> sp_removedbreplication
> instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Where are these subscriptions showing up? Are they showing up in EM, and
then when you drill down on them you get an error message? or are they in
the subscriber databases and you can't pull new subscriptions there.
If they are showing up in your subscription database you will probably need
to manually delete the contents of following rows
MSreplication_subscriptions
MSsubscription_agents
MSsubscription_properties
-
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
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:eGTI4KYAGHA.272@.TK2MSFTNGP09.phx.gbl...
>I have two orphan subscriptions on a SQL 2000 server. I need to drop both
>of them. Can anyone help me out with the syntax to do that? I have been
>trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
>figure them out (of course I am assuming that one of these is the one I am
>supposed to be using).
> The two subscriptions are listed as
> 'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
> 'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
> Thanks for any help you can give.
> Jeff
>

droping a CONSTRAINT

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helpingYou do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

It will show you exactly what to do|||I get an incredible script !!

I really dont understant
here I dop the column

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

then it doesnt exist any more ! why I cannot create it after ?|||because u failed to drop it....... u got error message :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.

DROP TABLE?

hi, why I can do this:
DECLARE @.table_name varchar(100)
SET @.table_name ='ABC'
DROP TABLE @.table_name
Line 3: Incorrect syntax near '@.table_name'http://www.sommarskog.se/dynamic_sql.html
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"js" <js@.someone@.hotmail.com> wrote in message
news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
>|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
Because the function requires an object literal, not a variable.
You can't even say:
Drop Table 'ABC'
You need to say:
Drop Table ABC
or
Drop Table dbo.ABC
David Gugick
Imceda Software
www.imceda.com|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
I should have added that if you want to perform a dynamic DROP, you can
use dynamic SQL:
Declare @.SQL nvarchar(128)
Set @.SQL = N'Drop Table dbo.ABC'
Exec sp_executesql @.SQL
David Gugick
Imceda Software
www.imceda.com|||DROP TABLE doesn't accept a variable. What you can do is:
DECLARE @.table_name SYSNAME
SET @.table_name ='Employees'
EXEC ('DROP TABLE' + @.table_name)
Jacco Schalkwijk
SQL Server MVP
"js" <js@.someone@.hotmail.com> wrote in message
news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
>|||...yielding one of the weaknesses here, difficult parsing...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TABLEEmployees'.
:-)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:er1E1NQQFHA.3416@.TK2MSFTNGP10.phx.gbl...
> DROP TABLE doesn't accept a variable. What you can do is:
> DECLARE @.table_name SYSNAME
> SET @.table_name ='Employees'
> EXEC ('DROP TABLE' + @.table_name)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uDYbxIQQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||Note to self: should use fixed-width fonts :)
Jacco Schalkwijk
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8OwKVQQFHA.3448@.TK2MSFTNGP10.phx.gbl...
> ...yielding one of the weaknesses here, difficult parsing...
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'TABLEEmployees'.
> :-)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:er1E1NQQFHA.3416@.TK2MSFTNGP10.phx.gbl...
>|||js wrote:
> hi, why I can do this:
> DECLARE @.table_name varchar(100)
> SET @.table_name ='ABC'
> DROP TABLE @.table_name
> Line 3: Incorrect syntax near '@.table_name'
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You cannot use a variable for the table name in the command "Drop
Table."
You can, however, use dynamic SQL, which, in this case is VERY
DANGEROUS!
Use at your own risk:
DECLARE @.table_name varchar(100)
SET @.table_name ='ABC'
EXEC ('DROP TABLE ' + @.table_name)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmKZloechKqOuFEgEQJfjgCfX++bziZp74Kw
80hlHNV+y+UZ9akAoLO4
zHlTEuZb1E7oAuG7vBq0IjwD
=zRUS
--END PGP SIGNATURE--

DROP TABLE IF EXISTS

Hello,
Forgive me if I am being stupid, but I cannot get some syntax to work and I
need it urgently. I want to drop a table if it exists and make a new table
in its place. I though I could do,
DROP TABLE IF EXISTS tablename;
CREATE TABLE tablename ([ID] [int] NOT NULL)
but I get the first time as the table doesn't exist yet!
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'tablename' , because it does not exist in the system
catalog.
Please could someone help me with the synatax!if exists (select * from dbo.sysobjects where id =
object_id(N'[<owner>].[<tablename>]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [<owner>].[<tablename>]
GO
CREATE TABLE [<owner>].[<tablename>] (
...
) GO
"11Oppidan" wrote:

> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new tabl
e
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the syste
m
> catalog.
> Please could someone help me with the synatax!
>
>|||You can try as follows...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tablename]
GO
CREATE TABLE [dbo].[tablename] (
[aId] [int] IDENTITY (1, 1) NOT NULL ,
[nDayId] [int] NOT NULL
) ON [PRIMARY]
GO|||11Oppidan wrote on Wed, 23 Mar 2005 09:15:07 -0000:

> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
You can use EM to script out table creation, and if you keep the option to
create the DROP syntax ticked you'll get something like:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tablename]
GO
followed by the create statement.
Dan|||try this:
if object_id('tablename') is not null
drop table tablename
go
or this:
if exists(select * from sysobjects where xtype='u' and name='tablename')
drop table tablename
go
hth
dean
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new
table
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
system
> catalog.
> Please could someone help me with the synatax!
>|||Many thanks guys!
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
>

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FK
Joe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
--
David Portas
SQL Server MVP
--

Drop Primary Key Non-Cluster Index

I have a SQL Server 2000 database that I need to drop the primary key that
is a non-cluster index this has a foreign key.
What is the correct syntax complete this task with the tables listed below.
Table A
C1
C2 : PK (T2 Non-Cluster Index)
Table B
C1
C2 : FKJoe K. wrote:
> I have a SQL Server 2000 database that I need to drop the primary key that
> is a non-cluster index this has a foreign key.
> What is the correct syntax complete this task with the tables listed below
.
> Table A
> C1
> C2 : PK (T2 Non-Cluster Index)
> Table B
> C1
> C2 : FK
ALTER TABLE B DROP CONSTRAINT fk_table_b_table_a;
ALTER TABLE A DROP CONSTRAINT pk_for_table_a;
David Portas
SQL Server MVP
--

DROP INDEX syntax for MS SQL 2000

I am executing
DROP INDEX [IX_Users] ON [Users]
and i get a syntax error
what is the exact syntax to DROP an INDEX for MS SQL 2000
thank youDROP INDEX [Users].[IX_Users]

i always check BOL before i post a question here.|||thank you
a really strange syntax not at all like CREATE INDEX|||actually the syntax that Sean gave you is deprecated in 2005, although it still works. It will likely go away with katmai.

in 2005, the preferred syntax is what you tried initially. See:

http://msdn2.microsoft.com/en-us/library/ms176118.aspx