יום רביעי 02 מאי 2012 09:15
We are getting following error while when we sync data from One SQL Azure Instance to Another. Please advise.
Database provisioning failed with the exception
"The column 'update_scope_local_id' was specified multiple times for 'changes'.
Inner exception: SqlException Error Code: -2146232060 - SqlError Number:8156,
Message: The column 'update_scope_local_id' was specified multiple times for 'changes'.
For more information, provide tracing id ‘eec4a2eb-9975-49a8-9bf3-7cc91aa89fbc’ to customer support.
יום רביעי 02 מאי 2012 10:53מנחה דיון
can you check if you have not accidentally added the sync framework scope_xxx tables in the scope definition?
יום רביעי 02 מאי 2012 13:05
Thanks for the reply.
Not added this table in the scope definition. We are trying to sync 175 tables. Is it because of that ? Please let me know.
יום רביעי 02 מאי 2012 13:44מנחה דיון
sorry, my mistake...
can you check if you the _tracking tables were added in the scope by accident...
no, i dont think the number of tables has something to do with the error. i would suggest though that you break up the 175 tables into multiple sync groups if you can.
יום שישי 04 מאי 2012 08:46
Hi JuneT ,
Thanks ..Issue fixed.
Now we are getting another error.
Sync failed with the exception "Sync was aborted because more than 1000 changes failed to apply. Examine your table schemas to look for conflicting constraints or incompatible data types that may prevent sync from succeeding."
For more information, provide tracing id ‘42f47752-8d8f-4b28-992a-0d6d5bffa712’ to customer support.
How we can get more details about this error? Any log file or DBV's will provide more details about this issue? Please let me know.
יום שישי 04 מאי 2012 10:14מנחה דיון
does your database have existing rows already before you configured sync? i mean do both SQL Azure databases contain the same data already?
do you have the same schemas om both databases? same constraints?
יום שישי 04 מאי 2012 10:57
No data present in the destination database.
We didnt created any schema in the destination database, it is empty and all the schemas created by sync job.
יום שישי 04 מאי 2012 11:19מנחה דיון
do you have other sync groups? do you have tables belonging to more than one syncgroup?
sorry if am asking too many questions, we both dont have access to the detailed logs so i can only guess what may be happening.
if you have a table belonging to more than sync group, make sure the columns included for a table in both sync group is the same. e.g., if sync group 1 -> table 1 has col 1 and col 2, you cant have sync group 2 - table 1 having col 1 only or having col 1, col 2, col 3...
have you also compared the schemas?
יום שישי 04 מאי 2012 11:39
Thanks for your valuable time.
Yes, I have 4 other sync jobs, all are belonging to different databases and are running fine without any issues.
For this particular database, I have created only one sync job and added all the tables in to it.
What I can do is, instead of adding all the tables in to one sync job, I will group the tables and create separate job for each. But the problem is, user may not get up-to-date data when they generate reports and arrange the tables based on the relations(PK-FK) is also a challenge (170 tables with many relations with each other)
Please let me know your thoughts on this.
יום שישי 04 מאי 2012 12:08מנחה דיון
lets focus on the error then rather than recreating multiple sync groups...
can you do a schema compare between the source azure db and the one where sync fx created the tables itself?
your error means sync fx is able to select the rows from the source but is not able to insert to the target...
are you able to delete the sync group? if you can, i suggest you delete it...then see if the _tracking tables, stored procs and User Defined table types created by Data Sync is removed as well.
the original error you first posted indicates you have accidentally added the data sync tables to the sync. so that means that database has been previously provisioned. there may have been leftover data sync objects in your database and deleting the sync group should remove this.
if it doestn clean it up, download the sql azure data sync agent. although you dont need it because youre not synching on-premise db, install it so you can get access to the deprovisioning utility. once you install the agent, go to the folder where you installed the agent and you fill find the deprovisioningutil.exe there. run the deprovisioning utility to completely get rid of data sync created objects for the databases youre having an issue.
יום שישי 04 מאי 2012 14:09
Thanks , As recommended, I will clear all the schemas and resync it again. I will update you the status once it done