Most Popular
Recently Added
Recently Updated

Sending SQL Events on SQL Server

Install the following procedures. Use the following syntax to send sql to a specific node:

exec sym_send_sql '1', 'create table test (id integer, note varchar(10), primary key (id))';

create procedure dbo.sym_next_seq(@sequence_name varchar(50), @next_value decimal(19) output) as
begin
  declare @current_value decimal(19);
  declare @increment_by integer;
  declare @success integer = 0;
  while (@success = 0) begin
    select @current_value = current_value, @increment_by = increment_by from sym_sequence where sequence_name=@sequence_name;  
    update sym_sequence set current_value=@current_value+1 where sequence_name=@sequence_name and current_value=@current_value;
    SET @success = @@ROWCOUNT
  end
  set @next_value = @current_value+1
end

create procedure dbo.sym_send_sql(@node_id varchar(50), @sql varchar(max))
as
begin
  declare @trigger_hist_id integer;
  declare @batch_id decimal(19);
  declare @data_id decimal(19);
  declare @channel_id varchar(50) ='config';
  declare @table_name varchar(100) = 'sym_trigger';
  select @trigger_hist_id = max(trigger_hist_id) from sym_trigger_hist where source_table_name=@table_name;
  begin transaction
    insert into sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, create_time) values(@table_name,'S',@trigger_hist_id, '"' + replace(@sql, '"','\"') + '"',@channel_id,current_timestamp);
    set @data_id = @@identity;
    exec sym_next_seq 'outgoing_batch', @batch_id output;
    insert into sym_outgoing_batch(batch_id, node_id, channel_id, status, data_event_count, other_event_count, last_update_time, create_time) values(@batch_id, @node_id, @channel_id, 'NE', 1, 1, current_timestamp, current_timestamp);
    insert into sym_data_event (data_id, batch_id, router_id, create_time) values(@data_id, @batch_id, '?', current_timestamp);
    if @@error  0 begin
      rollback
      raiserror ('There was an error inserting the sql event', 16, 1);
      return
    end
  commit
end

Properties ID: 000010   Views: 1995   Updated: 11 years ago
Filed under: