Post reply

Replication issue on SQL 2008

  • June 27, 2011 at 2:11 am

    #253395

    Dear all,

    I have a database of sql 2008 version in the compatibility mode of 90(2005) .It has a table which has two columns of the data type 'date' which is new in SQL 2008. Now when configuring the transactional replication to a subcriber on 2008, it fails during the snapshot step saying that 'date' is not valid data dtype in sql 2005.

    I believe that due to lower compatiblity mode of the publisher, the script that gets generated from it during snapshot, generates it thinking that it is generating it for a 2005 database and then it fails because the 2005 does not understand 'date' . I have an option to change the compatibility mode of publisher but it is risky and needs some good time to analyze the database.

    Is there a way for me to tell the sql server to generate the snapshot for a true 2008 database.

    Thanks

    Chandan

  • June 27, 2011 at 4:27 am

    #1344382

    On some blog, some guy suggested that we can change the compatibility mode to 100, run the snapshot agent and then once the replication starts working fine, we change the compatibility level back to 90.

    By changing the compatibility model to 100, the snapshot agent generates bulk export ddl scripts thinking that it is doing it in accordance with a 2008 database. Once it is done with it, we can change the compatibility mode back to 90.

    This solution looks nice to me. Any one has something more elegant than this?

    Regards

    Chandan

  • June 27, 2011 at 7:51 am

    #1344519

    Any thoughts from anyone here??

  • June 27, 2011 at 11:00 am

    #1344679

    Please help

  • June 28, 2011 at 7:39 am

    #1345183

    You could always try to create the table manually on the subscriber, take a BCP and import the data and then start the replication without snapshot.

    -Roy

  • June 28, 2011 at 7:57 am

    #1345194

    Roy Ernest (6/28/2011)

    You could always try to create the table manually on the subscriber, take a BCP and import the data and then start the replication without snapshot.

    thanks for your reply. I am new in replication. For those tables, if i use bulk import and export as you said, then what happens when i add these articles in the publisher? It asks me to reinitialize subscriber and it will generate snapshot agent. How do i skip initializing the snapshot for these two tables?

  • June 29, 2011 at 7:18 am

    #1345909

    After you will add two articles, you can alway recreate subscription with 'no snapshot initialization'.

    Cheers, Max

    Check-out free open-source utility Sql Server Replication Explorer[/url]

  • June 30, 2011 at 3:05 am

    #1346393

    Maksymilian Mulawa (6/29/2011)

    After you will add two articles, you can alway recreate subscription with 'no snapshot initialization'.

    Thanks for sharing that utility

    M&M

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply