Post
Ask Question
ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 275.3k

Msg 537, Level 16, State 3, Procedure Recover_Truncated_Data_Proc, Lin

Apr 8 2021 9:12 PM

I work on sql server 2012 i call procedure name as below
EXEC Recover_Truncated_Data_Proc 'Nahdy','dbo.Student'
I get error
Msg 537, Level 16, State 3, Procedure Recover_Truncated_Data_Proc, Line 113 Invalid length parameter passed to the LEFT or SUBSTRING function.
so How to solve this issue please

this table dbo.Student
  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_bitAS [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
  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

Answers (2)