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
What I have tried:
1 USE [Nahdy]
2 GO
3
4
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
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
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
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
97 SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID))
98 SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))
99 SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)'))
100 FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
101
102 DELETE @temppagedata
103
104
105 INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');
106
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
136
137
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
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
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
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
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
207 ,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]
208
209
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
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
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
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
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
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
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
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)
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
297
298 ;With CTE AS (
299
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
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
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
322
323
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
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
373
374
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)))
380 WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20))))
381 WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value))))
382 WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value))))
383 WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value))))
384 WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))
385 WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100)
386 WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100)
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))
388 WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2)
389
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))
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))
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))))
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)
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)')
397 WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value))
398 END AS FieldValue
399 ,[Rowlogcontents]
400 ,[Transaction ID]
401 ,[Slot ID]
402 FROM CTE ORDER BY nullbit
403
404
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
415
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