Solved

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
1
1,305 Views
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
0
Comment
Question by:rigelnet
1 Comment
 
LVL 16

Accepted Solution

by:
DcpKing earned 100 total points
ID: 39199742
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

861 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

23 Experts available now in Live!

Get 1:1 Help Now