April 9, 2021 at 5:43 pm
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
April 9, 2021 at 6:22 pm
please don't post same issue twice.
original thread here https://www.sqlservercentral.com/forums/topic/msg-537-level-16-state-3-procedure-recover_truncated_data_proc-line-113-inva
Ratbak already gave you a indication of what you should do - what did you find so far after following those instructions?
April 9, 2021 at 11:08 pm
i write this post again because i don't mention issue on first post clear
so i write here clear on this post
my issue on lines below
SELECT [Page ID],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4)) ) as [Slot ID]
so please how to solve issue of
Invalid length parameter passed to the LEFT or SUBSTRING function.
April 10, 2021 at 4:42 pm
Also here: http://www.c-sharpcorner.com/forums/msg-537-level-16-state-3-procedure-recovertruncateddataproc-lin. Looking at your posting history, you have 106 posts over nine years. Normally I'd expect someone working on SQL Server over that time to be able to resolve this kind of issue on their own, especially when a simple search on the error message would provide the solution. When you post, could you say what you have tried to resolve the problem? That way the people who volunteer a large amount of their free time will know that you have put some effort into resolving the query and that you aren't just using them as free help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply