Failure on sync.

Comments

• Andras B Redgate › Posts: 249

  December 6, 2005 4:23AM

  EZLinks wrote:

  I was attempting to synchronize two databases today and noticed that when an unexpected error occurs (such as an old stored procedure is encounted on the "from" database that includes references to a missing field on a table due to a sync from the other machine) the syncronization stops completely and does not allow the process to continue at all.
  
  Is there a chance you can offer the ability to continue the sync and ignore the error rather than dying and having to manually track down changes to the DB?
  
  Here is an example of the error that caused a stop:
  
  The following error message was returned from the SQL Server:
  
  [207] Invalid column name 'BeginDate'.
  Invalid column name 'EndDate'.

  
  Hi,
  one thing you can try is to exclude this stored procedure only, and disable include dependencies. This will work only in this case, and if there are other corrupt stored procedures in your database, SQL Server once again will not allow you to run the script. Another alternative is to set the "Do not include plumbing for transactional synchronization scripts". Then run the script from a query analyzer. This will not use transactions, just simple batch separators.
  The reason we do not skip errors is that if SQL Server does not accept a stored procedure because it is in an inconsistent state (while missing table references are ok, references to nonexisting columns in existing tables are not ? ) dependent objects cannot be synchronized either, and the target database would most likely end up in a bigger mess.
  Note also, that while many stored procedures were accepted by SQL Server 2000, 2005 has a much better syntax checker, picks out more inconsistencies, and rejects procs that were valid previously.
  
  Regards,
  Andras

  András Belokosztolszki, PhD
  Red Gate Software Ltd.

  0 · Share on Twitter