Msg 537, level 16, state 3, procedure recover_truncated_data_proc, line 113 invalid length parameter passed to the LEFT or SUBSTRING function.

  1  USE [Nahdy]
  2   GO
  3      
  4   /****** Object:  Table [dbo].[Student]    Script Date: 4/8/2021 11:02:03 PM ******/
  5   SET ANSI_NULLS ON
  6   GO
  7      
  8   SET QUOTED_IDENTIFIER ON
  9   GO
 10      
 11   SET ANSI_PADDING ON
 12   GO
 13      
 14   CREATE TABLE [dbo].[Student](
 15       [Sno] [int] NOT NULL,
 16       [Student ID] [nvarchar](6) NOT NULL,
 17       [Student name] [varchar](50) NOT NULL,
 18       [Date of Birth] [datetime] NOT NULL,
 19       [Weight] [int] NULL
 20   ) ON [Data Filegroup 1]
 21      
 22   GO
 23      
 24   SET ANSI_PADDING OFF
 25   GO
 26  
 27  procedure as below
 28  
 29   alter PROCEDURE Recover_Truncated_Data_Proc
 30   @Database_Name NVARCHAR(MAX),
 31   @SchemaName_n_TableName NVARCHAR(MAX),
 32   @Date_From datetime='1900/01/01',
 33   @Date_To datetime ='9999/12/31'
 34   AS
 35   DECLARE @Fileid INT
 36   DECLARE @Pageid INT
 37   DECLARE @Slotid INT
 38       
 39   DECLARE @ConsolidatedPageID VARCHAR(MAX)
 40   Declare @AllocUnitID as bigint
 41   Declare @TransactionID as VARCHAR(MAX)
 42       
 43   /*  Pick The actual data
 44   */
 45   declare @temppagedata table
 46   (
 47   [ParentObject] sysname,
 48   [Object] sysname,
 49   [Field] sysname,
 50   [Value] sysname)
 51       
 52   declare @pagedata table
 53   (
 54   [Page ID] sysname,
 55   [AllocUnitId] bigint,
 56   [ParentObject] sysname,
 57   [Object] sysname,
 58   [Field] sysname,
 59   [Value] sysname)
 60       
 61       
 62       DECLARE Page_Data_Cursor CURSOR FOR
 63       /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
 64       SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
 65       ,[Slot ID],[AllocUnitId]
 66       FROM    sys.fn_dblog(NULL, NULL)  
 67       WHERE   
 68       AllocUnitId IN
 69       (Select [Allocation_unit_id] from sys.allocation_units allocunits
 70       INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
 71       AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
 72       AND partitions.partition_id = allocunits.container_id)  
 73       Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
 74       AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS') 
 75       AND Description Like '%Deallocated%'
 76       /*Use this subquery to filter the date*/
 77       
 78       AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
 79       WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
 80       AND [Transaction Name]='TRUNCATE TABLE'
 81       AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 82       
 83       /****************************************/
 84       
 85       GROUP BY [Description],[Slot ID],[AllocUnitId]
 86       ORDER BY [Slot ID]    
 87           
 88       OPEN Page_Data_Cursor
 89       
 90       FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
 91       
 92       WHILE @@FETCH_STATUS = 0
 93       BEGIN
 94           DECLARE @hex_pageid AS VARCHAR(Max)
 95           /*Page ID contains File Number and page number It looks like 0001:00000130.
 96             In this example 0001 is file Number &  00000130 is Page Number & These numbers are in Hex format*/
 97           SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
 98           SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))  ---Seperate the page ID
 99           SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer
100           FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) 
101                           
102           DELETE @temppagedata
103           -- Now we need to get the actual data (After truncate) from the page
104       
105           INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;'); 
106           ---Check if any index page is there
107           If (Select Count(*) From @temppagedata Where [Field]='Record Type' And [Value]='INDEX_RECORD')=0
108           Begin
109               DELETE @temppagedata
110               INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;'); 
111           End
112           Else
113           Begin
114              DELETE @temppagedata
115           End
116       
117           INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
118           FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID
119       END
120       
121   CLOSE Page_Data_Cursor
122   DEALLOCATE Page_Data_Cursor
123       
124   DECLARE @Newhexstring VARCHAR(MAX);
125       
126   DECLARE @ModifiedRawData TABLE
127   (
128     [ID] INT IDENTITY(1,1),
129     [PAGE ID] VARCHAR(MAX),
130     [Slot ID] INT,
131     [AllocUnitId] BIGINT,
132     [RowLog Contents 0_var] VARCHAR(MAX),
133     [RowLog Contents 0] VARBINARY(8000)
134   )
135   --print N'before issue';
136   --The truncated data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
137   --This hex value is in string format
138       
139   INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId]
140   ,[RowLog Contents 0_var])
141   SELECT [Page ID],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) ) as [Slot ID]
142   ,[AllocUnitId]
143   ,(
144   SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
145   FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And
146   [Object] Like '%Memory Dump%'
147   FOR XML PATH('') ),1,1,'') ,' ','')
148   ) AS [Value]
149   From @pagedata B
150   Where [Object] Like '%Memory Dump%'
151   Group By [Page ID],[ParentObject],[AllocUnitId]
152   Order By [Slot ID]
153       
154   -- Convert the hex value data in string, convert it into Hex value as well. 
155   UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
156   FROM @ModifiedRawData
157       
158   DECLARE @RowLogContents VARBINARY(8000)
159   Declare @AllocUnitName NVARCHAR(Max)
160   Declare @SQL NVARCHAR(Max)
161   DECLARE @bitTable TABLE
162   (
163     [ID] INT,
164     [Bitvalue] INT
165   )
166   ----Create table to set the bit position of one byte.
167       
168   INSERT INTO @bitTable
169   SELECT 0,2 UNION ALL
170   SELECT 1,2 UNION ALL
171   SELECT 2,4 UNION ALL
172   SELECT 3,8 UNION ALL
173   SELECT 4,16 UNION ALL
174   SELECT 5,32 UNION ALL
175   SELECT 6,64 UNION ALL
176   SELECT 7,128
177       
178   --Create table to collect the row data.
179   DECLARE @DeletedRecords TABLE
180   (
181       [RowLogContents]    VARBINARY(8000),
182       [AllocUnitID]       BIGINT,
183       [Transaction ID]    NVARCHAR(Max),
184       [Slot ID]           INT,
185       [FixedLengthData]   SMALLINT,
186       [TotalNoOfCols]     SMALLINT,
187       [NullBitMapLength]  SMALLINT,
188       [NullBytes]         VARBINARY(8000),
189       [TotalNoofVarCols]  SMALLINT,
190       [ColumnOffsetArray] VARBINARY(8000),
191       [VarColumnStart]    SMALLINT,
192       [NullBitMap]        VARCHAR(MAX)
193   )
194      
195   --Create a common table expression to get all the row data plus how many bytes we have for each row.
196   ;WITH RowData AS (
197   SELECT
198       
199   [RowLog Contents 0] AS [RowLogContents] 
200       
201   ,[AllocUnitID] AS [AllocUnitID] 
202       
203   ,[ID] AS [Transaction ID]  
204       
205   ,[Slot ID] as [Slot ID]
206   --[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
207   ,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData
208       
209    --[TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
210   ,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
211   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as  [TotalNoOfCols]
212       
213   --[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
214   ,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
215   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength] 
216       
217   --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
218   ,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
219   CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
220   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]
221       
222   --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
223   ,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
224   CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
225   CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
226   + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
227   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols] 
228       
229   --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
230   ,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
231   SUBSTRING([RowLog Contents 0]
232   , CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
233   + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
234   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
235   , (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
236   CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
237   CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
238   + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
239   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)
240   * 2)  ELSE null  END) AS [ColumnOffsetArray] 
241       
242   --  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
243   ,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x30,0x70)
244   THEN  (
245   CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4 
246       
247   + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
248   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) 
249       
250   + ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x30,0x70) THEN
251   CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
252   CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
253   + CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
254   ,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2)) 
255       
256   ELSE null End AS [VarColumnStart]
257   From @ModifiedRawData
258   ),
259       
260   ---Use this technique to repeate the row till the no of bytes of the row.
261   N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
262   N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
263   N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
264   N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
265              FROM N3 AS X, N3 AS Y)
266       
267   insert into @DeletedRecords
268   Select   RowLogContents
269           ,[AllocUnitID]
270           ,[Transaction ID]
271           ,[Slot ID]
272           ,[FixedLengthData]
273           ,[TotalNoOfCols]
274           ,[NullBitMapLength]
275           ,[NullBytes]
276           ,[TotalNoofVarCols]
277           ,[ColumnOffsetArray]
278           ,[VarColumnStart]
279            --Get the Null value against each column (1 means null zero means not null)
280           ,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
281           (CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]
282   FROM
283   N4 AS Nums
284   Join RowData AS C ON n<=NullBitMapLength
285   Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
286   FROM RowData D
287       
288   CREATE TABLE [#temp_Data]
289   (
290       [FieldName]  VARCHAR(MAX) COLLATE database_default NOT NULL,
291       [FieldValue] VARCHAR(MAX) COLLATE database_default NOT NULL,
292       [Rowlogcontents] VARBINARY(8000),
293       [Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL,
294       [Slot ID] int
295   )
296   ---Create common table expression and join it with the rowdata table
297   --to get each column details
298   ;With CTE AS (
299   /*This part is for variable data columns*/
300   SELECT Rowlogcontents,
301   [Transaction ID],
302   [Slot ID],
303   NAME ,
304   cols.leaf_null_bit AS nullbit,
305   leaf_offset,
306   ISNULL(syscolumns.length, cols.max_length) AS [length],
307   cols.system_type_id,
308   cols.leaf_bit_position AS bitpos,
309   ISNULL(syscolumns.xprec, cols.precision) AS xprec,
310   ISNULL(syscolumns.xscale, cols.scale) AS xscale,
311   SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
312   --Calculate the variable column size from the variable column offset array
313   (CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
314   CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],
315       
316   ---Calculate the column length
317   (CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
318   - ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
319   ELSE 0 END) AS [Column Length]
320       
321   --Get the Hexa decimal value from the RowlogContent
322   --HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])
323   --This is the data of your column but in the Hexvalue
324   ,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
325   SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END)
326   - ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
327   - ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
328   ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
329   - ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
330   ELSE 0 END))) END AS hex_Value
331       
332   FROM @DeletedRecords A
333   Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
334   INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
335   AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
336   INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
337   LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
338   WHERE leaf_offset<0
339       
340   UNION
341   /*This part is for fixed data columns*/
342   SELECT  Rowlogcontents,
343   [Transaction ID],
344   [Slot ID],
345   NAME ,
346   cols.leaf_null_bit AS nullbit,
347   leaf_offset,
348   ISNULL(syscolumns.length, cols.max_length) AS [length],
349   cols.system_type_id,
350   cols.leaf_bit_position AS bitpos,
351   ISNULL(syscolumns.xprec, cols.precision) AS xprec,
352   ISNULL(syscolumns.xscale, cols.scale) AS xscale,
353   SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
354   (SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
355   sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
356   syscolumns.length AS [Column Length]
357       
358   ,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
359   SUBSTRING
360   (
361   Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
362   sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
363   ,syscolumns.length) END AS hex_Value
364   FROM @DeletedRecords A
365   Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
366   INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
367    AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
368   INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
369   LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
370   WHERE leaf_offset>0 )
371       
372   --Converting data from Hexvalue to its orgional datatype.
373   --Implemented datatype conversion mechanism for each datatype
374   --Select * from sys.columns Where [object_id]=object_id('' + @SchemaName_n_TableName + '')
375       
376   INSERT INTO #temp_Data
377   SELECT NAME,
378   CASE
379    WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))  --NVARCHAR ,NCHAR
380    WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))  --VARCHAR,CHAR
381    WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
382    WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
383    WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
384    WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
385    WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
386    WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
387    WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC  
388    WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
389    --WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- DECIMAL
390    WHEN system_type_id =106 And xscale=1 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --— DECIMAL
391      
392   When system_type_id =106 And xscale=0 THEN CONVERT(VARCHAR(MAX),CONVERT(bigINT,CONVERT(BINARY(8), REVERSE(hex_Value))))
393    WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))  -- BIT
394    WHEN system_type_id =62 THEN  RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
395    When system_type_id =59 THEN  Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
396    WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
397    WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
398    END AS FieldValue
399   ,[Rowlogcontents]
400   ,[Transaction ID]
401   ,[Slot ID]
402   FROM CTE ORDER BY nullbit
403       
404   --Create the column name in the same order to do pivot table.
405       
406   DECLARE @FieldName VARCHAR(max)
407   SET @FieldName = STUFF(
408   (
409   SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
410       
411   FOR XML PATH('')
412   ), 1, 1, '')
413       
414   --Finally did pivot table and got the data back in the same format.
415   --The [Update Statement] column will give you the query that you can execute in case of recovery.
416   SET @sql = 'SELECT ' + @FieldName  + ' FROM #temp_Data 
417   PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ')) AS pvt
418   ORDER BY Convert(int,[Transaction ID],Convert(int,[Slot ID]))'
419       
420   EXEC sp_executesql @sql
421       
422   GO