E
E
Egor Nikitin2019-01-29 21:14:16
Transact SQL
Egor Nikitin, 2019-01-29 21:14:16

[RowLog Contents 0] - How to get the values ​​of this field (t-sql)?

Hello.
The essence in general is simple:
There is a 1s server base. I am writing a simple query in MS SQL management studio to see how the next document was written to the document table. Simply put, I want to see in the transaction log what data was transferred to the table to write the document.
Tried it this way:

SELECT  CONVERT(varchar(MAX), [RowLog Contents 0]), 
  CONVERT(varchar(MAX), [RowLog Contents 1]), 
  CONVERT(varchar(MAX), [RowLog Contents 2]), 
  CONVERT(varchar(MAX), [RowLog Contents 3]), 
  CONVERT(varchar(MAX), [RowLog Contents 4]),
  CONVERT(varchar(MAX), [RowLog Contents 5])
  FROM sys.fn_dblog
    (NULL, NULL)
WHERE operation IN
   ('LOP_INSERT_ROWS') and [Transaction ID] = '0000:001496ee' ;

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-01-29
@Habr_NED

SELECT  [Transaction ID],
  CONVERT(varchar, [RowLog Contents 0]), 
  CONVERT(varchar, [RowLog Contents 1]), 
  CONVERT(varchar, [RowLog Contents 2]), 
  CONVERT(varchar, [RowLog Contents 3]), 
  CONVERT(varchar, [RowLog Contents 4]),
  CONVERT(varchar, [RowLog Contents 5])
FROM sys.fn_dblog (NULL, NULL)
WHERE operation = 'LOP_INSERT_ROWS'
Works. And you?
Or are you not satisfied with the resulting CONVERT(varchar, [RowLog Contents 0])? There is an answer on the web:
Different columns are used for each type of transaction, in order to get the information you need, you must know exactly which columns are used for which transactions, and this is not easy, as there is no official documentation with a description.
Inserted and deleted rows are stored in hexadecimal values. In order to pull data from these values, you must know the storage format, understand the status bits, know the total number of columns, and so on.

data in hex value is not clear to me
There are descriptions of [RowLog Contents 0] structures on the net, for example: Forensics or Decoding a Simple Update Statement Within the Tran... etc.
If you need only bit numbers without a prefix, use CONVERT(VARCHAR,[RowLog Contents 0],2).

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question