Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Snapshot-Agent Error Data Type "Date"

Proposed Answer Snapshot-Agent Error Data Type "Date"

  • Friday, August 22, 2008 7:54 AM
     
     
    Sign In to Vote
    0

    Hello everybody,

    I'm currently trying to set up a transactional push-replication between multiple SQL-Servers, all 2008. It works perfect as long as no table contains a column with the new Data Type "Date". In that case, Snapshot Agent creates an error, claiming that this Data Type is not supported by the Target Server, SQL-Server 2005, althought all Target Servers are definitely SQL-Server 2008 (Version 10.0.1600). Does anybody have a solution or am I facing a bug?

    Regard Dieter

     

All Replies

  • Friday, May 11, 2012 2:49 PM
     
     
    Sign In to Vote
    0
    I'm having the same problem.  Sure would be nice if MSFT would answer the question...
     
  • Friday, May 11, 2012 4:13 PM
     
     Proposed Answer
    Sign In to Vote
    1
    Even though the installations are 2008, please check the compatibility mode of the databases on the subscriber and publisher.  If they are set to 90 (2005) then it validates according to that compatibility mode abnd could be causing the error


    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

     
  • Friday, May 11, 2012 4:20 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    1
    You need to ensure that the publication database compatibility level is set to 100 or higher.

    Brandon Williams (blog | linkedin)

     
  • Saturday, May 12, 2012 12:52 AM
     
     
    Sign In to Vote
    0

    Wow, 3 years later, that was fast!  Thanks!

    So I figured out that the database had a compatibility level of 90 (2005), yet there were tables in it with datetime2 columns.  I know this isn't a replication issue, but how is this possible?  I find this ... alarming.

     
  • Saturday, May 12, 2012 1:45 AM
    Moderator
     
     
    Sign In to Vote
    1

    New features may work under older compatibility levels sometimes.

    Brandon Williams (blog | linkedin)

     
  • Saturday, May 12, 2012 1:56 AM
     
     
    Sign In to Vote
    0
    Hmm... I appreciate the intent to help, but I was looking for a better understanding of how this is possible.  Why or how is it that it "may work ... sometimes" ?
     
  • Saturday, May 12, 2012 2:54 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    1

    When a SQL Server database is upgraded, through backup and restore or detach and attach, it maintains its compatibility level. 

    Some features are available based on the database version — while some features are only available when the compatibility level is set after the upgrade.  In this case, setting compatibility level to 100 is required to generate a snapshot containing a datetime2 data type column.

    Brandon Williams (blog | linkedin)

     
  • Saturday, May 19, 2012 9:51 AM
     
     
    Sign In to Vote
    0

    Please do refer this as it may help you.

    http://www.bigresource.com/MS_SQL-Snapshot-Agent-Retried-and-Failed-fgVNjS2R.html