Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to drop a column in table sql 2000

Posted on 2009-05-16
7
Medium Priority
?
925 Views
Last Modified: 2013-11-30
when building a merge replication a column was added to all of my tables. I need to remove this column. I have run the following in querry anaalyzer

alter table tablename
drop column  rowguid

I get the following error

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FAHead__rowgui__2DB1C7EE' is dependent on column 'rowguid'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'index_165575628' is dependent on column 'rowguid'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.

This column must be remover ASAP. I have tried to remove it by right clicking and selecting delete but delete is not available.

How do I delete this column?
0
Comment
Question by:jimmylew52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 15

Assisted Solution

by:MohammedU
MohammedU earned 600 total points
ID: 24404970
Before dropping the column you have to remove the index and default attached to this column...

drop index tablename.indexname
go
DROP DEFAULT <defaultname>
go
alter table tablename drop column....
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24404996
As those columns are added by SQL Server for replication, you have to first delete the replication activity, remove the publishing activity for this database and then try issuing the drop column statement given below

alter table tablename drop column columnname

This should help you out.
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 24405037
MohammedU:

What might the default <defaultname> be or how do I find it

rrjegan17:

replication has already been deleted.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1400 total points
ID: 24405065
Ok.. To make it clear giving the steps below:
1. Drop the existing Merger replication
 
exec sp_dropmergesubscription @publication = 'MyPublicatioName', @subscriber = 'MySubscriberName', @subscriber_db = 'MySubscriptionDatabase'
 
2. Stop all replication triggers
declare test cursor for select sysobjects.name, tablename=object_name(parent_obj) From sysobjects join sysmergearticles
on object_name(sysobjects.parent_obj)=sysmergearticles.name
where sysobjects.type='tr'
open test
declare @tablename sysname
declare @triggername sysname
declare @string varchar(1000)
declare @newstring varchar(1000)
fetch next from test into @triggername, @tablename
while @@fetch_status=0
begin
set @string='drop trigger @triggername'
select @newstring=replace(replace(@string, '@tablename',@tablename),'@triggername',@triggername)
print @newstring
exec (@newstring)
fetch next from test into @triggername, @tablename
end
close test
deallocate test
 
3. Finally run Cleanup subscription
 
sp_mergesubscription_cleanup [ @publisher =] 'publisher'
        , [ @publisher_db =] 'publisher_db'
        , [ @publication =] 'publication'
 
4. Drop the Rowguid columns using the syntax provided earlier now if it exists.

Open in new window

0
 
LVL 1

Author Comment

by:jimmylew52
ID: 24405080
All publications and subscriptios have been deleted. Ran your script

sp_mergesubscription_cleanup [ @publisher =] 'servername'
        , [ @publisher_db =] 'T2Com'
        , [ @publication =] 'T2Com'

received error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'servername'.

alter table FADetailArchive
drop column  rowguid

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__FADetailA__rowgu__336AA144' is dependent on column 'rowguid'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN rowguid failed because one or more objects access this column.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24405094
Hope servername is the sysname and more details below:

http://msdn.microsoft.com/en-us/library/ms188391(SQL.90).aspx
0
 
LVL 1

Author Comment

by:jimmylew52
ID: 24405250
Wasn't what I needed. Here is what is working.

sp_HelpConstraint tablename

alter table Tablename drop constraint (enter constraint for rowguid Here - from previous command)

alter table Conversation drop column rowguid

Get an error stating an Index is dependant

drop index Conversation.(enter index from error message above)

alter table Conversation drop column rowguid

This works but hopefully it can be scripted as I have to run this several hundred times;

I am not good at sql scripting. any ideas on how to do this?
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question