locked
How to solve issue of Invalid length parameter passed to the LEFT or SUBSTRING f RRS feed

  • Question

  • User696604810 posted

    I work on sql server 2014 I face issue error as Invalid length parameter passed to the LEFT or SUBSTRING function when run script below ?

    Microsoft SQL Server 2014 - 12.0.2269.0 (X64)

    Enterprise Edition (64-bit) on Windows NT 6.3

    I create table student with rows values insert as below :

     CREATE TABLE [dbo].[Student](  
            [Sno] [int] NOT NULL,  
            [Student ID] nvarchar(6) Not NULL ,  
            [Student name] [varchar](50) NOT NULL,  
            [Date of Birth]  datetime not null,  
            [Weight] [int] NULL)  
      --Insert data into table
      Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40)  
      Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',35)

    2- after create table student and insert rows on it

    I make the following

    1. truncate table [dbo].[Student]

    after that i need to get truncated data so
    I run script below
    I get error
    Msg 537, Level 16, State 3, Line 96
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    issue exist on line 96

     SELECT [Page ID],
          Substring([ParentObject], case when CHARINDEX('Slot', [ParentObject]) < 0 then len([ParentObject]) else ABS(CHARINDEX('Slot', [ParentObject])+4) end, CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) as [Slot ID]

    and this script give me error above
    so How to solve issue ?

    declare @Database_Name NVARCHAR(MAX)='Nahdy'
     declare @SchemaName_n_TableName NVARCHAR(MAX)='dbo.homo'
     declare @Date_From datetime='1900/01/01'
     declare @Date_To datetime ='9999/12/31'
     DECLARE @Fileid INT
     DECLARE @Pageid INT
     DECLARE @Slotid INT
         
     DECLARE @ConsolidatedPageID VARCHAR(MAX)
     Declare @AllocUnitID as bigint
     Declare @TransactionID as VARCHAR(MAX)
         
     /*  Pick The actual data
     */
     declare @temppagedata table
     (
     [ParentObject] sysname,
     [Object] sysname,
     [Field] sysname,
     [Value] sysname)
         
     declare @pagedata table
     (
     [Page ID] sysname,
     [AllocUnitId] bigint,
     [ParentObject] sysname,
     [Object] sysname,
     [Field] sysname,
     [Value] sysname)
         
         
         DECLARE Page_Data_Cursor CURSOR FOR
         /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
         SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
         ,[Slot ID],[AllocUnitId]
         FROM    sys.fn_dblog(NULL, NULL)  
         WHERE   
         AllocUnitId IN
         (Select [Allocation_unit_id] from sys.allocation_units allocunits
         INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
         AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
         AND partitions.partition_id = allocunits.container_id)  
         Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
         AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS') 
         AND Description Like '%Deallocated%'
         /*Use this subquery to filter the date*/
         
         AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
         WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
         AND [Transaction Name]='TRUNCATE TABLE'
         AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
         
         /****************************************/
         
         GROUP BY [Description],[Slot ID],[AllocUnitId]
         ORDER BY [Slot ID]    
             
         OPEN Page_Data_Cursor
         
         FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
         
         WHILE @@FETCH_STATUS = 0
         BEGIN
             DECLARE @hex_pageid AS VARCHAR(Max)
             /*Page ID contains File Number and page number It looks like 0001:00000130.
               In this example 0001 is file Number &  00000130 is Page Number & These numbers are in Hex format*/
             SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
             SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))  ---Seperate the page ID
             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
             FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) 
                             
             DELETE @temppagedata
             -- Now we need to get the actual data (After truncate) from the page
         
             INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;'); 
             ---Check if any index page is there
             If (Select Count(*) From @temppagedata Where [Field]='Record Type' And [Value]='INDEX_RECORD')=0
             Begin
                 DELETE @temppagedata
                 INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;'); 
             End
             Else
             Begin
                DELETE @temppagedata
             End
         
             INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
             FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID
         END
         
     CLOSE Page_Data_Cursor
     DEALLOCATE Page_Data_Cursor
         
     DECLARE @Newhexstring VARCHAR(MAX);
         
      SELECT [Page ID],
      Substring([ParentObject], case when CHARINDEX('Slot', [ParentObject]) < 0 then len([ParentObject]) else ABS(CHARINDEX('Slot', [ParentObject])+4) end, CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) as [Slot ID]
     ,[AllocUnitId]
     ,(
     SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
     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
     [Object] Like '%Memory Dump%'
     FOR XML PATH('') ),1,1,'') ,' ','')
     ) AS [Value]
     From @pagedata B
     Where [Object] Like '%Memory Dump%'
     Group By [Page ID],[ParentObject],[AllocUnitId]
     Order By [Slot ID]

    the above script exist on website as below :
    https://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/
    but it give issue
    the goal from script it will return rows truncated data from table students

    1. 30001400010000000000000060940000280000000500e0020029002d00
    2. 530054004400300030003100426f6231
    Friday, April 9, 2021 5:47 PM

All replies

  • User-1716253493 posted

    AFAIK, length parameter is positive value

    Friday, April 9, 2021 6:05 PM
  • User-939850651 posted

    Hi ahmedbarbary,

    It seems that you have modified this part of sql query based on this article, but I am a little confused about your test.

     declare @SchemaName_n_TableName NVARCHAR(MAX)='dbo.homo'

    I see that you used the same table (dbo.[Student]) for this test, but in the following parameters, you used "dbo.homo", I think this should be the cause of the problem.

    I see that you also use temporary tables. Have you checked the contents of temporary tables? Is it possible that the field value is null?

    If possible, please provide more details.

    Best regards,

    Xudong Peng

    Monday, April 12, 2021 6:08 AM