Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mstclair
Starting Member
13 Posts |
 Posted - 2010-03-19 : 16:46:13
|
| We've got the problem described in [url=http://blogs.msdn.com/sqlserverfaq/archive/2009/10/01/cannot-alter-schema-of-replicated-article.aspx]this msdn blog article[/url] and it's causing major headaches.We have a server that has many article columns incorrectly marked as being published to non-SQL subscribers (sys.columns shows is_non_sql_subscribed as True), which is causing us major problems by preventing minor schema changes to these specific articles only. The blog post confirms that this can happen. We've never had a non-SQL subscriber to any of our publications.As far as the solutions recommended in the blog, we have these issues:1) None of our SQL 2005 servers (mostly build 9.0.3186) include the system stored procedure sys.sp_MSarticlecol, which would be most useful! Not just for the solution, but also for testing and recreating the problem.2) Reapplying the snapshots does not fix the problem.3) Dropping and recreating the articles is something we'd really like to avoid due to needing to schedule downtime for giant snapshots, and frankly I'm skeptical that this will definitely correctly reset the sys.columns values correctly.4) This is the most dissapointing, but using the DAC connection and trying to update sys.colums directly, as recommended, fails with a message 4406 - Update or insert of view or function failed because it contains a derived or constant field.We're at a loss. If there's any way that we can straighten this out via the DAC (ANY way to update the sys.columns or underlying data?) or by getting the sys.sp_MSarticlecol and any dependent objects on our system, that is what we would prefer.Any help would be greatly appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mstclair
Starting Member
13 Posts |
Posted - 2010-03-22 : 09:32:56
|
| Calling Microsoft is definitely on the list, but I'm pretty sure they are going to have us try dropping and recreate everything replication-related before escalating the incident, and it's something we REALLY would like to avoid. |
 |
|
|
|
|
|
|
|
Can't alter schema - bad nonsqlsubscriber metadata