July 29, 2011 at 8:18 am
After spending a few hours troubleshooting\resolving a Replication SNAPSHOT failure -- trying to publish a SQL Server 2008 R2 article contaiing a DATETIME2(7) column -- to a SQL 2005 Server column defined as DATETIME -- thought I'd post the resolution...
Replication DATETIME2 to DATETIME conversion (SQL 2008 R2 -> SQL 2005)
Recently ran into an issue w/ our Snapshot failing converting DATETIME2 to DATETIME.
Characteristics:
Source SQL Server1: SQL 2008 R2 sp1 - compatibilty mode set to 9 (SQL 2005) <- publisher & distributor
Target SQL Server1: SQL 2005 - compatibilty mode set to 9 (SQL 2005) <- subscriber #1
Target SQL Server2: SQL 2008 R2 sp1 - compatibilty mode set to 10 (SQL 2008) <- subscriber #2
Windows OS: Windows Server 2008
Publication on SQL2008 R2 - contained 1 article (SQL 2008 R2) defined w/ column MyDate DATETIME2(7) NOT NULL
Problem: When we attempted to generate the snapshot, it failed attempting to convert DATETIME2 to DATETIME because our Publication SQL Server 2008 R2 machine was set to compatibility level 9 (2005)
Resolution: Actually 2 different scenarios for resolving this problem -
1) IF you only need to Replicate to a target SQL 2008, simply change Publisher's Compatibilty mode to 10 (SQL 2008) - and the snapshot should work
2) IF you need to Replicate to target SQL 2005, set your Publisher Compatibility mode to 9 (SQL 2005) then:
- Access Publication properties and select Articles
- highlight the Article(s) that include datatype DATETIME2(7) -- 1 at a time
- set propeties for each Article as:
- IMPORTANT: Set the following attributes for each Article containing DATETIME2(7) for 2005 compatibility
- "convert data types" = true
- "convert new datetime to NVARCHAR" = true
3) run your snapshot!
Note: the target DATETIME column will be generated as nvarchar(27)
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply