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.

Posted on 2013-05-27
Last Modified: 2013-06-28
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
Question by:rigelnet
1 Comment
LVL 16

Accepted Solution

DcpKing earned 100 total points
Comment Utility
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.



Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sort by Month and Year - SQL 3 22
Complex SQL 10 32
Row-Level Security 2 17
Database connection opened on a machine 8 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now