All geek questions in one place

Microsoft Sync Framework - How to redraw a table (or the entire area) after changing a schema?

I have already set up synchronization with the Microsoft Sync Framework, and now I need to add fields to the table. How to reinstall databases?

The setup is very simple:

  • Two SQL Express 2008 servers
  • The scope includes the entire database
  • Using Microsoft Sync Framework 2.0
  • Synchronization through direct access. Using a standard newSqlSyncProvider

Am I making structural changes from both ends? Or can I only change one server and allow the Sync Framework to somehow propagate the change?

Do I need to delete tables _trackingand / or stored procedures? What about triggers?

Has anyone used Sync Framework? Please, help.

+3
source share
4 answers

Actually, I posted my own answer on my blog http://myazurejourney.blogspot.com/

He has a few steps, and this is definitely a hack. But it works.

check this. tell me what you think

+2
source

delete region and resubmit ... use this code to delete region

private void RemoveScope(SqlConnection Conn, bool ShowAlert)
{
    foreach (var table in _settings.TablesToSync)
    {
        SqlCommand dropTracking = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_tracking]') AND type in (N'U'))
            DROP TABLE [dbo].[" + table.Key + "_tracking]", Conn);
        dropTracking.ExecuteNonQuery();

        SqlCommand dropTriggers = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_delete_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_insert_trigger];

            IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update_trigger]'))
            DROP TRIGGER [dbo].[" + table.Key + @"_update_trigger];
        ", Conn);
        dropTriggers.ExecuteNonQuery();

        SqlCommand dropStoredProc = new SqlCommand(@"
            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_delete];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_deletemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_deletemetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insert];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insertmetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_insertmetadata];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectchanges]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectchanges];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectrow]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_selectrow];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_update];

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_updatemetadata]') AND type in (N'P', N'PC'))
            DROP PROCEDURE [dbo].[" + table.Key + @"_updatemetadata];
        ", Conn);
        dropStoredProc.ExecuteNonQuery();
    }

    SqlCommand getScopeGuid = new SqlCommand(@"
            USE [" + Conn.Database + @"]
            SELECT scope_config_id FROM scope_info WHERE scope_name = '" + _settings.ScopeName + "'", Conn);

    var reader = getScopeGuid.ExecuteReader();

    if (reader.HasRows)
    {
        reader.Read();

        var id = reader.GetGuid(0);

        reader.Close();

        SqlCommand deleteScope = new SqlCommand(@"
                DELETE FROM scope_info WHERE scope_config_id = '" + id + @"';
                DELETE FROM scope_config WHERE config_id = '" + id + @"';
            ", Conn);
        deleteScope.ExecuteNonQuery();
    }

    if(ShowAlert)
        MessageBox.Show("Scope has been removed");
}
0
source

Montago, SQL, . , , :)

USE DatabaseName
GO

DECLARE @sqlDrop VARCHAR(1000)
SET @sqlDrop = 'IF EXISTS (SELECT * FROM #table# WHERE object_id = OBJECT_ID(''#name#'')) DROP #what# #name#'
DECLARE @sqlCommand VARCHAR(1000)

DECLARE @id INT
SET @id = 0
DECLARE @name SYSNAME
DECLARE @prev INT

WHILE 1 = 1
   BEGIN
      /* find traces of synchronization */

      -- to be sure that id changed
      SET @prev = @id

      -- get the next table
      SELECT TOP 1
             @id = object_id,
             @name = name
        FROM sys.tables
       WHERE object_id > @id
       ORDER BY object_id

      -- confirm that there is next table
      IF @id = @prev
         BREAK

      /* remove traces of synchronization */

      -- remove table
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_tracking')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
      EXEC (@sqlCommand)

      -- remove triggers
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.triggers')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete_trigger')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TRIGGER')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete_trigger', '_insert_trigger')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert_trigger', '_update_trigger')
      EXEC (@sqlCommand)

      -- remove stored procedures
      SET @sqlCommand = @sqlDrop
      SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.procedures')
      SET @sqlCommand = REPLACE(@sqlCommand, '#name#', @name + '_delete')
      SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'PROCEDURE')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_delete', '_deletemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_deletemetadata', '_insert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insert', '_insertmetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_insertmetadata', '_selectchanges')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectchanges', '_selectrow')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_selectrow', '_update')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_update', '_updatemetadata')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_updatemetadata', '_bulkdelete')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkdelete', '_bulkinsert')
      EXEC (@sqlCommand)
      SET @sqlCommand = REPLACE(@sqlCommand, '_bulkinsert', '_bulkupdate')
      EXEC (@sqlCommand)

   END

-- remove scope and schema tables
SET @sqlCommand = @sqlDrop
SET @sqlCommand = REPLACE(@sqlCommand, '#table#', 'sys.tables')
SET @sqlCommand = REPLACE(@sqlCommand, '#name#', 'schema_info')
SET @sqlCommand = REPLACE(@sqlCommand, '#what#', 'TABLE')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'schema_info', 'scope_config')
EXEC (@sqlCommand)
SET @sqlCommand = REPLACE(@sqlCommand, 'scope_config', 'scope_info')
EXEC (@sqlCommand)

This, as you can see, goes through all the tables and tries to find traces of synchronization. You just need to change the database name (first line). Also, if you want to be more secure with what you delete, use this code to search for tables:

  -- get the next table
  SELECT TOP 1
         @id = object_id,
         @name = REPLACE(name, '_tracking', '')
    FROM sys.tables
   WHERE object_id > @id
     AND name LIKE '%_tracking'
   ORDER BY object_id

This will only look for tables that are actually synchronized.

0
source

You can use this script:

declare @SQL varchar(max);
set @SQL='';
select @SQL += ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[schema_info]'') AND type in (N''U'')) ' +  
               ' DROP TABLE [dbo].[schema_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_config]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_config]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[scope_info]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[scope_info]; ' +
               ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' +  name  + '_tracking]'') AND type in (N''U'')) ' + 
               ' DROP TABLE [dbo].[' +  name  + '_tracking]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_delete_trigger];' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_insert_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update_trigger]'')) ' +
               ' DROP TRIGGER [dbo].[' + name + '_update_trigger]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_delete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_delete]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_deletemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_deletemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insert]; ' + 
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_insertmetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_insertmetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectchanges]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectchanges]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_selectrow]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_selectrow]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_update]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_update]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_updatemetadata]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_updatemetadata]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_BulkType]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_BulkType]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkinsert]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkinsert]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkupdate]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkupdate]; ' +
               ' IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + name + '_bulkdelete]'') AND type in (N''P'', N''PC'')) ' +
               ' DROP PROCEDURE [dbo].[' + name + '_bulkdelete]; '              
from sysobjects
where type = 'U'

--select @SQL
EXEC(@SQL)
GO
0
source

Source: https://habr.com/ru/post/1747717/

More articles:


All Articles