locked
Basic Database Sync Issues RRS feed

  • Question

  • I've been trying to implement a basic database sync between two databases. The provisioning seems to be working fine but the synchronize step fails with the below error - 

    Failed to execute the command 'BulkInsertCommand' for table 'TableName'; the transaction was rolled back. Ensure that the command syntax is correct. 

    The inner exception is 

    Invalid object name 'TableName_tracking'

    Below is the basic code - 

                

    private static void Main(string[] args)
            {
                string connString1 = @"sourcedbconnstring";
                string connString2 = @"clientdbconnstring";

                var serverConn = new SqlConnection(connString1);
                var clientSqlConn = new SqlConnection(connString2);

                //PrepareServerForProvisioning(serverConn);
                //PrepareClientForProvisioning(serverConn, clientSqlConn);

                var provider1 = new SqlSyncProvider(Scopename, serverConn, null, Schemaname);
                var provider2 = new SqlSyncProvider(Scopename, clientSqlConn, null, Schemaname);

                var orchestrator = new SyncOrchestrator
                    { LocalProvider = provider1, RemoteProvider = provider2, Direction = SyncDirectionOrder.Upload };
                orchestrator.Synchronize();

            }

    Has anyone encountered the same issue?



    Thanks Gaurav

    Monday, July 16, 2012 11:14 PM

Answers

  • have you cleaned up the triggers as well? can you look at how its referencing the tracking table?

    you should use the deprovisioning api to clean up sync framework created objects.

    lookup deprovisionscope and deprovisionstore in the docs.

    • Marked as answer by Gaurav Grover Wednesday, July 18, 2012 6:55 PM
    Tuesday, July 17, 2012 7:30 AM

All replies

  • did you specify an objectprefix or schema when provisioning? can you confirm in the provisioned objects if they got applied?
    Tuesday, July 17, 2012 2:48 AM
  • I didn't use a prefix but I did specify the schema..The only assertion about the schema being applied is that the table names\ types that were created all had the schema name at the beginning..

    Thanks Gaurav

    Tuesday, July 17, 2012 5:27 AM
  • have you opened up the stored proc itself to inspect if it has a schema when referencing objects? do you have more than one scope defined for the same table?


    • Edited by JuneT Tuesday, July 17, 2012 7:01 AM
    Tuesday, July 17, 2012 7:01 AM
  • The stored proc is auto generated. I opened it and where ever there is a reference to my tablename_tracking, it has the schema name appended. Currently the app is running under one single scope. While I was developing it, i did use a handful of different scope names. 

    Since every time I re-ran my POC app, the database were already provisioned, so I created scripts to clean up the tables, stored proc and types that were created when the database is first provisioned so as to be able to re provision the database


    Thanks Gaurav

    Tuesday, July 17, 2012 7:23 AM
  • have you cleaned up the triggers as well? can you look at how its referencing the tracking table?

    you should use the deprovisioning api to clean up sync framework created objects.

    lookup deprovisionscope and deprovisionstore in the docs.

    • Marked as answer by Gaurav Grover Wednesday, July 18, 2012 6:55 PM
    Tuesday, July 17, 2012 7:30 AM
  • Yup that was it..as soon as I cleared the triggers this worked. Thanks for your help

    Thanks Gaurav

    Wednesday, July 18, 2012 6:55 PM