Answer the question
In order to leave comments, you need to log in
[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
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? 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 meThere are descriptions of [RowLog Contents 0] structures on the net, for example: Forensics or Decoding a Simple Update Statement Within the Tran... etc.
CONVERT(VARCHAR,[RowLog Contents 0],2)
.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question