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 |
|
spardha
Starting Member
2 Posts |
 Posted - 2009-05-26 : 09:03:37
|
| Ok so I have a test setup of 2 servers both systems running server 2008, with SQL server 2008 running.Ive imported our database and created a local publication on one machine.Then on the 2nd machine setup the subcription successfully.The problem is the snapshop agent fails during the replication with the error:Message: Script failed for Table 'dbo.sysdiagrams'. heres the rest of the error message:Source: Microsoft.SqlServer.SmoTarget Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[])Message: Script failed for Table 'dbo.sysdiagrams'. Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)Get help: http://help/0Source: Microsoft.SqlServer.SmoTarget Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingOptions)Message: Column definition in object sysdiagrams contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000.Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingOptions options) at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes) at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb) at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingOptions so) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) (Source: Microsoft.SqlServer.Smo, Error number: 0)Get help: http://help/0 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-05-26 : 12:03:47
|
| its a system table to store diagrams info..I think is removed in 2005. so its best to remove the table from publication.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
spardha
Starting Member
2 Posts |
Posted - 2009-05-28 : 04:04:40
|
quote:
Originally posted by dinakar
its a system table to store diagrams info..I think is removed in 2005. so its best to remove the table from publication.
How do i remove it?? delete it as its not used in the DBThanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-05-28 : 10:35:49
|
| check out books online for sp_droparticle.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
h2osonic
Starting Member
2 Posts |
Posted - 2009-06-11 : 16:09:08
|
| I have the exact same problem with a user table (not a system table). I create a SQL 2008 publication intended for a SQL 2008 subscriber, and the snapshot agent quits because one of the tables uses nvarchar(MAX). The error message is similar to the one originally posted, stating that the table "contains type Nvarchar(MAX), which is not supported in the target server version, SQL Server 2000."Why does it mention SQL 2000? I did not configure the publication to support any 2000 subscribers! :( |
|
|
|
h2osonic
Starting Member
2 Posts |
Posted - 2009-06-12 : 15:11:48
|
| So I figured out the answer to my own problem. I had restored a SQL 2005 database in SQL 2008 and assumed it was now a SQL 2008 database. Using the T-SQL statement "SELECT compatibility_level from sys.databases WHERE name='XYZ'" I discovered the compatibility mode on the database was 80, which is the lowest compatibility mode and explains the SQL 2000 error. I used the statement "ALTER DATABASE XYZ SET COMPATIBILITY_LEVEL = 100" to make the database compatible with SQL 2008 only and prevent the error I mentioned previously from occurring during snapshot creation. I hope that helps the next person who encounters this problem.Now if I can just get the subscriber synchronization to pick up all of my stored procedures I'll be set... |
|
|
|
davidth
Starting Member
5 Posts |
Posted - 2010-07-22 : 06:19:44
|
| thank h2osonic.i resolt my problem with your explaint |
|
|
|
|
|
|
|
|
Replication error SQL 2008