question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft commented

How to solve issue of Invalid length parameter passed to the LEFT or SUBSTRING function ?

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

 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],CHARINDEX('Slot', [ParentObject])+4, (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


 30001400010000000000000060940000280000000500e0020029002d00
 530054004400300030003100426f6231









sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have anyupdate?Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ahmedsalah-1628

Truncate means removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Since nothing is recorded, the truncated data cannot be recovered.

Some third-party tools seem to be able to recover the data after truncation, but if there is an update, insert or delete operation after truncation, the data page may be overwritten and the data cannot be restored.

Therefore, it is best to back up the data before performing the truncation operation.

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

what you mean by restore clean backup database

RESTORE DATABASE db FROM DISK = ...

Since only one table was lost, you probably want to restore it as a copy. Also, you may want to make a point-in-time restore, so that you can recover the table before the accident.

and can you help me by coding to solve issue of substring please

No. To be perfectly blunt. You are attempting an operation which is advanced and well over your head. As a matter of fact, it is advanced for me as well.

It is also very unlikely that you would be able to recover the table this way, because for this to be possible, the following must be true:
1. The database is in full recovery.
2. The transaction log has not been backed up since the accident.
This is extremely unlikely, and if would be true, this is a case of mismanagement, because the transaction log should be backed up regularly, several times per day.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ahmedsalah-1628 commented

Answer: you restore a clean backup of the database, rather than trying to run a script that you found somewhere.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

what you mean by restore clean backup database
and can you help me by coding to solve issue of substring please

0 Votes 0 ·

can you please tell me
how to solve issue n that line please
SELECT [Page ID],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) ) as [Slot ID]

0 Votes 0 ·