Column Valid_date(column_name) in object dbo.Table_name contains type Date, which is not supported in the target server version, SQL Server 2005.

We need to configure Database transnational replication between two SQL 2012 Server.

we created SQL Agent Authentication between both server for db replication. After successfully creation of Publication, when we check monitoring replication it will shows that error for some in-compatibility for some tables, which fails t create first snapshot to create replication between SQL 2012 Server.

Error got from snapshot Agent History from Replication Monitor Window says : 'Column Valid_date(column_name) in object dbo.Table_name contains type Date, which is not supported in the target server version, SQL Server 2005.'

Any one can suggest how to make that column compatible with SQL 2012 server. Required efficient solution as there are more then 500 tables need to change with some columns.

below is full error message :

Error messages:
Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for Table 'dbo.table_name'.
Stack:    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(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/0
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
Message: Column Valid_date(column_name) in object dbo.Table_name contains type Date, which is not supported in the target server version, SQL Server 2005.
Stack:    at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingPreferences options)
   at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingPreferences sp, StringBuilder sb, ColumnCollection columns, ICollection indexes)
   at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingPreferences sp, StringBuilder sb)
   at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
rigelnetAsked:
Who is Participating?
 
DcpKingConnect With a Mentor Commented:
Your problem is that you're trying to replicate data between a version of the database system (2012) that supports the type Date onto a version (2005) that does not.

You can change the column type from Date to Datetime in the 2012 version and that should cure your problem. Alternatively, replicate to a 2012 system and not a 2005 system.

hth

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.