February 12, 2002 at 9:06 am
I am using SQL Server 7.0 SP3 and am wondering if anyone has any experience with what I am planning to do.
I have a table being replicated with transactional replication that I need to modify to make a bit value column not accept NULLS. If I try to do it in Enterprise Manager I will be stopped because unchecking the NULL checkbox causes SQL Server to drop and recreate the table which replication prohibits.
I have not tested the following with replication on, but did test it on a table and it worked:
I altered the column and changed the column datatype from bit to int. Then I altered the column again from int to bit and specified that it does not allow NULLS. Now the column does not allow NULLs and I didn't have to drop the table.
I plan to test this on a table with replication, but suspect it will work since I am not dropping the table. Has anyone ever tried this before or does anyone have another idea or a simpler way to do this? Is there anything in the triggers or sp's related to replication that would cause the replication to fail because of the change from allowing NULL to not allowing NULL?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 12, 2002 at 4:05 pm
I don't think you will be able to do this. I'm running SQL2K, but the same rule should still apply to 7.0. - can't alter a replicated column (it's been a while). I also confirmed with my own quck test, and BOL (alter table). Of course, there's always that one way to find out for sure....
Sean
February 12, 2002 at 5:53 pm
I just ran the following on a replicated table in Northwind and it failed:
ALTER TABLE categories
ALTER COLUMN categoryname nvarchar(15) null
Server: Msg 4929, Level 16, State 1, Line 1
Cannot alter the table 'categories' because it is being published for replication.
Your best option is to remove the article, modify it, then add it back. I've got an article posted about a workaround method of doing it.
Andy
February 12, 2002 at 5:59 pm
You cannot make a change to any table under replication. What happens is when the table is being altered there is a temporary version created with the new settings and all the data from the original is inserted (that is why it will let you make some conversions but bark about data loss). Once the data has been copied a drop table is performed on the original table and the new temporary version is renamed to the original tables name. You can see this in Profiler when you make these changes, so since the old table is dropped it will not let you perform this task.
February 13, 2002 at 5:50 am
I have added columns to a replicated table and SQL Server has allowed this. IT just gives me the warning that the new column is not included in replication. Which is fine when the new column doesn't need to be replicated. That is why I considered this other option as neither alter table command will cause the table to be dropped.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 13, 2002 at 6:28 am
Adding a column to a table and altering an existing column are two different stories. Give it a try - you should find out pretty quickly if your theory works out!
February 13, 2002 at 6:28 am
I have read the article by Andy. That will work, I just thought the other way should work since the column name was staying the same. I am looking for what is blocking me from making the change now, for curiousity. I don't see any triggers on the table at the publisher nor at the subscriber that block the alter table commands. Does anyone know, is the check being done in the distribution database? or somewhere else?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 13, 2002 at 6:46 am
Ok I made a mistake in my statement. You can add a column but you cannot change any replicated column in any way and you will not be able to remove a column once added unless you delete publication first. Also just a note on adding found this in SQL2K BOL
If I create a publication with one table as an article, and then change the schema of the published table (for example, by adding a column to the table), will the new schema ever be applied at the Subscribers?
Answer:
Yes. The new schema will be applied at the Subscribers, provided schema changes to the published table were made through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.
February 13, 2002 at 7:07 am
Well I found out what was blocking the change and ran the following to make the change:
UPDATE sysobjects SET replinfo = 0
WHERE name = 'TestAlterTable'
ALTER TABLE dbo.TestAlterTable ALTER COLUMN YesNo int
GO
ALTER TABLE dbo.TestAlterTable ALTER COLUMN YesNo bit NOT NULL
GO
ALTER TABLE dbo.TestAlterTable ADD CONSTRAINT
DF_TestAlterTable_YesNo DEFAULT (0) FOR YesNo
GO
UPDATE sysobjects SET replinfo = 3
WHERE name = 'TestAlterTable'
The column called replinfo in table sysobjects blocks the change. I of course doing this in our testing environment. Now, my problem is that changes to existing data or additions to the table on the publisher are not being replicated. However, the replication is not reporting a failure.
I am continuing to research the problem, but if anyone has any ideas please let me know.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 13, 2002 at 7:30 am
Does anyone know how SQL Server calls the sp's created by transactional replication? Or where this takes place?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 13, 2002 at 8:18 am
Thats an interesting bit of info Robert. Will have to do some more testing of my own. I'd use caution doing it though. Not because undocumented - who cares! But if you were to alter a column to be wider than previously and did not modify the subscriber, you could have insert/updates fail. Etc. Yukon supposed to have better capabilities with regards to modifying replicated tables.
Andy
February 13, 2002 at 8:27 am
This will never be done in our production environment until we are satisfied that there are no adverse affects to replication.
Thus far, I still haven't found out why replication is no longer working and is not giving any errors. SQL Server is reacting as if replication on that table no longer exists (except that unless I change that column replinfo I still can't make changes to the table.)
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 13, 2002 at 12:53 pm
Well, sometimes that is the price you pay for tinkering with the system tables. I have to give you high marks for your creative approach. I think you are at the start of a very interesting learning experience. Please be sure to keep us informed of what you find out! Many of us will undoubtedly do some testing of our own.
February 15, 2002 at 5:27 pm
I have done this before in a merge replication SQL2K, to change a column (data type or name),let's say you want to change the column C1
1- Create a new column C2
2-Copy data to C1 from C2
3-Drop C1
4-Create C1 with the new data type or name
5-copy data from C2 to C1
6- drop C2
This should work Fine
Abdu
February 15, 2002 at 5:31 pm
I'll try that for merge replication. I am at this moment trying to do this for transactional replication and I know I can't drop a column in transactional replication while it is being published for replication unless I set replinfo = 0.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply