Go to Main page..

Service broker for logging, glitches (Page 1) - Databases - Programmer's Town





Service broker for logging, glitches [new]
Recently decided to upgrade the existing logging system and a number of "automation" using ServiceBroker. It seems like asynchronous logging will not slow down, but in case of a critical load, the queue will be gradually queued up in order. Since this is sort of like “another thread / connection”, you do not need to steam it in order not to lose the logs during rollback.

I made contracts, queues, activators, test scripts - everything is OK. He does everything as it should, he also catches mistakes (he tried to divide by zero), nothing breaks.

But then the magic begins.

I start a pack unit tests (from C # are called with "new" journaling). No errors. Logs are empty. The queue is empty.
I launch a separate test through the debugger - all the rules, there is data in the logs. I start HP from SMS - there are logs.

Debugging debugger in the SMS in which there is an error (access to a non-existent object). I go step by step, and where logs are to be written, the table is empty, neither before the error, nor the error itself, nor after. Log presets are called, messages are sent to the queue, but the activator is not called.
I start without a debugger with an “error” - there are no logs either.
I launch the .NET application, press the buttons, other CPs with the same logging are called, there are logs.
Here again debugger in SMS - no logs.
I correct the store, so that there is no error, I launch it, without debugging - there are logs.


This was the end of the working day. Now I sit and break my head. Is it something that's buggy for me, or is there some other broker mode of operation in the debugger mode? Or if the error is more serious than the division by zero, then the entry in the queue dies and is it somehow calculated? And does the activator of the broker work not quite in the “separate thread” and “separate connection to the database”?
Or a broker for logs is not used Camelfo? Are there any nuances that I did not take into account?
Where logs disappear when unit tests work in packs?

server - 2014 Standart
Re: Service broker for logging, glitches [new]
I work with this broker article
https://germangorelkin.blogspot.com/2017/07/ms-sql-server.html
Re: Service broker for logging, glitches [new]
Kifirchik
Since this is sort of like “another thread / connection”, you do not need to steam it in order not to lose the logs during rollback.
This is exactly what you need to "steam", because when a transaction is rolled back, everything that was sent to the queue is also rolled back.

Cost through event notifications - 17329722
Re: Service broker for logging, glitches [new]
invm,
I understand correctly that it is “on the fingers”, this is about how I did, except that getting the message into the queue is not through the dialogue with the conversation conversation, but using sp_trace_generateevent?
Re: Service broker for logging, glitches [new]
Kifirchik
I understand correctly that it is “on the fingers”, this is about how I did, except that getting the message into the queue is not through the dialogue with the conversation conversation, but using sp_trace_generateevent?
Those. is the difference in behavior when you roll back a transaction for you is irrelevant?
Re: Service broker for logging, glitches [new]
invm, of course essential
I meant the implementation and not the behavior.
and apparently the type of message service only [ http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] ?
Re: Service broker for logging, glitches [new]
Kifirchik
I meant the implementation and not the behavior.
Realization yes, through SB.
Re: Service broker for logging, glitches [new]
invm,
blinded from your workpiece and your code. vskidku, did not miss anything?
+
 -- alter database db2_log set enable_broker with rollback immediate; - Enable Service Broker - alter database db2_log set enable_broker with rollback immediate;  DB2_LOG go if exists ( select 1 from sysobjects where id = object_id ( 'SendLogMessage' ) and type in ( 'P' , 'PC' )) drop procedure SendLogMessage if exists ( select 1 from sysobjects where id = object_id ( 'AsyncLogQueueActivation' ) and type in ( 'P' , 'PC' )) drop procedure AsyncLogQueueActivation if exists ( select 1 from sysobjects where id = object_id ( 'XmlMessageToTable' ) and type in ( 'P' , 'PC' )) drop procedure XmlMessageToTable if exists ( select 1 from sysobjects where id = object_id ( 'SimpleAuditData' ) and type in ( 'U' )) drop table SimpleAuditData if exists ( select 1 from sys . services where name = 'AsyncLogService' ) drop service AsyncLogService if exists ( select 1 from sys . service_queues where name = 'AsyncLogQueue' ) drop queue AsyncLogQueue ; use DB2_LOG go if exists ( select 1 from sysobjects where id = object_id ( 'SendLogMessage' ) and type in ( 'P' , 'PC' )) drop procedure ) and type in ( 'P' , 'PC' )) drop procedure AsyncLogQueueActivation if exists ( select 1 from sysobjects where id = object_id ( 'XmlMessageToTable' ) and type in ( 'P' , 'PC' )) exists ( select 1 from sysobjects where id = object_id ( 'SimpleAuditData' ) and type in ( 'U' )) drop table If you have ( select 1 from sys . services where name = = AsyncLogService ' ) drop service AsyncLogService if exists ( select 1 from sys . Service_queues where name = 'AsyncLogQueue' ) drop queue AsyncLogQueue ;  exists ( select * from sys . server_event_notifications where name = 'asyncLogEventNotify' ) drop event notification asyncLogEventNotify on server --- ---------------------------------------------- go -- Создаем очередь и сервисы if object_id ( 'AsyncLogQueueActivation' , 'P' ) is null exec ( 'create procedure AsyncLogQueueActivation as begin set nocount on; end;' ); if exists ( select * from sys . server_event_notifications where name = 'asyncLogEventNotify' ) drop event notification asyncLogEventNotify on server --- ------------------------- --------------------- go - Creating a queue and services if object_id ( 'AsyncLogQueueActivation' , 'P' ) is null exec ( 'create procedure AsyncLogQueueActivation as begin set nocount on; end; ' );  queue AsyncLogQueue with status = on , retention = off , activation ( status = on , procedure_name = AsyncLogQueueActivation , max_queue_readers = 5 , execute as owner ), poison_message_handling ( status = on ); go create queue AsyncLogQueue with status = on , retention = off , activation ( status = on , procedure_name = AsyncLogQueueActivation , max_queue_readers = 5 , execute as owner ), poison_message_handling ( status = on );  service AsyncLogService authorization dbo on queue AsyncLogQueue ( [ http: // schemas . microsoft . com / SQL / Notifications / PostEventNotification ] ); go create service AsyncLogService authorization dbo on queue AsyncLogQueue ( [ http: // schemas . microsoft . com / SQL / Notifications / PostEventNotification ] );  table SimpleAuditData ( ad_id int identity , ad_OccurredAt datetime not null , ad_Login sysname , ad_Application nvarchar ( 128 ), ad_Host nvarchar ( 128 ), ad_ShortText nvarchar ( 128 ), ad_LongText nvarchar ( 4000 ), Stamp timestamp , constraint PK_SimpleAuditData primary key ( ad_id ) ); go create table SimpleAuditData ( ad_id int identity , ad_OccurredAt datetime not null , ad_Login sysname , ad_Application nvarchar ( 128 ), ad_Host nvarchar ( 128 ), ad_ShortText nvarchar ( 128 ), ad_LongText nvarchar ( 4000 ) ) );  create procedure XmlMessageToTable @message xml as begin with s as ( select n . value ( 'EventType[1]' , 'sysname' ) as EventType , n . value ( 'PostTime[1]' , 'datetime' ) as PostTime , n . value ( 'TextData[1]' , 'sysname' ) as TextData , n . value ( 'BinaryData[1]' , 'varbinary(8000)' ) as BinaryData , n . value ( 'DatabaseID[1]' , 'int' ) as DatabaseID , n . value ( 'DatabaseName[1]' , 'sysname' ) as DatabaseName , n . value ( 'ApplicationName[1]' , 'sysname' ) as ApplicationName , n . value ( 'HostName[1]' , 'sysname' ) as HostName , n . value ( 'SessionLoginName[1]' , 'sysname' ) as SessionLoginName from @message . nodes ( '/EVENT_INSTANCE' ) x ( n ) ) insert into SimpleAuditData ( ad_OccurredAt , ad_Login , ad_Application , ad_Host , ad_ShortText , ad_LongText ) select s . go - the HP that parses the XML and writes it to the table create procedure XmlMessageToTable @message xml as it is chosen ( select n . value ( 'EventType [1]' , 'sysname' ) as EventType , n . value ( 'PostTime [ 1] ' , ' datetime ' ) as PostTime , n . Value ( ' TextData [1] ' , ' sysname ' ) as TextData , n . Value ( ' BinaryData [1] ' , ' varbinary (8000) ' ) as BinaryData , n . value ( 'DatabaseID [1]' , 'int' ) as DatabaseID , n . value ( 'DatabaseName [1]' , 'sysname' ) as DatabaseName , n . value ( 'ApplicationName [1]' , 'sysname' ) as ApplicationName , n . value ( 'HostName [1]' , 'sysname' ) as HostName , n . value ( 'SessionLoginName [1]' , 'sysname' ) as SessionLoginName from @message . nodes ( '/ EVENT_INSTANCE' ) x ( n ) ) insert into SimpleAuditData ( ad_OccurredAt , ad_Login , ad_Application , ad_Host , ad_ShortText , ad_LongText ) select s .  s . PostTime , s .  s . SessionLoginName , s .  s . ApplicationName , s .  s . HostName , s .  cast ( s . BinaryData as nvarchar ( 4000 )) from s --where DatabaseID = db_id(); TextData , cast ( s . BinaryData as nvarchar ( 4000 )) from s --where DatabaseID = db_id ();  -- Процедура которая будет принимать сообщения и обрабатывать их go alter procedure AsyncLogQueueActivation with execute as owner as begin set nocount on ; end - A procedure that will receive messages and process them. go alter procedure AsyncLogQueueActivation with ;  @handle uniqueidentifier ; declare @handle uniqueidentifier ;  @message xml ; declare @message xml ;  @message_type sysname ; declare @message_type sysname ;  ( 1=1 ) begin begin transaction ; while ( 1 = 1 ) begin transaction ;  ( receive top ( 1 ) @handle = conversation_handle , @message = CAST ( message_body AS XML ), @message_type = message_type_name from AsyncLogQueue ), timeout 500 ; waitfor ( receive top ( 1 ) @handle = conversation_handle , @message = CAST ( message_body AS XML ), @message_type = message_type_name from AsyncLogQueue ), timeout 500 ;  ( @@rowcount = 0 ) begin rollback transaction ; if ( @@ rowcount = 0 ) begin rollback transaction ;  'Error - нет данных в очереди' break ; print 'Error - no data in the queue' break ;  if @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' begin exec XmlMessageToTable @message end else if @message_type = N 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' begin end conversation @handle with cleanup ; end if @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' begin exec XmlMessageToTable @message end then if @message_type = N 'http://schemas.microsoft.com/SQL/ServiceBroker/ EndDialog ' begin end conversation @handle with cleanup ;  else if @message_type = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' begin -- TODO --log errors end conversation @handle with cleanup ; end else if @message_type = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' begin - TODO --log errors end conversation @handle with cleanup ;  commit transaction ; end commit transaction ;  end go -- привязываем пользовательское событие к службе create event notification asyncLogEventNotify on server for USERCONFIGURABLE_0 -- event_id = 82 to service 'AsyncLogService' , 'current database' ; end end go - bind a custom event to the create event notification service asyncLogEventNotify on server for USERCONFIGURABLE_0 - event_id = 82 to service 'AsyncLogService' , 'current database' ;  go create procedure SendLogMessage @logMsg nvarchar ( 128 ), @logData nvarchar ( 1024 ) as begin set nocount on ; go - A general procedure with which you can send messages go create procedure SendLogMessage @logMsg nvarchar ( 128 ), @logData nvarchar ( 1024 ) as begin set nocount on ;  -- [ @eventid = ] event_id -- [ , [ @userinfo = ] 'user_info' ] -- [ , [ @userdata = ] user_data ] -- event_id - numbers from 82 through 91 declare @b varbinary ( 8000 ) = cast ( @logData as varbinary ( 8000 )); - sp_trace_generateevent - [@eventid =] event_id - [, [@userinfo =] 'user_info'] - [, [@userdata =] user_data] - event_id - numbers from 82 through 91 declare @b varbinary ( 8000 ) = cast ( @logData as varbinary ( 8000 ));  sp_trace_generateevent 82 , @logMsg , @b ; exec sp_trace_generateevent 82 , @logMsg , @b ;  go exec SendLogMessage 'message1' , 'data1' exec SendLogMessage 'message2' , 'data2' exec SendLogMessage 'message3' , 'data3' exec SendLogMessage 'message4' , 'data4' waitfor delay '00:00:01' select * from SimpleAuditData end go exec SendLogMessage 'message1' , 'data1' exec SendLogMessage 'message2' , 'data2' exec SendLogMessage 'message3' , 'data3' exec SendLogMessage 'message4' , 'data4 Simpleauditdata 
Re: Service broker for logging, glitches [new]
Kifirchik
vskidku, did not miss anything?
It seems not.
Re: Service broker for logging, glitches [new]
Kifirchik,

tests roll back transactions and broker messages with them. You need to use event notification mechanism for logging, which sends messages to the broker, regardless of rollback.
Re: Service broker for logging, glitches [new]
Thank you all for the tips. Earned on "combat" base.
The truth still had to dance with the old women.
dopilivanie so that on the basis of this all started (maybe someone will come in handy)
 db_log use db_log
 go
 sp_changedbowner 'sa' exec sp_changedbowner 'sa'
 go
 database db_log set trustworthy on alter database db_log set trustworthy on
 go
 database db_log set enable_broker with rollback immediate ; alter database db_log set enable_broker with rollback immediate ; 
 go

 master ; use master ;
 go
 alter trace to user1 grant alter trace to user1
 go


I admit not fully realized the consequences of trustworthy and changedbowner, but it worked)))

and inside the message handler I had to add a line
set QUERY_GOVERNOR_COST_LIMIT 0;
otherwise the broker crashed with an error
author
The query threshold of 6000 has been canceled.

Seeing the processing of XML with its data crammed into BinaryData has turned out to be relatively resource-intensive.
Re: Service broker for logging, glitches [new]
Kifirchik,

Right, now your any db_owner can become a server sysadmin.
Re: Service broker for logging, glitches [new]
Kifirchik
I admit not fully realized the consequences of trustworthy and changedbowner, but it worked)))
And why from the activation procedure to access external resources?
Re: Service broker for logging, glitches [new]
Kifirchik,

By the way, you should also keep in mind that the dialogs are not closed for event notifications. they hang until the notification is removed.
If you have this system of logging, it assumes to work with a high load, you should be concerned about what you will do with open dialogues.
Re: Service broker for logging, glitches [new]
felix_ff
Kifirchik,

Right, now your any db_owner can become a server sysadmin.


invm
> I admit I did not fully realize the consequences of trustworthy and changedbowner, but it worked)))

And why from the activation procedure to access external resources?


please check the question regarding trustworthy or changedbowner?
Re: Service broker for logging, glitches [new]
Kifirchik,

aggregate.

having made the user from the sysadmin group a database manager, you have extended its rights; any user of the db_owner group can make
execute as user = 'dbo', thereby switching itself to the context 'sa', but it is still closed in the database.

having put trustworthy you opened a sandbox, thereby the user is not locked in base now and gets access of the rights of the sysadmin level
Re: Service broker for logging, glitches [new]
felix_ff
Kifirchik,

By the way, you should also keep in mind that the dialogs are not closed for event notifications. they hang until the notification is removed.
If you have this system of logging, it assumes to work with a high load, you should be concerned about what you will do with open dialogues


doesn't it save?
         waitfor
         (
           ( 1 ) receive top ( 1 )
               @handle = conversation_handle
             @msgBody = cast ( message_body as xml ) , @msgBody = cast ( message_body as xml )
             -, @msgBodyStr = cast (message_body as nvarchar (max))
             @msgType = message_type_name , @msgType = message_type_name
           AsyncLogQueue from AsyncLogQueue
         timeout 500 ; ), timeout 500 ;

         ....

         if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' else if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
         begin
             conversation @handle with cleanup ; end conversation @handle with cleanup ;
         end
         if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' else if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
         begin
             - TODO
             --log errors
             conversation @handle with cleanup ; end conversation @handle with cleanup ;
         end 
Re: Service broker for logging, glitches [new]
Kifirchik,

will not save.

event triggering sends only the message type to the queue [ http://schemas.microsoft.com/SQL/Notifications/EventNotification]

no enddialog comes to it, therefore your lower if branches will never work.

[ http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] will go to the queue only when the notification notification is removed drop event notification

here you essentially need to organize the logic of the subtracted message closed the dialogue from the goal.

    @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' if @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
             begin
                 XmlMessageToTable @message exec XmlMessageToTable @message
                 conversation @handle ; end conversation @handle ;
             end
Re: Service broker for logging, glitches [new]
Kifirchik,

the dialogue must be closed from two sides - on service A and on service B. Service A in this case is controlled by the Event Notification system, and service B is your procedure. You receive and process the message and close the dialogue on your side, regardless of the type of message received.
Re: Service broker for logging, glitches [new]
felix_ff,
 @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' if @message_type = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
             begin
                 XmlMessageToTable @message exec XmlMessageToTable @message
                 conversation @handle ; end conversation @handle ;
             end

In this embodiment, the queue processes only one first message, I do not see all the other sp_trace_generateevent (((


Vladislav Kolosov
the dialogue must be closed from two sides - on service A and on service B. Service A in this case is controlled by the Event Notification system, and service B is your procedure. You receive and process the message and close the dialogue on your side, regardless of the type of message received.

I do not quite understand the "close for my part"

For my part, I call
   @a nvarchar ( 128 ) = 'AsyncLogMsg' declare @a nvarchar ( 128 ) = 'AsyncLogMsg'
    @b varbinary ( 8000 ) = cast ( @messageXml as varbinary ( 8000 )); declare @b varbinary ( 8000 ) = cast ( @messageXml as varbinary ( 8000 ));
    sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;

How to close the dialogue with "my side"?
or "my side" is the procedure for activating the queue?
Re: Service broker for logging, glitches [new]
Kifirchik
please check the question regarding trustworthy or changedbowner?
Yes.
Re: Service broker for logging, glitches [new]
Kifirchik,

Well, write so that when you activate the queue, the cycle reads messages from the queue, if suddenly the conversation_handle changes in the cycle or the receive instruction does not return messages, the last read dialogue closes.

 @h uniqueidentifier , @h2 uniqueidentifier while 1=1 begin waitfor ( receive top ( 1 ) @h = conversation_handle , @m = message_body from [ quque ]), timeout 500 if @@rowcount = 0 begin if @h is not null end conversation @h ; declare @h uniqueidentifier , @h2 uniqueidentifier while 1 = 1 begin waitfor ( receive top ( 1 ) @h = conversation_handle , @m = message_body from [ quque ]), timeout 500 if @@ rowcount = 0 begin if @h is not null end conversation @h ;  ; break ;  --логика обработки сообщения if @h2 <> @h and @h2 is not null end conversation @h2 ; end - the logic of processing a message if @ h2 <> @h and @ h2 is not null end conversation @ h2 ;  @h2 = @h ; set @ h2 = @h ;  end 


and it is better to read into the buffer
 @buffer table ( h uniqueidentifier , message varbinary ( max )); declare @buffer table ( h uniqueidentifier , message varbinary ( max ));
 @tc int , @limit int , @m varbinary ( max ), @sql nvarchar ( max ); declare @tc int , @limit int , @m varbinary ( max ), @sql nvarchar ( max );
 1 = 1 begin while 1 = 1 begin
       ( receive conversation_handle , message_body into @buffer from [ queue ]), timeout 500 waitfor ( receive conversation_handle , message_body into @buffer from [ queue ]), timeout 500
       @tc = @@rowcount ; set @tc = @@ rowcount ;
       @limit + = @tc ; set @limit + = @tc ;
       @tc = 0 or @limit > 100 break ; if @tc = 0 or @limit> 100 break ;
 ; end ;

 cur cursor local fast_forward for select message from @buffer ; declare cur cursor local fast_forward for select message from @buffer ;
 cur ; open cur ;
 1=1 begin while 1 = 1 begin
       next from cur into @m ; fetch next from cur into @m ;
       @@fetch_status <> 0 break ; if @@ fetch_status <> 0 break ;
       <someproc> @m ; exec <someproc> @m ;
 ; end ;
 cur ; close cur ;
 cur ; deallocate cur ;

 @sql = N '' ; set @sql = N " ;
 @sql = @sql + 'end conversation ' + quotename ( cast ( h as nchar ( 50 )), nchar ( 39 )) + nchar ( 59 ) + nchar ( 13 ) + nchar ( 10 ) select @sql = @sql + 'end conversation' + quotename ( cast ( h as nchar ( 50 )), nchar ( 39 )) + nchar ( 59 ) + nchar ( 13 ) + nchar ( 10 )
 @buffer ; from @buffer ;
 ( @sql ); exec ( @sql );
Re: Service broker for logging, glitches [new]
Kifirchik
Recently decided to upgrade the existing logging system and a number of "automation" using ServiceBroker. It seems like asynchronous logging will not slow down, but in case of a critical load, the queue will be gradually queued up in order.


I wonder what the difference is: write in a queue or write to a magazine right away?

Shl. Battle with windmills?
Re: Service broker for logging, glitches [new]
aleks222
Kifirchik
Recently decided to upgrade the existing logging system and a number of "automation" using ServiceBroker. It seems like asynchronous logging will not slow down, but in case of a critical load, the queue will be gradually queued up in order.


I wonder what the difference is: write in a queue or write to a magazine right away?

Shl. Battle with windmills?

Good question, made you think ...
But it would be fair if the entry in the queue was equal to the entry in the table.

We have a simple scheme (entry in the table) has been working for five years, and as practice shows, hell you will understand these records. At the same time, the user-triggered, jobs, triggers (through which we perform automation with the neighboring system) work.
A lot of problems with the answer to the questions of who caused whom, from where, to which particular call the line relates, to which business object. Logs on the storage / modules need to somehow be turned on / off as needed.
In case of an error, the logs are rolled back, and there are no traces of error at all. Well, the triggers of the neighboring system are also rolled back.
As a result, since part of the work is an outsource at the customer, he occasionally calls, and says "well, here I have yesterday, so and so is buggy, sort it out," and rough sex begins with this data stream. And you need a certain "influx" and a bottle of vodka to figure it out and possibly correct the consequences. In general, there is very little use from this log.
This is a “simple” system. And even on it, colleagues watching the performance, commenting log entries get profit.

Recently, a new system was invented that should solve the problems listed above:
- In addition, an ID is written "business process", within which there may be several CPs that also have their own words that are not similar to neighboring IDs.
- there is a field for "business object"
- there is a system on / off logging (via the nameplate)
- the entry is simplified (long, only the first line is indicated with a heap of arguments)
- logs after transaction rollback are not lost (through intermediate storage in a table variable)
That is, logging in such a version is at least two entries on the start / end, and each time the entry is addressed to the settings table and meta information plus an entry in the logs.
This will definitely have an even greater impact on performance.
Also, there are errors due to which the logs are still rolled back, the version with the table variable does not work perfectly.

Yesterday, I launched a test on a laptop in which VERY MANY logs are written through the discussed logging option (via broker and event) (simplified, only insertion without using meta information). Tests for a minute hammered in queue 100k of records which asynchronously minutes 15..20 were pushed into the table of dens. For me, this is an example that it minimally loads stored procedures.

Also, it seems to me that it is extremely important to unleash asynchronously with the triggers of the neighboring system, not in which they execute their stored procedures. And apparently, the doctor ordered to use ServiceBroker.
Now there was an opportunity, and I began to dig in the direction of ServiceBroker, and decided to start with logs.

As for windmills - from the point of view of the fact that there are a lot of other tasks, you have to do it in the background, and even without it it works, it definitely looks like a windmill.
From the point of view of “I want it to be not through W # noo” and less to damn with the analysis of problems - this is not a mill but a fortress on the way to a bright future
Re: Service broker for logging, glitches [new]
Kifirchik
For me, this is an example that it minimally loads stored procedures.


You have already been told: all queues of the broker service are the essence of the table.

Those. your example is written twice.
And this, of course, is faster than once.
Re: Service broker for logging, glitches [new]
felix_ff
Kifirchik,
aggregate.

having made the user from the sysadmin group a database manager, you have extended its rights; any user of the db_owner group can make
execute as user = 'dbo', thus switching itself to the context 'sa', but it is still closed in the database.

having put trustworthy you opened a sandbox, thereby the user is not locked in base now and gets access of the rights of the sysadmin level

What then is the "right" strategy to choose?
make a separate user is not in the db_owner group and not sysadmin, and in the owners of its base?
Re: Service broker for logging, glitches [new]
Kifirchik,

I do not know your infrastructure. if your business logic allows you to have a user with the least privileges, then it is advisable to do this with your own user.

the right strategy if you need to use SAT between databases is to create certificates and configure security based on them.

also with regards to the issue of rights for users: by giving some alter trace user, you gave him the opportunity to create a server trace. so now this login can safely monitor requests, even in those databases in which he himself has not been released.
or podnapryach server some kind of kosher track for catching lock: asquired for example.
in this embodiment, it is better to make the procedure a wrapper, and sign it with a certificate for which you have the appropriate right, and the user is already given the right to execute this wrapper procedure.
Re: Service broker for logging, glitches [new]
Kifirchik,

May already share, why did you need to include trustworthy?
Re: Service broker for logging, glitches [new]
invm
Kifirchik,

May already share, why did you need to include trustworthy?

Yes xs)
Everything works on my computer without it.
At the facility on one server was got, on the other there is not. In the trace, I barely dug up about the message about the lack of rights (approximately).
Compared the base, the difference was in trustworthy.
I remembered that in one of the examples on ServiceBroker I came across that the author included it.
Turned on, re-created the queue and bindings - it worked.

With regards to infrastructure
The application server works with MSSQL, under one login. Plus the adjacent application.
bases, something like this
BASE_TASK1
BASE_TASK2
BASE_TASK3
BASE_LOG
Our application server works from user1, third-party - from user2, both of them have a db_datareader / writer database, and grant exec.
The division into bases is a lot logical, that is, there are connections between the bases. The network is closed, the IT department in the server does not climb. I would say the certificates here are excessive complication, and will complicate the lives of colleagues.
And there are several such servers interconnected by replications.

in the bases BASE_TASK1, BASE_TASK2b, BASE_TASK3 there are wrappers over sp_trace_generateevent, something like a spoiler
+
 procedure log . create procedure log .  spLogSimple 
      ( 255 ) @logProc nvarchar ( 255 )
    @logMsg nvarchar ( 255 ) , @logMsg nvarchar ( 255 )
    @logData nvarchar ( 2048 ) = null , @logData nvarchar ( 2048 ) = null
 as
 begin
     --print 'call log.spLogSimple'
     @messageBody xml = declare @messageBody xml =
     (
          select
             LogProc @logProc as LogProc  
           @logMsg as LogMsg , @logMsg as LogMsg  
           @logData as LogData , @logData as LogData  
           db_name () as LogDb , db_name () as LogDb
           getdate () as LogDateTime , getdate () as LogDateTime
         xml raw ( 'AsyncLogMessageSimple' ), elements for xml raw ( 'AsyncLogMessageSimple' ), elements
     ) 
     --print cast (@messageBody as nvarchar (4000))

     - passed to the event
     @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple'
     @b varbinary ( 8000 ) = cast ( @messageBody as varbinary ( 8000 )); declare @b varbinary ( 8000 ) = cast ( @messageBody as varbinary ( 8000 ));
     sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;

     --print 'finish log.spLogSimple'
 end


in the base BASE_LOG - the queue, the service broker and the queue handler, which calls the HP that makes the insert in BASE_LOG.log.AppLog
+
 procedure log . create procedure log .  execute as owner AsyncLogQueueProcessing with execute as owner
 as
 begin
     nocount on ; set nocount on ;
     ARITHABORT OFF ; SET ARITHABORT OFF ;
     QUERY_GOVERNOR_COST_LIMIT 0 ; set QUERY_GOVERNOR_COST_LIMIT 0 ;

     ...
     ( 1=1 ) while ( 1 = 1 )
     begin
         - begin tran
         waitfor
         (
           ( 1 ) receive top ( 1 )
               @handle = conversation_handle
             @msgBody = cast ( message_body as xml ) , @msgBody = cast ( message_body as xml )
             @msgType = message_type_name , @msgType = message_type_name
           AsyncLogQueue from AsyncLogQueue
         timeout 500 ; ), timeout 500 ;
 
         ( @@rowcount = 0 ) if ( @@ rowcount = 0 )
         begin
             '@@rowcount = 0, нет данных в очереди, выходим' print '@@ rowcount = 0, no data in the queue, exit'           
             ; break ;
         end
         ...
             log . exec log .  XmlEventMessageProcessing @msgBody
 
     end
 end
 ------------ --- ------------
 procedure log . create procedure log .  with execute as owner XmlEventMessageProcessing @message xml with execute as owner
 as
 begin
     ....
     into log . insert into log .  Applog
     ....
 end


I suspect my problems may be caused by the fact that I used with as owner
Re: Service broker for logging, glitches [new]
Kifirchik,

and what error did you struggle with by including trustworthy. it can affect the service broker only if you have an interbase queue dialogue.
Re: Service broker for logging, glitches [new]
felix_ff
Kifirchik,
and what error did you struggle with by including trustworthy. it can affect the service broker only if you have an interbase queue dialogue.


Now turned off on both bases, and without it everything works ((
Re: Service broker for logging, glitches [new]
Below is a piece of code as I see it would be "right to do"
the owner of all the databases of an excellent user, under which clients do not work and which cannot be TRACE EVENT
in the databases - two user_task users, for the work of clients, and user_log, which will have rights to TRACE EVENT

user_task calls CP wrappers over TRACE EVENT which run as user_log

but does not work - "No permission to launch" SP_TRACE_GENERATEEVENT "

What is missing in the code?

+
 master go if exists ( select 1 from sys . databases where name = 'db_log' ) drop database db_log if exists ( select 1 from sys . databases where name = 'db_task' ) drop database db_task go if exists ( select 1 from master . sys . server_principals where name = 'login_db_owner' ) drop login login_db_owner go if exists ( select * from master . sys . server_principals where name = 'login_task' ) drop login login_task go if exists ( select * from master . sys . server_principals where name = 'login_log' ) drop login login_log go --- ---------------------------------------------------------------- create database db_log go create database db_task go create login login_db_owner with password = 'login_db_owner' go create login login_task with password = 'login_task' go create login login_log with password = 'login_log' go -- собственник баз - login_db_owner alter authorization on database ::db_log to login_db_owner go alter authorization on database ::db_task to login_db_own db_log if there is ( select 1 from sys . databases where name = 'db_task' ) drop master dh_log exists ( select 1 from sys . databases where name = 'db_log' ) drop database db_task go if exists ( select 1 from master . sys . server_principals where name = 'login_db_owner' ) drop login login_db_owner go if exists ( select * from master . sys . server_principals where name = 'login_task' ) drop login login_task go if exists ( select * from master . sys . server_principals where name name = 'login_log' ) drop login login_log go --- --------------------------------------- ------------------------- create database db_log go create database db_task go create login login_db_owner with password = 'login_db_owner' go create login login_task with password = ' login_task ' go create login login_log with password = ' login_log ' go - database owner - login_db_owner alter authorization for database :: db_log to login_db_owner go alter authorization for database :: db_task to login_db_own  use master go grant alter trace to login_log go --- -------------- -- пользователи в базах use db_task go create user user_task for login login_task create user user_log for login login_log go alter role db_datareader add member user_task alter role db_datawriter add member user_task go grant exec to user_task grant exec to user_log go --- -------------- use db_log go create user user_task for login login_task create user user_log for login login_log go alter role db_datareader add member user_log go alter role db_datawriter add member user_log go grant exec to user_log go --- ----------------------- -- тестовая ХП бизнес логики и обертка над sp_trace_generateevent use db_task go create schema log go go -- вроде как логину login_log (c которым связан пользователь базы user_log) -- даны права на er go - login_log login give the opportunity to call EVENTs use master go grant grant go to login_log go --- ------------------ - users in databases use db_task go create user user_task for login login_task create user_name login_name login_log go alter role db_datareader add member user_task alter role db_datawriter add member user_task go go user_log go --- -------------- db_log go create user user_task for user login login_task create user user_log for user login login_log go alter role db_datareader add member user_log for user go log alter role db_datawriter add member user_log --------- - test HP business logic and a wrapper over sp_trace_generateevent use db_task go create schema log go go - sort of like login_log login (with which user_log user is associated) - is given the rights to  -- и от его имени запустим ХП create procedure log . sp_trace_generateevent - and run the create procedure log CP on its behalf .  ( 255 ), @logData nvarchar ( 2048 ) = null with execute as 'user_log' as begin declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ), elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 )); spLogSimple @logMsg nvarchar ( 255 ), @logData nvarchar ( 2048 ) = null with execute as 'user_log' as declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ), elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 ));  sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;  go create procedure SomeTaskProc as begin exec log . end go create procedure SomeTaskProc begin begin exec log .  , 'args1' waitfor delay '00:00:01.000' exec log . spLogSimple 'SomeTaskProc start' , 'args1' waitfor delay '00: 00: 01.000 ' exec log .  , 'args2' end go --- ----------- -- база с логами use db_log go create schema log go create table log . spLogSimple 'SomeTaskProc finish' , 'args2' end go --- ----------- - base with logs use db_log go create schema log go create table log .  ( LogDateTime datetime , LogMsg nvarchar ( 128 ), LogData nvarchar ( 2000 ), Stamp timestamp ) go go create procedure log . AppLog ( LogDateTime datetime , LogMsg nvarchar ( 128 ), LogData nvarchar ( 2000 ), Stamp timestamp ) go go create procedure log .  as begin declare @postTime datetime = @message . XmlMsgProcessing @message xml as begin declare @postTime datetime = @message .  ( '(EVENT_INSTANCE/PostTime)[1]' , 'datetime' ) declare @textData nvarchar ( 128 ) = @message . value ( '(EVENT_INSTANCE / PostTime) [1]' , 'datetime' ) declare @textData nvarchar ( 128 ) = @message .  ( '(EVENT_INSTANCE/TextData)[1]' , 'sysname' ) declare @binaryData varbinary ( 8000 ) = @message . value ( '(EVENT_INSTANCE / TextData) [1]' , 'sysname' ) declare @binaryData varbinary ( 8000 ) = @message .  ( '(EVENT_INSTANCE/BinaryData)[1]' , 'varbinary(8000)' ) declare @messageXml xml = cast ( cast ( @binaryData as nvarchar ( 4000 )) as xml ) declare @logMsg nvarchar ( 128 ) = @messageXml . value ( '(EVENT_INSTANCE / BinaryData) [1]' , 'varbinary (8000)' ) declare @messageXml xml = cast ( cast ( @binaryData as nvarchar ( 4000 )) as xml ) declare @logMsg nvarchar ( 128 ) = @messageXml .  ( '(AsyncLogMessageSimple/LogMsg)[1]' , 'nvarchar(128)' ) declare @logData nvarchar ( 128 ) = @messageXml . value ( '(AsyncLogMessageSimple / LogMsg) [1]' , 'nvarchar (128)' ) declare @logData nvarchar ( 128 ) = @messageXml .  ( '(AsyncLogMessageSimple/LogData)[1]' , 'nvarchar(2000)' ) insert into log . value ( '(AsyncLogMessageSimple / LogData) [1]' , 'nvarchar (2000)' ) insert into log .  ( LogDateTime , LogMsg , LogData ) select @postTime , @logMsg , @logData end go create procedure log . AppLog ( LogDateTime , LogMsg , LogData ) select @postTime , @logMsg , @logData end go create procedure log .  execute as 'user_log' as begin set nocount on ; AsyncLogQueueProcessing with execute as 'user_log' as begin set nocount on ;  arithabort off ; set arithabort off ;  query_governor_cost_limit 0 ; set query_governor_cost_limit 0 ;  @h uniqueidentifier , @h2 uniqueidentifier , @m xml , @msgType sysname while 1=1 begin waitfor ( receive top ( 1 ) @h = conversation_handle , @m = message_body from log . AsyncLogQueue ), timeout 500 if @@rowcount = 0 begin if @h is not null end conversation @h ; declare @h uniqueidentifier , @h2 uniqueidentifier , @m xml , @msgType sysname while 1 = 1 begin waitfor ( receive top ( 1 ) @h = conversation_handle , @m = message_body from log . AsyncLogQueue ), timeout 500 if @@ rowcount = 0 begin if @h is not null end conversation @h ;  ; break ;  --логика обработки сообщения if @msgType = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' begin exec log . end - message processing logic if @msgType = N 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' begin exec log .  conversation @h with cleanup ; XmlMsgProcessing @m end conversation @h with cleanup ;  else if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' end conversation @h with cleanup ; end else if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' end conversation @h with cleanup ;  if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' end conversation @h with cleanup ; else if @msgType = N 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' end conversation @h with cleanup ;  @h2 <> @h and @h2 is not null end conversation @h2 ; if @ h2 <> @h and @ h2 is not null end conversation @ h2 ;  @h2 = @h ; set @ h2 = @h ;  -- declare @handle uniqueidentifier; end - declare @handle uniqueidentifier;  - declare @msgBody xml;  - declare @msgBodyStr nvarchar (max);  - declare @msgType sysname;  -- begin -- waitfor( -- receive top (1) -- @handle = conversation_handle -- , @msgBody = cast( message_body as xml) -- , @msgType = message_type_name -- from AsyncLogQueue -- ), timeout 500; - while (1 = 1) - begin - waitfor ( - receive top (1) - @handle = conversation_handle -, @msgBody = cast (message_body as xml) -, @msgType = message_type_name - from AsyncLogQueue -), timeout 500;  -- if (@@rowcount = 0) -- begin -- print '@@rowcount = 0, нет данных в очереди, выходим' -- break; - - if (@@ rowcount = 0) - begin - print '@@ rowcount = 0, there is no data in the queue, exit' - break;  -- -- if @msgType = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' -- begin -- exec log.XmlEventMessageProcessing @msgBody -- end conversation @handle with cleanup; - end - - if @msgType = N'http: //schemas.microsoft.com/SQL/Notifications/EventNotification ' - begin - exec log.XmlEventMessageProcessing @msgBody - end conversation @handle with cleanup;  -- else if @msgType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' -- end conversation @handle with cleanup; - end - else if @msgType = N'http: //schemas.microsoft.com/SQL/ServiceBroker/EndDialog ' - end conversation @handle with cleanup;  -- end conversation @handle with cleanup; - else if @msgType = N'http: //schemas.microsoft.com/SQL/ServiceBroker/Error ' - end conversation @handle with cleanup;  -- end end go create queue log . - - end end go create queue log .  status = on , retention = off , activation ( status = on , procedure_name = log . AsyncLogQueueProcessing , max_queue_readers = 5 , execute as 'user_log' ), poison_message_handling ( status = on ); AsyncLogQueue with status = on , retention = off , activation ( status = on , procedure_name = log . AsyncLogQueueProcessing , max_queue_readers = 5 , execute as 'user_log' ), poison_message_handling ( status = on );  service AsyncLogService authorization user_log on queue log . go go create service AsyncLogService authorization user_log on queue log .  ([ http: // schemas . microsoft . com / SQL / Notifications / PostEventNotification ] ); AsyncLogQueue ([ http: // schemas . Microsoft . Com / SQL / Notifications / PostEventNotification ] );  event notification AsyncLogEventNotify on server go create event notification AsyncLogEventNotify on server for USERCONFIGURABLE_0 to service 'AsyncLogService' , 'current database' ; asyncLogEventNotify on event go to event notify event asyncLogEventNotify on service for asyncLogService, asyncLogEventNotify ;  ------------------------------------------------------ --- ------------------------------------------------------ use db_task go -- эмитируем вызов хранимки бизнес логики от пользователя user_task execute as user = 'user_task' begin try exec SomeTaskProc end try begin catch print 'Error in SomeTaskProc' print '@error_procedure = ' + isnull ( error_procedure () , 'null' ) print '@error_line = ' + isnull ( convert ( varchar , error_line ()), 'null' ) print '@error_number = ' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message = ' + isnull ( error_message () , 'null' ) print '@error_severity = ' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state = ' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert --- ------------------------------------------------------ --- ------------------------------------------------------ use db_log go -- пробуем вста  go --- ---------------------------------------------- -------- --- --------------------------------------- --------------- use db_task go - issue a call to the business logic user from user_task execute as user = 'user_task' begin try exec SomeTaskProc end try catch catch print 'Error in SomeTaskProc' print '@error_procedure =' + isnull ( error_procedure () , 'null' ) print '@error_line =' + isnull ( convert ( varchar , error_line ()), 'null' ) print '@error_number =' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message =' + isnull ( error_message () , 'null' ) print '@error_severity =' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state =' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert --- ----------------------- ------------------------------- --- ---------------- -------------------------------------- use db_log go - try to get up  --execute as user = 'login_log' execute as user = 'user_log' begin try -- exec db_task.log.spLogSimple 'Test message','some data' declare @m xml = ( select 'Test message2' as LogMsg , 'some data2' as LogData for xml raw ( 'AsyncLogMessageSimple' ), elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 )); Post it under user_log and read it --execute as user = 'login_log' execute as user = 'user_log' begin try - exec db_task.log.spLogSimple 'Test message', 'some data' declare @m xml = (select ' Test message2 ' as LogMsg , ' some data2 ' as LogData for xml raw ( ' AsyncLogMessageSimple ' ), elements ) declare @a nvarchar ( 128 ) = ' AsyncLogMessageSimple ' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 ));  sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;  log . exec log .  top 20 * from log . AsyncLogQueueProcessing select top 20 * from log .  by Stamp desc end try begin catch print 'Error in db_log' print '@error_procedure = ' + isnull ( error_procedure () , 'null' ) print '@error_line = ' + isnull ( convert ( varchar , error_line ()), 'null' ) print '@error_number = ' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message = ' + isnull ( error_message () , 'null' ) print '@error_severity = ' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state = ' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert AppLog order by Stamp desc end try, try catch, catch 'Error in db_log' print '@error_procedure =' + isnull ( error_procedure () , 'null' ) print '@error_line =' + isnull ( convert ( varchar , error_line ()), 'null' ) print '@error_number =' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message =' + isnull ( error_message () , 'null' ) print '@error_severity =' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state =' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert 
Re: Service broker for logging, glitches [new]
Kifirchik,

you confuse the switching context; in your case, you must use execute as login instead of execute as user

make
 db_log use db_log
 go

 as user = 'user_log' ; execute as user = 'user_log' ;
 go
 master use master
 * from sys . select * from sys .  user_token
 * from sys . select * from sys .  login_token ;
 go

 db_log use db_log
 go
 revert ;
 go

 as login = 'login_log' ; execute as login = 'login_log' ;
 go
 master use master
 go
 * from sys . select * from sys .  user_token
 * from sys . select * from sys .  login_token ;
 go

 db_log use db_log
 go
 revert ;
 go
see the difference
Re: Service broker for logging, glitches [new]
About carefully looked, there you have mixed.

easier to do so:

 master go create certificate [ trace_cert ] ENCRYPTION BY PASSWORD = 'some_strong_password' WITH SUBJECT = 'certificate_for_trace_events' ; use master go create certificate [ trace_cert ] ENCRYPTION BY PASSWORD = 'some_strong_password' WITH SUBJECT = 'certificate_for_trace_events' ;  login [ trace_login ] from certificate [ trace_cert ]; create login [ trace_login ] from certificate [ trace_cert ];  CERTIFICATE [ trace_cert ] TO FILE = 'C:\temp\trace_cert.cer' WITH PRIVATE KEY ( FILE = 'C:\temp\trace_cert.pvk' , ENCRYPTION BY PASSWORD = 'storng_password' , DECRYPTION BY PASSWORD = 'storng_password' ); BACKUP CERTIFICATE [ trace_cert ] TO FILE = 'C: \ temp \ trace_cert.cer' WITH PRIVATE KEY ( FILE = 'C: \ temp \ trace_cert.pvk' , ENCRYPTION BY PASSWORD = 'storng_password' , DECRYPTION BY PASSWORD = 'storng_password' );  alter trace to [ trace_login ]; grant alter trace to [ trace_login ];  db_task go create certificate [ trace_cert ] from file = 'C:\temp\trace_cert.cer' with private key ( FILE = 'C:\temp\trace_cert.pvk' , ENCRYPTION BY PASSWORD = 'storng_password' , DECRYPTION BY PASSWORD = 'storng_password' ); go use db_task go create certificate [ trace_cert ] from file = 'C: \ temp \ trace_cert.cer' with private key ( FILE = 'C: \ temp \ trace_cert.pvk' , ENCRYPTION BY PASSWORD = 'storng_password' , DECRYPTION BY PASSWORD = 'storng_password' );  or alter procedure log . go create or alter procedure log .  ( 255 ), @logData nvarchar ( 2048 ) = null --with execute as 'user_log' as begin declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ), elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 )); spLogSimple @logMsg nvarchar ( 255 ), @logData nvarchar ( 2048 ) = null --with execute as 'user_log' as begin declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ) , elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 ));  sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;  go add signature to [ spLogSimple ] by certificate [ trace_cert ] with password = 'strong_password' ; end go add signature to [ spLogSimple ] by certificate [ trace_cert ] with password = 'strong_password' ;  go 


then you don't need to be steamed with context switching and their rights.
you have rights granted to the trace_login login will be applied to the call of the module which is signed by the corresponding certificate.

I wrote from memory can somewhere with the syntax nakosyachil.
Re: Service broker for logging, glitches [new]
Felix_ff, worked)
The memory turned out very accurately)))

Understandably, that is, you cannot just say “execute from this login”, it should be like “login”, and it is possible automatically if you create a login with a certificate.
+
 master go if exists ( select 1 from sys . databases where name = 'db_task' ) drop database db_task go if exists ( select 1 from master . sys . server_principals where name = 'login_db_owner' ) drop login login_db_owner go if exists ( select * from master . sys . server_principals where name = 'login_task' ) drop login login_task go if exists ( select * from master . sys . server_principals where name = 'login_trace' ) drop login login_trace go use master go --- ---------------------------------------------------------------- create database db_task go create login login_db_owner with password = 'login_db_owner' go create login login_task with password = 'login_task' go --- ------------------------------------------------------------ -- СЕРТИФИКАТ -- drop certificate [trace_cert] -- create certificate [trace_cert] encryption by password = 'some_strong_password' with subject = 'certificate_for_trace_events'; use master go if exists ( select 1 from sys . databases where name = 'db_task' ) drop database db_task go if exists ( select 1 from master . sys . server_principals where name = 'login_db_owner' ) drop login login_db_owner go if exists ( select * from master . sys . server_principals where name = 'login_task' ) drop login login_task go if exists ( select * from master . sys . server_principals where name = 'login_trace' ) drop login login_trace go use master go --- ----- -------------------------------------------------- --------- create database db_task go create login login_db_owner with password = 'login_db_owner' go create login login_task with password = 'login_task' go --- ------------------ ---------------------------------------------- - CERTIFICATE - - drop certificate [trace_cert] - create certificate [trace_cert] encryption by password = 'some_strong_password' with subject = 'certificate_for_trace_events';  -- ( file = 'c:\svn\trace_cert.pvk',encryption by password = 'some_strong_password', decryption by password = 'some_strong_password' ); - backup certificate [trace_cert] to file = 'c: \ svn \ trace_cert.cer' with private key - (file = 'c: \ svn \ trace_cert.pvk', encryption by password = 'some_strong_password', decryption by password = 'some_strong_password');  login login_trace from certificate [ trace_cert ]; create login login_trace from certificate [ trace_cert ];  master go grant alter trace to login_trace ; go use master go grant alter trace to login_trace ;  ------------------------------------------------------------ -- собственник баз - login_db_owner alter authorization on database ::db_task to login_db_owner go --- -------------- -- пользователи в базах use db_task go create user user_task for login login_task go alter role db_datareader add member user_task alter role db_datawriter add member user_task go grant exec to user_task go --- ----------------------- -- тестовая ХП бизнес логики и обертка над sp_trace_generateevent use db_task go create schema log go create certificate [ trace_cert ] from file = 'c:\svn\trace_cert.cer' with private key ( file = 'c:\svn\trace_cert.pvk' , encryption by password = 'some_strong_password' , decryption by password = 'some_strong_password' ); go --- ---------------------------------------------- -------------- - database owner - login_db_owner alter authorization on database :: go to db_task to login_db_owner go --- --------------------- users in databases, use db_task go create user user_task for login login_task go alter role db_datareader add member user_task alter role db_datawriter add member user_task go go --- --------------------- ------ - test business logic CP and wrapper over sp_trace_generateevent use db_task go create schema log go create certificate [ trace_cert ] from file = 'c: \ svn \ trace_cert.cer' with private key ( file = 'c: \ svn \ trace_cert.pvk ' , encryption by password = ' some_strong_password ' , decryption by password = ' some_strong_password ' );  procedure log . go create procedure log .  ( 255 ), @logData nvarchar ( 2048 ) = null -- with execute as 'user_log' as begin declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ), elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 )); spLogSimple @logMsg nvarchar ( 255 ), @logData nvarchar ( 2048 ) = null - with execute as 'user_log' as begin declare @m xml = ( select @logMsg as LogMsg , @logData as LogData for xml raw ( 'AsyncLogMessageSimple' ) , elements ) declare @a nvarchar ( 128 ) = 'AsyncLogMessageSimple' declare @b varbinary ( 8000 ) = cast ( @m as varbinary ( 8000 ));  sp_trace_generateevent 82 , @a , @b ; exec sp_trace_generateevent 82 , @a , @b ;  go add signature to log . end go add signature to log .  certificate [ trace_cert ] with password = 'some_strong_password' ; spLogSimple by certificate [ trace_cert ] with password = 'some_strong_password' ;  procedure SomeTaskProc as begin exec log . go go create procedure SomeTaskProc begin begin exec log .  , 'args1' waitfor delay '00:00:01.000' exec log . spLogSimple 'SomeTaskProc start' , 'args1' waitfor delay '00: 00: 01.000 ' exec log .  , 'args2' end go --- ------------------------------------------------------ use db_task go -- эмитируем вызов хранимки бизнес логики от логина login_task execute as login = 'login_task' begin try print 'Current login/user: [' + isnull ( SUSER_NAME (), 'null' )+ ']/[' + isnull ( current_user , 'null' )+ '], call SomeTaskProc...' exec SomeTaskProc print 'Ok' end try begin catch print 'Error in SomeTaskProc' print '@error_procedure = ' + isnull ( error_procedure () , 'null' ) print '@error_line = ' + isnull ( convert ( varchar , error_line ()), 'null' ) print '@error_number = ' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message = ' + isnull ( error_message () , 'null' ) print '@error_severity = ' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state = ' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert spLogSimple 'SomeTaskProc finish' , 'args2' end go --- ------------------------------------ ------------------ use db_task go - issue a call to the business logic stored in login_task login execute as login = 'login_task' begin try print 'Current login / user: [' + isnull ( SUSER_NAME (), 'null' ) + '] / [' + isnull ( current_user , 'null' ) + '], call SomeTaskProc ...' exec SomeTaskProc print 'Ok' end try catch catch Print 'Error in SomeTaskProc ' print ' @error_procedure = ' + isnull ( error_procedure () , ' null ' ) print ' @error_line = ' + isnull ( convert ( varchar , error_line ()), ' null ' ) print ' @error_number = ' + isnull ( convert ( varchar , error_number ()), 'null' ) print '@error_message =' + isnull ( error_message () , 'null' ) print '@error_severity =' + isnull ( convert ( varchar , error_severity ()), 'null' ) print '@error_state =' + isnull ( convert ( varchar , error_state ()), 'null' ) end catch revert 

It remains to grow the queues, to understand what is there with the dialogues exactly going on and pull their more complex handlers.

The only thing, BUT, the solution in the end turns out to be not so trivial, if after a year less experienced colleagues take it to the base, something breaks, it may turn out that it will be easier for them than to repair their bike.

And there are questions about how it will behave with backups and database transfer.
The certificate as I understand it lives in the server, if I transfer the database to another server - then I need to load the certificate and update the add signature to log.spLogSimple? or make a new certificate and re-sign log.spLogSimple?
Re: Service broker for logging, glitches [new]
Kifirchik
Understandably, that is, you cannot just say “execute from this login”, it should be like “login”, and it is possible automatically if you create a login with a certificate.
https://docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2008-r2/ms188304(v=sql.105)
Kifirchik
Certificate as I understand it lives in the server
The certificate lives in the database where it was created.
Re: Service broker for logging, glitches [new]
Another pathology.
The column on the left is the time of the call eventa, taken before the call itself, that is, the real time of the call in CP.
The column on the right is the timestamp of the entry in the table with logs, that is, the message processing order (desc).

  Logdatetime stamp 
2018-12-18 16: 48: 19.507 0x00000000000B8C0F
2018-12-18 16: 48: 04.230 0x00000000000B8C0E
2018-12-18 16: 48: 35.720 0x00000000000B8C0D
2018-12-18 16: 48: 19.563 0x00000000000B8C0C
2018-12-18 16: 48: 35.700 0x00000000000B8C0B
2018-12-18 16: 48: 35.687 0x00000000000B8C08
2018-12-18 16: 48: 35.683 0x00000000000B8C07
2018-12-18 16: 48: 35.493 0x00000000000B8C06
2018-12-18 16: 48: 35.490 0x00000000000B8C05
2018-12-18 16: 48: 35.490 0x00000000000B8C04
2018-12-18 16: 48: 19.357 0x00000000000B8C03
2018-12-18 16: 48: 11.397 0x00000000000B8C02

It was assumed that Stamp will be able to determine the correct order of the Event calls, but the data in the LogDateTime column is mixed.
The RECEIVE manual says - "The result set returned by the RECEIVE instruction is implicitly ordered."
That is, the queue as received, and issues (apparently in reverse order, the order by to the queue is not applicable).

Event turns out turn out to queue not always as their call?
Re: Service broker for logging, glitches [new]
Kifirchik,

that is, the message queue is guaranteed only within a single conversation, and not heaps of dialogs.
Re: Service broker for logging, glitches [new]
Hmm ...
two more questions:
1) conversation, dialogue, regarding the Notify event, where is it? challenge one hp? or package?
2) is it possible to make so that the notify event themselves and messages like "The activated proc '[log]. [AsyncLogQueueProcessing] ? is it somehow customizable?
Re: Service broker for logging, glitches [new]
Kifirchik
Hmm ...
two more questions:
1) conversation, dialogue, regarding the Notify event, where is it? challenge one hp? or package?
2) is it possible to make so that the notify event themselves and messages like "The activated proc '[log]. [AsyncLogQueueProcessing] ? is it somehow customizable?


1) the question is not quite clear. do you mean to what point the messages will fall into the same dialogue?
If the question is in this, I definitely will not tell you, you need to experiment. In general, within one notification there should be one dialogue, but I have encountered situations when this is not the case. so that the messages are not accumulated, we wrote a task that once a week recreated the notification.

2) your messages to the log can be incurred if your event notification fails to deliver. What is detailed in the log message written?
Re: Service broker for logging, glitches [new]
Kifirchik
conversation, regarding the Notify event, where is it? challenge one hp? or package?
You should not care at all. The time stamp should come along with the logged message. You should not be attached to the order of receipt of messages and, especially, to the order of rows in the log table.
No need to complete the dialogue for every sneeze. It’s not you who started this dialogue - it’s not for you to complete.
It is necessary to complete only in response to EndDialog and Error and without the option with cleanup (my joint in the example). This option is used to abort.
And check what is happening in sys.conversation_endpoints in msdb and VTSDB2_LOG.
Kifirchik
Is it possible to do so so that notify event themselves and messages like "The activated proc '[log]. [AsyncLogQueueProcessing] is it somehow customizable?
Remove all diagnostic output from the log.AsyncLogQueue procedure.
Re: Service broker for logging, glitches [new]
Damn, exactly
"The activated proc '[log]. [AsyncLogQueueProcessing]' running on queue 'VTSDB2_LOG.log.AsyncLogQueue' output the following: 'exec spLogSimpleDo1'
this is a PRINT 'exec spLogSimpleDo1' inside AsyncLogQueueProcessing

invm
You should not care at all. The time stamp should come along with the logged message. You should not be attached to the order of receipt of messages and, especially, to the order of rows in the log table.

Unfortunately, time stamps are not unique, two (and even 5) identical ones may come, and then it is problematic to understand the sequence in which they appeared. If the dialogue in the queue has borders, for example, within one @spid, or within one CP, then this would accurately preserve the order of calls, based on the order in which the messages appear in the queue.
Otherwise, you need to create a Stamp before sending a logged message. Or sequence long using.

It is necessary and true to do the experiment, saving the handle of the dialogue in the queue handler. Who knows, I would simplify it a lot)

sys.conversation_endpoints
LOG + LOG
  conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system priority 
B7B2F36D-72FF-E811-8127-0CC47A70A1C0 A08BFD0B-64C5-4B63-A087-B8E7B9A8F976 0 2 00000000-0000-0000-0000-000000000000 65561 2087-01-01 10: 44: 50.107 CL CLOSED http://schemas.microsoft 65561 2087-01-01 com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 11 0 0 -1 0 0 0 5
DEA109ED-95FF-E811-8127-0CC47A70A1C0 172476B0-6464-4DB9-A6CA-434B14087F3F 2 00000000-0000-0000-0000-000000000000 65561 2087-01-01 10: 44: 50.107 CD CLOSED http://schemas.microsoft. com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 8 0 0 -1 0 0 0 5
D64E0EF4-A8FF-E811-8127-0CC47A70A1C0 A3A3467B-5E23-4942-A844-827CA59B7B93 0 00000000-0000-0000-0000-000000000000 65561 2087-01-01 10: 44: 50.107 CD CLOSED http://schemas.microsoft. com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 4 0 0 -1 0 0 0 5
8C5D3F74-B0FF-E811-8127-0CC47A70A1C0 59930D03-6E20-4EB1-A76E-4C4C554A11D7 0 2 8B5D3F74-B0FF-E811-8127-0CC47A70A1C0 65562 2087-01 18, how to do it, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example, for example com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 270110 0 0 -1 0 0 0 5
65D5176F-4500-E911-8127-0CC47A70A1C0 337D1522-475E-4F85-B91D-CF9AA493DF0E 0 64D5176F-4500-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10: 44.440.440 CO CONSVERSON (S) CONSETRESHIELEASASASIADAE6C0 65562 2087-01-01 18: 10: 44.440.440 CO COVERSHeanSaverTextraE, de sére o ce CO deVéralo de la o carte de véraVesTera de Sénéra de Célés de Célés de Sénéric de Célo de sére FRESTERAlVAE com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 35229 0 0 -1 0 0 0 5
D686F8A0-FD00-E911-8127-0CC47A70A1C0 ACF3043A-465D-44BE-81BB-AEF2F3A098AA 0 2 D586F8A0-FD00-E911-8127-0CC47A70A1C0 65562 2087-01 18, to be as a factor in the subtraction of a number of reasons for the number of reasons for the number of people who are com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 49503 0 0 -1 0 0 0 5
548A006E-1701-E911-8127-0CC47A70A1C0 1E5D472B-A2AB-46BF-9B1E-D2809A0B6054 0 2 538A006E-1701-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10.4444343 COURSE CURAAAAC062621 086620087A0B6052 com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 56359 0 0 -1 0 0 0 5
53C289A0-2C01-E911-8127-0CC47A70A1C0 F7773E74-D6F1-44F1-9DC6-8CC624698FA7 0 2 52C289A0-2C01-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10: 44.440 CO CONVERSERSecting com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 51491 0 0 -1 0 0 0 5
D8AC24B0-3001-E911-8127-0CC47A70A1C0 F548C0BF-B19B-490F-A136-E147B2C018F6 0 2 D7AC24B0-3001-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10.4.4.44040 CO The wave pattern in the world, you will not be in the way the saE with the real estate in the world, it should be a challenge to the challenge to see the saE a com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 76844 0 0 -1 0 0 0 5
4BB85015-3801-E911-8127-0CC47A70A1C0 B23C5BD4-2E68-49D8-8CDB-BD8DC2C0DD05 0 4AB85015-3801-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10: 44.44040 CON CONVECTRON CONFORMS (CONSECTRES ) com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 59348 0 0 -1 0 0 0 5
7A4EEBD8-4801-E911-8127-0CC47A70A1C0 CF68993D-DEEB-4C19-8A99-1343EEF5661B 0 2 794EEBD8-4801-E911-8127-0CC47A70A1C0 65562 2087-01-01 18: 10: 44.440 CO CONVERSINGecting com / SQL / Notifications / EventNotificationService E6F81215-B045-463F-BF31-96D7359B35B4 1 1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01- 01 00: 00: 00.000 0 0x000000000000 -1 47554 0 0 -1 0 0 0 5

msdb + msdb
  conversation_handle conversation_id is_initiator service_contract_id conversation_group_id service_id lifetime state state_desc far_service far_broker_instance principal_id far_principal_id outbound_session_key_identifier inbound_session_key_identifier security_timestamp dialog_timer send_sequence last_send_tran_id end_dialog_sequence receive_sequence receive_sequence_frag system_sequence first_out_of_order_sequence last_out_of_order_sequence last_out_of_order_frag is_system priority 
9DC46B29-72FF-E811-8127-0CC47A70A1C0 FFFB9CA6-2C5D-4CA1-BFCE-2BEC0ACE3B98 1 2 00000000-0000-0000-0000-000000000000 2 2087-01-01 10: 44 5077 DO DISCONNECTED_OUTBOUND Asyn -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 4603 0x625CED000000 4603 0 0 0 - 1 0 0 1 5
745D3F74-B0FF-E811-8127-0CC47A70A1C0 F548C0BF-B19B-490F-A136-E147B2C018F6 1 2 755D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10; -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 76844 0x2A20EF000000 -1 0 0 0 -1 0 0 1 5
775D3F74-B0FF-E811-8127-0CC47A70A1C0 ACF3043A-465D-44BE-81BB-AEF2F3A098AA 1 2 785D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18.4.10.4.4.44.440.44.440D-44840B-AEF2F3A098AA 1 2 787D3F74-B0FF-E811-8127-0CC47A70A1C0 2 -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 49503 0xD81EEF000000 -1 0 0 0 -1 0 0 1 5
7A5D3F74-B0FF-E811-8127-0CC47A70A1C0 B23C5BD4-2E68-49D8-8CD-BD8DC2C0DD05 1 2 7B5D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10 10 tel. -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 59348 0x5A21EF000000 -1 0 0 0 -1 0 0 1 5
7D5D3F74-B0FF-E811-8127-0CC47A70A1C0 F7773E74-D6F1-44F1-9DC6-8CC624698FA7 1 2 7E5D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10: how to make a real-time-all-off-screen-saver; -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 51491 0x5D1FEF000000 -1 0 0 0 -1 0 0 1 5
805D3F74-B0FF-E811-8127-0CC47A70A1C0 337D1522-475E-4F85-B91D-CF9AA493DF0E 1 2 815D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10; -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 35229 0x821FEF000000 -1 0 0 0 -1 0 0 1 5
835D3F74-B0FF-E811-8127-0CC47A70A1C0 CF68993D-DEEB-4C19-8A99-1343EEF5661B 1 845D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10: 44.440 CONV. -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 47554 0x1EF7EE000000 -1 0 0 0 -1 0 0 1 5
865D3F74-B0FF-E811-8127-0CC47A70A1C0 59930D03-6E20-4EB1-A76E-4C4C554A11D7 February 1 875D3F74-B0FF-E811-8127-0CC47A70A1C0 2 01.01.2087 18: 10: 44.443 CO CONVERSING AsyncLogService D775EE0B-292D-4712-AECB -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 270110 0xA627EF000000 -1 0 0 0 -1 0 0 1 5
895D3F74-B0FF-E811-8127-0CC47A70A1C0 1E5D472B-A2AB-46BF-9B1E-D2809A0B6054 1 2 8A5D3F74-B0FF-E811-8127-0CC47A70A1C0 2 2087-01-01 18: 10: 40.46.44.44.44.44-B8FF-E811-812B-9B1E-D2809AFB-5B4FB-A2AB-46BF-9B1E-D2809A0B6054 1 2 -89C4559A6ED8 4 -1 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 1900-01-01 00: 00: 00.000 1900-01-01 00: 00: 00.000 56359 0x9E27EF000000 -1 0 0 0 -1 0 0 1 5

Is everything very bad in the tables? must be empty?
Re: Service broker for logging, glitches [new]
Kifirchik
Unfortunately, time stamps are not unique, two (and even 5) identical ones may come, and then it is problematic to understand the sequence in which they appeared. If the dialogue in the queue has borders, for example, within one @spid, or within one CP, then this would accurately preserve the order of calls, based on the order in which messages appear in the queue.
Otherwise, you need to create a Stamp before sending a logged message. Or sequence long using.
Enter in the VTSDB2_LOG sequence, pull values ​​from it into other databases and transmit the received in the message.
Kifirchik
Is everything very bad in the tables? must be empty?
No, more or less.
From the one in the LOG, you need to nail the lines with state_desc = 'CLOSED' by calling end conversation ... with clean up



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ