create procedure [sbm].[usp_create_transport](@host nvarchar(256) = null, @ref_tt_str nvarchar(32) = null)
as
set nocount on

  declare @instance nvarchar(256) = '\sqlexpress'
  declare @db_name nvarchar(256) = 'db_sup_kkm_stage'

  if @host is null and @ref_tt_str is null return

  if @ref_tt_str is null and @host is not null
    select @ref_tt_str = ref_tt_str, @instance = isnull([ИнстансSQL], @instance), @db_name = isnull([ИмяБД], @db_name) from sbm.uv_dimTT where [СерверХост] = @host
  else if @ref_tt_str is not null and @host is null
	select @host = [СерверХост], @instance = isnull([ИнстансSQL], @instance), @db_name = isnull([ИмяБД], @db_name) from sbm.uv_dimtt where ref_tt_str = @ref_tt_str

  declare @packedhostname nvarchar(256) 
  set @packedhostname = sbm.ufn_get_packedHostName(@host)
  
  declare @pLinkedServer nvarchar(256) = 'supkkm_' + @packedhostname;
  declare @pServerName nvarchar(256) = @host + @instance
  exec sbm.usp_createlinkedserver @pLinkedServer, @pServerName, N'master'

  declare @sql_str nvarchar(max)
  
  declare @target_cert varchar(max)
  set @sql_str = 'select @target_cert = tcert
    from openrowset(
      ''SQLOLEDB'', ''Server=SRV-MASTER-SBM;Database=master;Uid=xxxxxx;Pwd=xxxxxx;Timeout=10;Network Library=dbmssocn;'',
      ''select convert(varchar(max), certencoded(cert_id(''''cer_sup_kkm_target_30171001'''')), 1) tcert'')
  '
  exec sp_executesql @stmt = @sql_str, @params = N'@target_cert varchar(max) output', @target_cert = @target_cert output

  set @sql_str = 'exec (''' + 
    replace('use [master];
	if not exists (select * from sys.symmetric_keys where name = ''##ms_databasemasterkey##'')
	  create master key encryption by password = ''--<<Очень-Страшно-Сложный-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
	create certificate cer_@@packedhostname@@ with subject = ''cer_@@packedhostname@@ certificate'', start_date = ''20171001'', expiry_date = ''30171001'';
	create endpoint ep_sup_kkm state = started as tcp ( listener_port = 4022 ) for service_broker (authentication = certificate cer_@@packedhostname@@, encryption = disabled);
	create login lg_sup_kkm with password = ''--<<Очень-Страшно-Сложный-Совсем-Не-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
	create user usr_sup_kkm for login lg_sup_kkm;
	create certificate cer_sup_kkm_target_30171001 authorization usr_sup_kkm from binary = @@target_cert@@;
	grant connect on endpoint::ep_sup_kkm to lg_sup_kkm;'
	, '''', '''''') +
  ''') AT [@@pLinkedServer@@]'

  set @sql_str = replace(@sql_str, '@@ref_tt_str@@', @ref_tt_str)
  set @sql_str = replace(@sql_str, '@@packedhostname@@', @packedhostname)
  set @sql_str = replace(@sql_str, '@@target_cert@@', @target_cert)
  set @sql_str = replace(@sql_str, '@@pLinkedServer@@', @pLinkedServer)

  exec sp_executesql @sql_str  -- execute at remote
  
  declare @remote_cert varchar(max)
  set @sql_str = 'select @remote_cert = rcert
    from openrowset(
      ''SQLOLEDB'',  ''Server=' + @host + @instance + ';Database=master;Uid=xxxxxx;Pwd=xxxxxx;Timeout=10;Network Library=dbmssocn;'',
      ''select convert(varchar(max), certencoded(cert_id(''''cer_' + @packedhostname +''''')), 1) rcert'')
  '
  exec sp_executesql @stmt = @sql_str, @params = N'@remote_cert varchar(max) output', @remote_cert = @remote_cert output

  set @sql_str = 'exec (''' + 
    replace('use [master];
	create login lg_@@packedhostname@@ with password = ''--<<Очень-Страшно-Сложный-Совсем-Не-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
	create user usr_@@packedhostname@@ for login lg_@@packedhostname@@;
	create certificate cer_@@packedhostname@@ authorization usr_@@packedhostname@@ from binary = @@remote_cert@@;
    grant connect on endpoint::ep_sup_kkm to lg_@@packedhostname@@;' 
	, '''', '''''') +
  ''')'
 
  set @sql_str = replace(@sql_str, '@@host@@', @host)
  set @sql_str = replace(@sql_str, '@@db_name@@', @db_name)
  set @sql_str = replace(@sql_str, '@@ref_tt_str@@', @ref_tt_str)
  set @sql_str = replace(@sql_str, '@@packedhostname@@', @packedhostname)
  set @sql_str = replace(@sql_str, '@@remote_cert@@', @remote_cert)
  set @sql_str = replace(@sql_str, '@@pLinkedServer@@', @pLinkedServer)

  exec sp_executesql @sql_str -- execute at local
  
  exec sbm.usp_droplinkedserver @pLinkedServer

go