Sync Framework and SQL Notifications

All replies

• 

  

  0

  Sign in to vote

  first, the SelectRowCommand get executed when a conflict or error is encountered applying a change, so it looks like you are encountering either a conflict or an error applying a change.

  the connection error can mean many things, network not available, etc..

  see: http://blogs.msdn.com/b/spike/archive/2009/04/16/a-transport-level-error-has-occurred-when-sending-the-request-to-the-server-provider-tcp-provider-error-0-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx

  Tuesday, December 4, 2012 3:41 AM
• 

  

  0

  Sign in to vote

  I don't think thats the answer. If I let the sync run to completion, without setting up a SQL Notification, the sync never encounters an error. The error happens AS SOON AS I make the Dependency.Start() call, so it seems to me to be a conflict between the two services. Perhaps the Service Broker is interferring with the Sync ????

  Steve

  Tuesday, December 4, 2012 2:34 PM
• 

  

  0

  Sign in to vote

  then check your service broker or other apps that may be updating the rows...the SelectRow will only ever get called if there's a conflict.

  Wednesday, December 5, 2012 2:15 AM
• 

  

  0

  Sign in to vote

  The service broker is a MICROSOFT application!!!

  I merely initiate the service broker to watch for SQL notifications.

  commandText.Append(string.Format("USE {0}; ", databaseName));
  commandText.Append(string.Format("CREATE QUEUE {0}ChangeQueue; ", databaseName));
  commandText.Append(string.Format("CREATE SERVICE {0}ChangeService ON QUEUE {0}ChangeQueue ", databaseName));
  commandText.Append(string.Format("([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); "));
  commandText.Append(string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ", databaseName));
  commandText.Append(string.Format("ALTER DATABASE {0} SET ENABLE_BROKER; ", databaseName));
  commandText.Append(string.Format("ALTER DATABASE {0} SET MULTI_USER; ", databaseName));

  As I stated, I have a number of syncs running on individual tables, and they run just fine, AS soon as I issue the SqlDependancy.Start() command, a number of syncs end, with varying errors. This morning I got these....

  Wed Dec 12 08:27:00 2012 - ExecuteSync (c#) for [Job_srw_Data][TableLogVariable_PULSE_HEIGHT] Returned = [Failed to execute the command 'InsertCommand' for table 'TableLogVariable_PULSE_HEIGHT'; the transaction was rolled back. Ensure that the command syntax is correct.]!

  Wed Dec 12 08:27:00 2012 - ExecuteSync (c#) for [Job_srw_Data][TableLogVariable_CONFIDENCE] Returned = [Failed to execute the command 'UpdateMetadataCommand' for table 'TableLogVariable_CONFIDENCE'; the transaction was rolled back. Ensure that the command syntax is correct.]!

  Wed Dec 12 08:27:00 2012 - ExecuteSync (c#) for [Job_srw_Data][TableLogVariable_QUALITY_FACTOR] Returned = [Failed to execute the command 'InsertCommand' for table 'TableLogVariable_QUALITY_FACTOR'; the transaction was rolled back. Ensure that the command syntax is correct.]!

  This functionality is an itegral part of the application we are developing, and this MUST run. Are there "special" settings needed to make this work ?

  Steve

  Wednesday, December 12, 2012 2:36 PM
• 

  

  0

  Sign in to vote

  just because they all were made by MS doesn't mean they all work and play along together... and no, Service Broker is not an application by itself, you configure everything that it needs to do.

  also, the process a creating and sending a notification actually becomes part of the transaction of the statement that cause the notification...if the notification cannot be successfully created/sent, the statement fails as well.

  have you checked your SQL Server logs for errors?

  Wednesday, December 12, 2012 3:13 PM
• 

  

  0

  Sign in to vote

  just because they all were made by MS doesn't mean they all work and play along together

  I think this is the case. When I run the Sync without trying to do sql notifications, everything works fine. When I run SQL notifications without using any sync framework, the sql notifications work just fine. It's only when the two are put together the problems occur. I suspect the ALTER DATABASE calls in the SQL Notification service broker setup are killing the sync framework connections, since it's generally only 2 or 3 syncs (out of 14) that die.

  Our process uses SQL CE databases on remote machines.,since we don't always have cloud connectivity. When we have connectivity, our thought was to use Sync Framework to keep a SQL Database on the cloud updated as close to real time as possible. Our remote monitoring application would utilize SQL Notifications to tell us when a table changed on the SQL Server db in the cloud, and to allow us to fetch only the changed data and display on the remote monitoring machine.

  I am going to try and put some code in to re-start those syncs which are killed in the SQL Notification start, to see if we can get around this.

  Steve

  Wednesday, December 12, 2012 4:22 PM
• 

  

  0

  Sign in to vote

  when you get a notification and you initiate a sync, are you sure you're not getting a subsequent notification that causes another sync session to start?

  Thursday, December 13, 2012 3:10 AM
• 

  

  0

  Sign in to vote

  No. the Syncs are all already running prior to any notifications being registered. I had the application in debug mode, and when I sent the SQL statement that setup the notification broker, that's when the Syncs started failing. I put a fix in to re-start the syncs, and everything works ok for now. It appears that the "ALTER DATABASE" calls will abrubtly terminate connections, and I suspect that is the root of the problem.

  Steve

  Thursday, December 13, 2012 1:02 PM
• 

  

  0

  Sign in to vote

  that's where am confused.

  if you're using SQL Notification to determine if you need to synchronize, then you should enable SQL Notification first, when you get notified, you initiate a sync. I'm not sure why you have to enable notification when syncs are on-going.

  Friday, December 14, 2012 9:32 AM
• 

  

  0

  Sign in to vote

  The "main" application is running on a machine sitting on a remote site. It monitors well site operations and collects data. It stores all data locally in an SQL CE database. These sites are notorious for intermitant network connections, so we have to store locally. These machines are NOT high powered, they are single core, limited memory running Windows XP. We use Sync Framework to sync to the cloud when we have connection.

  The "office" machines are used to connect ONLY to the cloud, and gather as close-to-real-time as we can data to monitor well site operations. It is THIS software that intiates SQL Notification, since it cannot initiate syncs, as the SQL CE database in on the remote machine. So, when the remote machine application runs, it starts/manages the sync operations.

  Steve

  Monday, December 17, 2012 3:14 PM
• 

  

  0

  Sign in to vote

  Exactly I faced with this Error.

  have you find the solution ?

  Thursday, February 14, 2019 7:12 AM