Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

Need to drop a column in table sql 2000

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
jimmylew52
Asked:
jimmylew52
  • 3
  • 3
2 Solutions
 
MohammedUCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
jimmylew52Author Commented:
MohammedU:

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

rrjegan17:

replication has already been deleted.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
jimmylew52Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope servername is the sysname and more details below:

http://msdn.microsoft.com/en-us/library/ms188391(SQL.90).aspx
0
 
jimmylew52Author Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now