There are two functions:
- CONPEEK(varbinary, int)
- CONSIZE(image)
CONPEEK acts exactly as DAX handles it so it should be no mystery. CONSIZE is used within the CONPEEK function to handle nested containers, it will just give you the size in bytes of the container (Not to be confused with conlen() in DAX).
Note: Sorry, you must CAST the return value of CONPEEK as a varbinary(8000) before calling back into CONPEEK when dealing with nested containers. I could not find a way around it.
Usage:
DECLARE @con AS varbinary(8000);
-- An example container with structure:
-- * 2 (int)
-- * (container)
-- * 17 (int)
-- * 'abc' (str)
SET @con = 0x07FD01020000000707FD0111000000006100620063000000FFFF;
SELECT CONPEEK(@con, 1); --returns 2
SELECT CONPEEK(@con, 2); --returns a container (0x07FD0111000000006100620063000000FF)
SELECT CONPEEK(CAST(CONPEEK(@con, 2) AS varbinary(8000)), 1); --returns 17
SELECT CONPEEK(CAST(CONPEEK(@con, 2) AS varbinary(8000)), 2); --returns 'abc'
Downloads:
Changelog:
- 2011-11-21 - Bug - Strings were limiting to 30 characters, this is fixed
- 2012-11-15 - Feature - Added ability to read utcDateTime elements
- 2013-11-27 - Feature - Added ability to read EnumLabel elements
- 2014-04-02 - Bug - Fixed DateTime bug...Workaround added for unknown 0x31 type
- 2014-06-07 - Bug - Fixed Enum bug, int to string conversion error
- 2014-06-09 - Bug - Fixed DateTime bug, wrong parameter was referenced
- 2014-08-27 - Feature - Added ability to read Int64 elements
- 2014-12-10 - Bug - Fixed DateTime bug, conversion issue from string to datetime is fixed
- 2015-12-10 - Feature - Added ability to read BLOB elements
- 2015-12-10 - Bug - Fixed Enum bug, returns enum int value instead of 0 or 1
Hi,
ReplyDeletethanks, this makes my life much easier :-)
But I stumbled around a missing datatype:
0x06 is for datetime
It is packed into 12 bytes. First 3 are the same as for date, the next 3 bytes are hour, minute, second (those are exact, not minus one!). The next 6 bytes were always 0x00 in my cases.
Hey, thanks for commenting, I'm glad it helped out somebody. Could I ask you for an example of a BLOB which has this new type and tell me what the datetime value is supposed to be, then I can test it myself before committing the change.
DeleteBy the way, which version of AX are you on? If I'm not mistaken, datetimes were first implemented in AX 2012, which is why I would've missed it, I'm using 4.0.
Would this work?
ReplyDelete---
ELSE IF SUBSTRING(@bin, @pos, 1) = 0x06 --DATETIME
BEGIN
SET @pos = @pos + 1;
DECLARE @year char(4);
DECLARE @month char(2);
DECLARE @day char(2);
DECLARE @hour char(2);
DECLARE @minute char(2);
DECLARE @second char(2);
SET @year = SUBSTRING(@bin, @pos, 1) + 1900;
SET @month = SUBSTRING(@bin, @pos + 1, 1) + 1;
SET @day = SUBSTRING(@bin, @pos + 2, 1) + 1;
SET @hour = SUBSTRING(@bin, @pos + 3, 1) + 1;
SET @minute = SUBSTRING(@bin, @pos + 4, 1) + 1;
SET @second = SUBSTRING(@bin, @pos + 5, 1) + 1;
IF LEN(@month) < 2
SET @month = '0' + @month;
IF LEN(@day) < 2
SET @day = '0' + @day;
IF LEN(@hour) < 2
SET @hour = '0' + @hour;
IF LEN(@day) < 2
SET @minute = '0' + @minute;
IF LEN(@second) < 2
SET @second = '0' + @second;
SET @ret = CAST(@year + '-' + @month + '-' + @day + ' ' + @hour + ':' + @second + ':' + @second AS datetime);
SET @pos = @pos + 12;
END
You're so close, no +1's for the hour/minute/second as they are exact values. The reason for this, time values can have 0 as a value, date values cannot.
DeleteAt any rate, I have uploaded a new version of CONPEEK to include these datetimes...a very much overdue change if I might add
No matter what number I pass to ConPeek, i get nulls returned. Any Ideas (this is still for 4.0)
DeleteDECLARE @con AS varbinary(8000);
SELECT dbo.CONPEEK(CAST(dbo.CONPEEK(@con, 2) AS varbinary(8000)), 1)from INVENTTABLEMODULE
You're not selecting any column from InventTableModule. Right now, all rows returned are using a null defined (as it's never set) varbinary and trying to CONPEEK that. This is resulting in your nulls.
DeleteAlso, InventTableModule doesn't have any container fields in it, so unless you have something there that is custom, there is nothing to CONPEEK there, so I cannot offer any suggestions as to what you are trying to accomplish.
so what the heck is that table, i can't query against it at all, or at least 3/4 of the fields won't show up. Any help appreciated
DeleteUsually people trying to use this CONPEEK function is because they are externally SQL reporting against a table that has a container field (which shows up as binary junk that's not human readable). InventTableModule contains information on setup of items in each of the 3 modules (Sales, Purch, Invent), you will have 3 records per each record in InventTable. However, I can't guess what you're trying to do.
DeleteHi,
Deletegreat job so far. Anyway, I get an error processing one of the datasets:
Meldung 242, Ebene 16, Status 3, Zeile 4
Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
Data is:
0x07FD0707FD006D006F00640069006600690065006400420079000000006E002E00660061006C006B0065006D000000000000FF0707FD006D006F006400690066006900650064004400610074006500540069006D00650000000673000C0F311C00008990000006000000000000000000000000FF0707FD00720065006300560065007200730069006F006E000000014AC5854A0101000000FF0707FD004E0075006D00620065007200000001030000000100000000FF0707FD00520061006E006B00000001010000000100000000FFFF
Thanks in advance!
Hey, awesome functions - saved me heaps of time. Just one more type to add - 0xFC for enums. They're built with a byte for their value followed by a null terminated string (enum description).
ReplyDeleteInteresting! Would you have an example of that? Or how to create one from code? I'm not sure how the container would be able to determine what type of enum is in the container.
DeleteNo worries. Here's an example from our system (new AX2012 implementation), but it's probably fairly universal since it comes from the PRINTMGMTSETTINGS table.
DeleteA binary substring from the container is:
0xFC025300520053005200650070006F0072007400460069006C00650046006F0072006D00610074000000
As you can see, the first byte (0xFC) establishes that this is an enum. The next byte (0x02) is the value. Everything after that up to the null word is the string description of the enum. Converting the binary gives us "SRSReportFileFormat". To see what the enum value actually stands for, I had a look in the AOT data dictionary, and found an entry under SRSReportFileFormat called "HTML4_0" that had a value of 2. There's probably a way to do that translation in the tables but I haven't needed to go that far yet. Interestingly, even though there is a byte for the value, if you try to add an enum element with a value over 250 you get an error. Maybe there's reserved values or something (e.g. 0xFF)...
Ah, I see, the string value in the container is the AOT name for the enum header, not the enum child. And yes, you are correct, some of those values are reserved, like 0x FD which marks the start of a container and 0xFF, as you noted, which marks the end. I know exactly what I need to do to search for the enum child's label value. I'd imagine this CONPEEK function should then return the string value of the enum's label, not the name of the enum child. I'll work on getting this added in the next couple weeks (on vacation this week)
DeleteI have actually gotten around to applying this fix. Although I only have a temporary solution, the return value as of now is the EnumAOTName:Value. The more elegant solution is to actually return the label of the enum child, this I will add later.
DeleteExcellent post!
ReplyDeleteWe have strucked at point where the complexity of a container is not know. For example in Dynamics AX's SysDataBaseLog table > Data field is a container type and finding a value of a particular fieldId which can be at any level does not seems to a simple job. Do you know a better way to iterate thru a nested container structure and finding a value from any level by matching the value .
Sorry for being a hard ***
thank you
As long as the container's schema doesn't change throughout the records in this table, it should be possible. Unfortunately, I don't have an example of this in my local install of AX. Would you be able to provide me a binary example? (Also, verify when you paste it in your response that it ends in FF, sometimes SSMS truncates the values if they are long enough, and I can only assume SysDatabaseLog stores very large values).
DeleteHi codemann8,
DeleteHere is a binary representation of the SYSDATABASELOG.DATA field. I tried running with your code it does not work properly. Please help
0x07FD0707FD005400610062006C006500490064000000014D000000FF0707FD00520065006300490064000000311626005001000000FF0707FD0050006100720074006900740069006F006E000000310000005001000000FF0707FD00720065006300560065007200730069006F006E0000000101000000FF0707FD00730065007100750065006E00630065004E0075006D000000311626005001000000FF0707FD0064006100740061004100720065006100490064000000007500630063000000FF0707FD006300720065006100740065006400420079000000006A006D0070000000FF0707FD00640045004C005F004300720065006100740065006400540069006D006500000001F9D80000FF0707FD0063007200650061007400650064004400610074006500540069006D00650000000672010A04192D000089900000FF0707FD006D006F00640069006600690065006400420079000000006A006D0070000000FF0707FD00640045004C005F004D006F00640069006600690065006400540069006D006500000001F9D80000FF0707FD006D006F006400690066006900650064004400610074006500540069006D00650000000672010A04192D000089900000FF0707FD0044004100580049006E0074006500670072006100740069006F006E004900640000002D2B74D0A26277BD4CBF2C45C110C81DF9FF0707FD004D00610069006E0043006F006E00740061006300740057006F0072006B00650072000000310000005001000000FF0707FD0050006100720074007900000031AE60005001000000FF0707FD00540061007800470072006F00750070000000004700530054000000FF0707FD00430075007200720065006E00630079000000004100550044000000FF0707FD005000610079006D005400650072006D0049006400000000370044000000FF0707FD004300750073007400470072006F007500700000000052004100470053000000FF0707FD004100630063006F0075006E0074004E0075006D000000004E00450057004A0045004E0031000000FFFF
It seems there is a new type I'm unaware of, 0x31...I've modified the SQL script so it won't fail, however, this unknown type for the time being will return 0 until I figure out what it is. I'll look into it and hopefully post a real solution.
DeleteWould you be able to help in telling me what your database log tells you for the first 5 entries? Right now I don't have a client who is using database logging for me to look into this.
DeleteHi codemann8,
ReplyDeleteThe container values in AX is such:
https://dl.dropboxusercontent.com/u/8507404/AX_DATA.PNG
Hmm...that doesn't seem to be the same container. The binary version you sent earlier contains only two objects in each sub-container.
DeleteHi codemann8,
ReplyDeleteSorry about that.
The binary for https://dl.dropboxusercontent.com/u/8507404/AX_DATA.PNG
is :
0x07FD0707FD006D006F00640069006600690065006400420079000000006A006D0070000000006A006D0070000000FF0707FD006D006F006400690066006900650064004400610074006500540069006D00650000000672010A04223200008990000006710908040D36000000000000FF0707FD00720065006300560065007200730069006F006E00000001EC2CC3450101000000FF0707FD0044004100580049006E0074006500670072006100740069006F006E004900640000002DE56A1C523BA8B046A1EB1A74917877842D00000000000000000000000000000000FF0707FD004100630063006F0075006E007400530074006100740065006D0065006E0074000000FC0343007500730074004100630063006F0075006E007400530074006100740065006D0065006E0074000000FC0043007500730074004100630063006F0075006E007400530074006100740065006D0065006E0074000000FFFF
This is an Update transaction on the CustTable
Ah, I see the problem now. When I added DateTime, I forgot to update CONSIZE to measure the size of the new datatype. I updated this and also included the new type mentioned above, so it still should "run". I still don't know what kind of data that new type is. Would you be able to provide another example of that SysDatabaseLog entry from before, something that has that new type?
DeleteHi codemann8,
DeleteThis is the AX representation of the first binary I sent. The only new data type field I can see I have highlighted. But this is interpreted well by your code.
https://dl.dropboxusercontent.com/u/8507404/part1.PNG
https://dl.dropboxusercontent.com/u/8507404/part2.PNG
I think i am runinng into another data type issue here.
DeleteDECLARE @con AS varbinary(8000);
SET @con = 0x07FD0707FD00720065006300560065007200730069006F006E000000010EC080290101000000FF0707FD004100630074006900760065000000FC014E006F005900650073000000FC004E006F005900650073000000FFFF;
SELECT dbo.CONPEEK(CAST(dbo.CONPEEK(@con, 2) AS varbinary(8000)), 2);
or
DECLARE @con AS varbinary(8000);
SET @con = 0x07FD0707FD00720065006300560065007200730069006F006E000000010EC080290101000000FF0707FD004100630074006900760065000000FC014E006F005900650073000000FC004E006F005900650073000000FFFF;
SELECT dbo.CONPEEK(CAST(dbo.CONPEEK(@con, 2) AS varbinary(8000)), 3);
returns error
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value 'NoYes:' to data type int.
I am i missing something here?
Inside CONPEEK in the --ENUM section, can you try changing this line: SET @ret = CAST(@name + ':' + @value as varchar(44)); to
DeleteSET @ret = CAST(@name + ':' + CAST(@value as varchar(3) as varchar(44));
Does that solve the issue?
Changing that line to
DeleteSET @ret = CAST(@name + ':' + cast(@value as varchar(3)) as varchar(44));
did correct this issue, Thanks for such a fast response.
I now have a different issue. if you can help that would be awesome.
DECLARE @con AS varbinary(8000);
SET @con = 0x07FD0707FD005400610062006C00650049006400000001B98B0100FF0707FD0052006500630049006400000031A87D005001000000FF0707FD00720065006300560065007200730069006F006E0000000101000000FF0707FD00730065007100750065006E00630065004E0075006D00000031A87D005001000000FF0707FD006400610074006100410072006500610049006400000000750073000000FF0707FD0063007200650061007400650064004200790000000056004D006100630068006C00650074000000FF0707FD00640045004C005F004300720065006100740065006400540069006D0065000000014C850000FF0707FD0063007200650061007400650064004400610074006500540069006D0065000000067204000E1C2C000089900000FF0707FD006D006F006400690066006900650064004200790000000056004D006100630068006C00650074000000FF0707FD00640045004C005F004D006F00640069006600690065006400540069006D0065000000014C850000FF0707FD006D006F006400690066006900650064004400610074006500540069006D0065000000067204000E1C2C000089900000FF0707FD00500072006F00640075006300740069006F006E0052006500710000000072006500710075006900720065007300200067006C007500740065006E002000740065007300740069006E0067002000610073002000770065006C006C0020006100730020006D006900630072006F002000740065007300740069006E0067000000FF0707FD004D006900630072006F0054006500730074000000FC014E006F005900650073000000FF0707FD004900740065006D00490064000000003900330036002E00310037003500350055002D005000500031002D00330035000000FF0707FD00440069006D0065006E00730069006F006E00440065006600610075006C007400000031BF0A005001000000FF0707FD00430075007300740050006100720074004E0075006D0000000046004C00410031003100360033005F00470046000000FF0707FD0043007500730074004100630063006F0075006E0074000000003000390033003600300030000000FF0707FD004100630074006900760065000000FC014E006F005900650073000000FFFF
SELECT dbo.CONPEEK(CAST(dbo.CONPEEK(@con,8) AS varbinary(8000)), 2);
returns
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
Any hints/ ideas?
Again thanks for help
In the Datetime portion, I think I made a boo-boo. I believe I am referencing @month when I should be referencing @month2, the same goes with @day. Please change @day and @month to @day2 and @month2 (only in the datetime portion)
Deletehi codemann8,
ReplyDeletethanks a lot for these 2 scripts. about the 0x31 it is bigint type. I have updated your scripts by :
Consize : ELSE IF SUBSTRING(@bin, @pos, 1) = 0x31 --bigint
BEGIN
SET @pos = @pos + 9;
END
Conpeek : ELSE IF SUBSTRING(@bin, @pos, 1) = 0x31 --bigint
BEGIN
BEGIN
SET @pos = @pos + 1;
SET @ret = CAST(REVERSE(SUBSTRING(@bin, @pos, 8)) AS binary(8));
SET @ret = CAST(@ret AS bigint);
SET @pos = @pos + 8;
END
END
and for it is works well on my AX Log
thanks again,
sylvain
Thank you! I can't believe I forgot about int64. I've updated the script to include your logic.
DeleteHi codemann8,
ReplyDeletethanks a lot for these scripts, working great !!
I just have one problem. I get following error for this query:
DECLARE @con AS VARBINARY(8000);
SET @con = 0x07FD0707FD0102F00100006D00610072006B006500000000730062007500650041000000FF0707FD0100F001000670061B10322E0000000000000670061B100020000000000000FF0707FD010AF0010001AB67B47801339B894CFF0707FD01659C010004011DF004001DF0FFFF;
SELECT [dbo].[CONPEEK](CAST([dbo].[CONPEEK](@con, 2) AS VARBINARY(8000)), 2);
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
Sounds like a problem one had before. I am not able to fix this issue. Do you have any suggestions?
best regards,
bastian
Kind of a strange bug but I found it. Adding + 0 to hours, minutes, and seconds makes it happy. I'm guessing that converting from a varbinary to a char is different than converting from an int to a char. I've updated the code.
DeleteHi!
ReplyDeleteNice functions!
I have one problem, it always Return NULL.
I'm trying to Select the field PrintJobSettings from PrintMgmtSettings, to find and email address.
Is this correct?
select DBO.CONPEEK(CAST(DBO.CONPEEK(PRINTJOBSETTINGS, 1) AS varbinary(8000)), 1) as blobtxt, * from printmgmtsettings where parentid in (5637189110, 5637183032, 5637149846);
My con looks like this:
https://www.dropbox.com/s/z4wnlt9nqo4sjll/blob.txt?dl=0
Any sugestions?
When I run select * from printmgmtsettings the field PrintJobSettings itself is always blank in every record so I cannot test myself. However, there seems to be multiple unknown new types of data in this container. I'm seeing at least a new 0x30, 0x05, 0x34 so far with unknown sizes, and I'd imagine this will take a while to figure out. If you're able to step through the container and identify the new types and how many bytes it is, you could update ConSize to include them, and they'll just skip over those values. No immediate fix for this.
DeleteActually, I'm looking at this, and this structure looks wildly different than a normal container. Are you able to remove the print management setting and then re-add it. Lets see if that changes the container.
DeleteI finally figured this out, there's only one new type in your container 0x30, it is a BLOB type. I've updated the code to add the new type, however, you're example has only one large BLOB object. And, it appears the data from within a BLOB could be ANYTHING from a JPG, a CSV, a DOC, it could be anything so it will be impossible to pull anything from unless you know the type, and so this goes beyond the container format so from the scope of this blog post and what I'm trying to accomplish, I cannot further determine how to pull out your email address
DeleteHi,
ReplyDeletegreat job so far. Anyway, I get an error processing one of the datasets:
Meldung 242, Ebene 16, Status 3, Zeile 4
Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
Data is:
0x07FD0707FD006D006F00640069006600690065006400420079000000006E002E00660061006C006B0065006D000000000000FF0707FD006D006F006400690066006900650064004400610074006500540069006D00650000000673000C0F311C00008990000006000000000000000000000000FF0707FD00720065006300560065007200730069006F006E000000014AC5854A0101000000FF0707FD004E0075006D00620065007200000001030000000100000000FF0707FD00520061006E006B00000001010000000100000000FFFF
It's AX 2012R3.
Thanks in advance!
Hi codemann8,
ReplyDeletebelow is the data in the sysdatabaselog.data field
set @data = 0x07FD0707FD010E0001000402350004003500FFFF
dbo.CONPEEK(CAST(dbo.CONPEEK(data,1) AS varbinary(8000)), 2)
the value stored is an enum value 2 (CustVendBlocked::ALL) but the results shows 0.
Where as the same code works for integer data type of the sysdatabaselog record
0x07FD0707FD014675010002010000000000000000100200000000000000000050FFFF
result is 10, which is correct
Please help. thanks in advance
Sorry for the late response...I've actually just realized that 0x04 is an ENUM type, not a BOOL as I previously thought. I've updated this so it should work. It will return the int value of the enum.
DeleteCodemann8
ReplyDeleteI cannot get this to work for these tables in AX. Does not return the values in AX.
1) Table: WHSREPLENISHMENTTEMPLATELINE Field: ItemQuery
2) Table: REQLOG Field: Log
Here's the code I used, but neither returns the data as it is in AX:
select
dbo.CONPEEK(aa.itemquery, 1) as Itemquery1a,
convert( nvarchar(max), CAST( dbo.CONPEEK(aa.itemquery, 1) AS nvarchar(max) ) ) as Itemquery2c
from WHSREPLENISHMENTTEMPLATELINE aa
Please help. Thanks in advance.
Please include an example of the BLOB data in the container field ie. 0x07FD....FF. Please include an example from both tables as the current client I'm working with does not have any sample data for me to work with.
DeleteSorry for the slow response. Going thru implementation right now, and busy as all get out. Again, thanks in advance for any help you can provide.
Delete1) Table: WHSREPLENISHMENTTEMPLATELINE Field: ItemQuery
0x07FD30140300004A012F27570048005300520065006C0065006100730065006400500072006F006400750063007400560061007200690061006E00740073000000110001E8033600570048005300520065006C0065006100730065006400500072006F006400750063007400560061007200690061006E00740073000000E6491D0000000A4DEB03000000008404450063006F00520065007300440069007300740069006E0063007400500072006F006400750063007400560061007200690061006E0074000000110001E8033A00450063006F00520065007300440069007300740069006E0063007400500072006F006400750063007400560061007200690061006E0074000000E2093A00450063006F00520065007300440069007300740069006E0063007400500072006F006400750063007400560061007200690061006E0074000000094DE9030000D919000000920402001100010000FFFFFFFF860444006900730070006C0061007900500072006F0064007500630074004E0075006D006200650072000000110001E8032A0044006900730070006C0061007900500072006F0064007500630074004E0075006D006200650072000000E2093A00450063006F00520065007300440069007300740069006E0063007400500072006F006400750063007400560061007200690061006E0074000000E3092A0044006900730070006C0061007900500072006F0064007500630074004E0075006D006200650072000000000000000001FFFF840449006E00760065006E007400440069006D0043006F006D00620069006E006100740069006F006E000000110001E8032A0049006E00760065006E007400440069006D0043006F006D00620069006E006100740069006F006E000000E2092A0049006E00760065006E007400440069006D0043006F006D00620069006E006100740069006F006E000000094DEA030000D91900DF19010000920402001100010000FFFFFFFFFFFFFFFF8F04FFFF00000000000000FFFF9B04FFFF9A04FFFF00000000000001FFFFFFFF009005000000000000000000000000000000000000000000000000000000000000FF
2) Table: REQLOG Field: Log (example exceeds the 4,096 character max).
Looks like your #1 example is a container of one element and it is a BLOB type of length 788 bytes, does the CONPEEK not return that? I will refer you to Terje's comment just a bit above, I cannot further dive into BLOB types as these can be any type of data.
DeleteYour #2 I cannot look at without an example, but it sounds like it will exceed a character limit, which I cannot find a workaround for.
Nice utility. I tried modifying it a bit and couldn't get it work. The problem I was facing was that my container could be 30 or even 50 levels deep, and to see all of those it kept reprocessing the same container elements over and over.
ReplyDeleteMy idea was to convert it to a sproc that had 2 output variables: your @ret and the input @bin with @ret stripped off the beginning. So each time the sproc is called, it "pops" the first container element off the container, leaving the container one element smaller.
Does that sound like something you'd be willing to investigate?
I would've done it that way if there was something in the data structure that gives you the length of the container, but there is not, just the trailing "FF" to mark the end. So the only logical approach that's possible is a depth-first approach. You also cannot assume that the contents just prior to the final FF belongs to the same element inside the container.
DeleteHi, I just found a bug which may occur when the data contain non-ANSI characters. Please use NCHAR and NVARCHAR instead of just CHAR and VARCHAR to storing the data. Hope you have a new release. Thanks!
ReplyDeleteHey Cody, I used these scripts a year or so ago when I was troubleshooting some events that were happening in our SalesLine table. We have since decided that we are going to be dumping the full log into a normalized table for data warehousing purposes. When I first test the SQL functions, the performance was OK, but I was convinced that I could make it run faster with a custom C# deserialization assembly called from a SQL function. I was able to deserialize 9,000,000 log records into 150,000,000 individual data fields in about 5 minutes. I used all of your logic, but used core .NET BinaryReader functions to pull apart the data quicker. If you are interested in the source code to post somewhere on your site, I would be more than willing to post it.
ReplyDeleteIf you still have that code I certainly wouldn't mind taking a peek at it.
DeleteHero!
ReplyDeleteThese SPs are great - thanx so much! Any thoughts about parsing type 0x1D, a packed query?
ReplyDeleteThank you so much. This is making my life much easier.
ReplyDeleteHello. I do not know why, but the CAST command does not handle the date "2018-01-26 12:04:51"
ReplyDeleteWhen processing DATETIME, I changed:
SET @ret = CAST (@ year2 + '-' + @ month2 + '-' + @ day2 + '' + @hour + ':' + @min + ':' + @sec AS datetime);
on:
SET @ret = CONVERT (DATETIME, @ year2 + '-' + @ month2 + '-' + @ day2 + '' + @hour + ':' + @min + ':' + @sec, 102);
Thank you very much for your work. God bless the internet and people like you! :)
thx a lot, this is really useful
ReplyDeleteHi Cody, to handle nchar strings I adjusted "STRING" case of CONPEEK function:
ReplyDelete- from:
SET @ret = CAST(@ret AS varchar(8000)) +
CHAR(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)))
- to:
SET @ret = CAST(@ret AS nvarchar(4000)) +
NCHAR(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)))
Here is an example of decoding string 'ΕΘNΙΚΗ ΤΡΑ':
DECLARE @con AS varbinary(8000) =
0x07FD011D00010000950398034E0099039A0397032000A403A103910300000039003200340034003000350032003500300032000000FF;
SELECT dbo.CONPEEK(@con, 2)
- originally returned '?TN??? ??Α'
- after fix - 'ΕΘNΙΚΗ ΤΡΑ' what is how DAX (2009) displays
It works fine and is fully compatible with your standard.
Once again many thanks for that work, it let me create changes tracking report outside DAX.
Awesome. Thanks so much, this is helping us when used with SYSDBLog to create a change log BI report/odata feed for monitoring banking details.
ReplyDeletehey man, any chance you can add the type: 0x79?
ReplyDelete