February 15, 2002 at 5:35 pm
This is can work with the Transactional replication too.
Abdu
February 15, 2002 at 6:32 pm
For those interested I have found a way to modify a column (I have only tested this with a bit column so far) that allows NULLs to not allow NULLs without stopping and without breaking replication.
It is simple and fast. Run the following in query analyzer:
UPDATE syscolumns SET typestat = 1
WHERE id = 1262627541 AND name = 'YesNo'
id is the id of the table found in sysobjects and name is the column name to be changed.
typestat will be 0 if the bit column allows nulls. I don't know yet if this will work with other columns.
One caution (and I did this) is to make sure there are no NULL values before changing the column otherwise the only way to delete a row with a NULL (where a NULL is no longer allowed) is to delete it from Query Analyzer.
I have done this twice so far.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 15, 2002 at 7:24 pm
I also discovered that I can delete columns from syscolumns for a table with transactional replication and replication doesn't break. I deleted the columns from the publisher not the subscriber. In all my testing so far I have not modified the table at the subscriber. I'll get to that later.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 16, 2002 at 5:09 am
In SQL2K support for dropping a column is built in, allowing you to easily use the method Abdul mentioned. It's interesting, but can I ask why you need to do this instead of just redoing the pub (one way or the other)? Im also not sure why deleting the column didnt break replication, what about the stored procs that post data, dont you run into a problem with the incorrect number of passed parameters?
Andy
February 16, 2002 at 5:49 pm
I was surprised that deleting a column didn't break replication as I have looked at the stored procedures that update the data on the subscriber. I'll look into this and let you know what I find.
As for your first question, we replicate about 20 or more tables per publication for transactional replication and sometimes when we break replication and then rerun the snap shot it can take a few hours to complete and will sometimes fail.
I know that modifying system tables can be risky, but if I can develope a sure method that doesn't break replication and doesn't require replication to be stopped then we'll have something very useful to us in saving us from having to spend time monitoring replication to ensure the snap shot succeeds.
There are other reasons, such as the ability to change replication during peak hours without adverse effect and not having to worry about loosing the many column filters. Although that last might be resolved with the scripting that I've heard about. I'll have to learn how to do this.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 17, 2002 at 6:52 am
How much data do you have? Snapshotting live with SQL is tricky, can create a ton of blocking since it locks the db. SQL2K offers a concurrent option to avoid this. Another question is - do you HAVE to send over a new snapshot? If you stop the log reader and process any remaining transactions in the distribution db, no reason why you can't make changes to both publisher and subscriber, then restart the log reader.
Andy
February 19, 2002 at 4:32 am
I dream of having SQL Server 2000. For now, I must work with SQL Server 7.0. Some of the replicated tables have 40,000 to 90,000 rows.
Is there an article or something in BOL that describes in more detail about how to process remaining transactions?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 19, 2002 at 5:01 am
SQL2K is definitely worthwhile for replication. Doesnt have everything yet, but every lit bit helps. Easiest way is to just run the distribution agent, that will move any pending transactions over to the subscriber.
Andy
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply