2

Inside of the sys.assembly_files catalog, I can see a field named content

1> SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
2> GO
assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x07585

(1 rows affected)

However, even with sqlcmd -Y 8000 -y 5000 I can't get more than 0x4D5A90x075850x167D00x043CB

assembly_id                                                    file_id     content
----------- -------------------------------------------------- ----------- -------
          1 microsoft.sqlserver.types.dll                                1 0x4D5A90x075850x167D00x043CB

Wondering what was going on and suspecting the client wasn't returning everything to me, I tried mssql-cli, the port of pg-cli to SQL Server. Using mssql-cli, I saw the entirety of the content field, which does in fact look to be the right size to be the bit contents. That leaves me to believe this is a problem in sqlcmd.exe truncating, or other mutilating and adulterating the bytecode representation of the assembly. Using mssql-cli, I get this

master>SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
...... 
-[ RECORD 1 ]-------------------------
assembly_id      | 1
(No column name) | microsoft.sqlserver.types.dll
file_id          | 1
content          | 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000D00000000E1FBA0E00B409CD21B........ and on and on
(1 row affected)
  • The 'content' field is contains the 'binary' representation of their assemblies files. – Md Haidar Ali Khan Dec 17 '17 at 8:43
  • 1
    Evan: is 0x4D5A90x07585 exact output or redacted / edited? That is not valid binary. Valid binary data starts with 0x to indicate that what follows is binary, but then you can't ever get x again in the binary string itself since it is hex notation, which only uses 0 - 9 and A - F. Also, have you tried NVARCHAR(MAX) and XML types? I don't think you would get much farther with those ;-) What are you trying to accomplish here? – Solomon Rutzky Dec 17 '17 at 15:44
  • I agree, that's the question. I have 0 idea of what sqlcmd.exe is doing there, but that's what it is outputting. At least i know the problem now is the client. – Evan Carroll Dec 17 '17 at 17:22
  • Evan: this question is becoming very unclear. Is it about sqlcmd or mssql-cli? If it's about sqlcmd, then I assume the issue is only on Linux since I cannot reproduce that output via SQLCMD on Windows. And are you wondering why you don't get the entire binary string, OR are you wondering why it is showing specifically invalid binary? The output you are showing should not be described as truncated since it isn't valid to begin with. It can only be truncated if it is a portion of a valid thing, while anything invalid is just that: invalid. And if it's invalid, then it's a bug. – Solomon Rutzky Dec 17 '17 at 20:28
  • It's about sqlcmd I was wondering why it was showing me that 0x4D5A90x07585 for the content of the assembly. The answer is because it truncate it. You can't control or disable that truncation from what I can see. It's showing a invalid binary because it's doing some sort of truncation, what exactly it's doing and why or what is controlling it (flags), I have no idea. I would like to be able to see the contents of the column -- whatever they are. mssql-cli is currently my workaround. – Evan Carroll Dec 17 '17 at 21:01
2

Why does sqlcmd truncate varbinary(max)?

Because it's it might be GBs of data, so the query tools like SQLCMD and SSMS truncate the results for display.

You can dump to a file with bcp.exe, setting the "prefix-length" to 0.

bcp "select content from sys.assembly_files where name = 'microsoft.sqlserver.types.dll'" queryout microsoft.sqlserver.types.dll -T

Enter the file storage type of field content [varbinary(max)]:
Enter prefix-length of field content [8]: 0
Enter length of field content [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (16.13 rows per sec.)
2

What you are seeing is not truncation; it must be a bug. Neither 0x4D5A90x07585 nor 0x4D5A90x075850x167D00x043CB are valid binary values. The 0x should only be present once (at most), on the far left, and indicates that the value that follows is a binary value. Having that show up once or twice more is just not possible for a binary value since there can't be an "x" in hex / base 16 notation. Truncation would be the first N bytes of the value, and what you are seeing is not. If you execute the following:

SELECT CHARINDEX(N'07585', CONVERT(NVARCHAR(MAX), [content], 1))
FROM sys.assembly_files;

you should get back a value of 8003, indicating that the second set of 5 hex digits in your output is not the second set of 5 digits in that Assembly. And, it is also odd that each "fragment" (starting with 0x) is onyl 5 hex digits, when an actual byte requires 2 hex digits, so they should always be in sets of 2.

SQLCMD (on Windows at least) seems to have a limit of 524,287 bytes, which is more than enough to output the entire contents of the 392,376 byte assembly that you are working with (i.e. microsoft.sqlserver.types.dll).

With SQLCMD, you can use the -y switch to set the max size of variable length fields, which the [content] field is. The max you can set for -y is 8000, but if you use a value of 0, then it will go up to the 524,287 bytes.

Try the following to see what you get:

sqlcmd -S server auth_stuff -y 0 -Q "SELECT [content] FROM sys.assembly_files;" -o .\binary_truncation.txt

That query assumes that you have no custom assemblies loaded. If you do, then you can narrow down a particular one by adding the following to the end of that query:

WHERE assembly_id = 1 AND [file_id] = 1

The [file_id] = 1 row for each Assembly is the Assembly itself. Additional files, if there are any, will start at [file_id] = 2.

When I ran that command line on Windows, I got a file that was 784,777 bytes. Take off 25 bytes for a few newlines, the "(1 rows affected)" line, and the "0x" at the beginning, leaving you with 784,752 bytes. Each byte of binary takes up 2 single-byte characters, 00 - FF, so dividing that file size (minus the 25 bytes) by 2 gives us 392,376 bytes. Executing the following:

SELECT LEN([content]) FROM sys.assembly_files;

returns: 392,376. So, we extracted the entire Assembly.

Now, if there really is a bug with just VARBINARY output, we can test that by first converting it into NVARCHAR data by running the following:

sqlcmd -S server auth_stuff -y 0 -Q "SELECT CONVERT(NVARCHAR(MAX), [content], 1) FROM sys.assembly_files;" -o .\binary_truncation.txt

For me, that returns the same 784,777 byte file.

0

To Getting Information About Assemblies and of course their binary representation.Information about assemblies and their files are stored in two system table.

  1. sys.assemblies

  2. sys.assembly_files

sys.assemblies

In the sys.assemblies table information is stored about the assemblies itself, like name, id, permission set, timestamp of creation or latest modification.As a single assembly, can be represented by multiple physical files.

sys.assembly_files

A second system table sys.assembly_files exists.This table contains information about individual assembly files and also contains their content (binary representation) in a varbinary(max) field.

As per your query the output enter image description here

To get file names and their binary content for particular assembly we can use the below T-SQL code:

SELECT 
 af.name,
 af.content 
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id 
WHERE 
 a.name = 'Microsoft.SqlServer.Types'
;
Go

After running the TSQL query sys.assemblies along with inner join with sys.assembly_files. you shall get the same result of content field. whatever binary value you have got after execution with your query. See the screen shot as mention below

enter image description here

For further your ref Here

  • You're missing the question here: how is "0x4D5A90x07585" a binary representation of all their GIS classes, methods, and types in 5 bytes? – Evan Carroll Dec 17 '17 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.