-- Создание loopback для логгинга в отдельной транзакции
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'loopback') EXEC master.sys.sp_dropserver 'loopback','droplogins'
go
DECLARE @database VARCHAR(200) = DB_NAME();
EXEC master.dbo.sp_addlinkedserver @server = N'loopback', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=@@SERVERNAME, @catalog = @database
go
EXEC master.dbo.sp_serveroption loopback, 'RPC', 'TRUE'
go
EXEC master.dbo.sp_serveroption loopback, 'RPC OUT', 'TRUE'
go
EXEC master.dbo.sp_serveroption loopback, 'remote proc transaction promotion', 'FALSE'
go

-- логгируем действия
Declare @procedure sysname;
Set @procedure = Quotename(OBJECT_SCHEMA_NAME(@@PROCID)) + N'.' + Quotename(OBJECT_NAME(@@PROCID));
Declare @guid uniqueidentifier = NEWID();

EXEC loopback.[db1].[load].[ToLOG] @filial_id=@filial_id
		,@guid     =@guid
		,@procedure=@procedure
		,@action   =0

exec [SRV-REMOTE].[db1].[load].[Эта самая говенная процедура с loopback внутри] @filial_id=@filial_id

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'loopback',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

--  0 - Процедура начата
--  1 - процедура успешно завершена
-- -1 - процедура завершена с ошибкой

CREATE PROC [load].[ToLOG] 
    @filial_id smallint,
    @guid uniqueidentifier,
    @procedure sysname,
    @action smallint,
    @message nvarchar(max) = NULL
As
BEGIN
    Set NOCOUNT ON;
    
    insert into [load].[log] ([guid], [date], filial_id, [procedure], [action], [message])
				  Values (@guid, GETDATE(), @filial_id, @procedure, @action, Try_convert(xml, @message))

END
GO

CREATE PROCEDURE [load].[enqueue]
	@filial_id int = Null
	With execute as 'transferter'
AS
BEGIN
	SET NOCOUNT ON;
	exec [SRV002]..[load].[enqueue] @filial_id = @filial_id
END
GO

-- логгируем действия
Declare @procedure sysname;
Set @procedure = Quotename(OBJECT_SCHEMA_NAME(@@PROCID)) + N'.' + Quotename(OBJECT_NAME(@@PROCID));
Declare @guid uniqueidentifier = NEWID();

declare @load_proc sysname = quotename(@@servername) + N'.[db1].[load].[ToLOG]';
EXEC @load_proc @filial_id=@filial_id
		,@guid     =@guid
		,@procedure=@procedure
		,@action   =0