none
Sync Framework and SQL Notifications RRS feed

  • Question

  • I have an application that is running Sync Framework to keep a local SQL CE database in sync with a remote SQL Server database. My other application is using SQL Notifications to tell it when any changes are made to the SQL Server database and will act on them.  In this scenario, it should see changes as a result of the sync are happening to the server.  It appears that when I have an active Sync in progress and I setup a SQL Notification on the same table, I start getting errors such as this:

    [03/12/12 09:39:39]: Error in Sync (SyncException) Failed to execute the command 'SelectRowCommand' for table 'TableMudflowPulses'; the transaction was rolled back. Ensure that the command syntax is correct. Inner:System.Data.SqlClient.SqlException (0x80131904): 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.)
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParserStateObject.WriteSni()
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
       at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
       at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.Synchronization.Data.DbChangeHandler.ExecuteSelectRowCommand(SyncRowMetadata rowMetadata, DataTable& dataTable)--Sync-0
    Monday, December 3, 2012 3:50 PM

All replies

  • 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
    Moderator
  • 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
  • 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
    Moderator
  • 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
  • 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
    Moderator
  • 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
  • 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
    Moderator
  • 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
  • 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
    Moderator
  • 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
  • Exactly I faced with this Error.

    have you find the solution ?

    Thursday, February 14, 2019 7:12 AM