Why does sqlcmd truncate varbinary(max)? Inside of the sys.assembly_files catalog, I can see a field named content

1&gt; SELECT assembly_id, LEFT(name, 50), file_id, content FROM sys.assembly_files;
2&gt; 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&gt;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)
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.)