Ran into a situation with a column containing zipped varbinary data in a SQL 2012 instance needed to be unzipped. We wanted a method to decompress the data within TSQL without using CLR or external program. Since we had a SQL 2016 instance available we looked for a way to use the SQL 2016 decompress() function over a linked server. Inelegant as it may be, here is a brief example of syntax involved to get that to work.
Example of using compress over a linked server to compress a varchar string.
SELECT *
FROM openquery([APPS-INST03],'select compress(''test string'')');
Example of retrieving a remote column and calling compress
SELECT *
INTO mytable
FROM openquery([MYLINKEDINST],'select compress(name) "name_c" from [remotedb].[dbo].[remotetable]');
Example of passing a binary string to remote decompress function.
select * from openquery([apps-inst03],'select cast(decompress(0x1F8B08000000000004002B492D2E51282E29CACC4B0700451547130B000000) as varchar(max))');
Example of passing a compressed column value to the remote decompress via linked server using openquery()
DECLARE @tsql nvarchar(max) , @p1 nvarchar(100) = 'SELECT * FROM openquery([MYLINKEDINST],''SELECT cast(decompress(0x' , @p2 nvarchar(100) = ') as varchar(max))'');' SELECT @tsql = @p1 + CONVERT(VARCHAR(MAX),name_c,2) + @p2 FROM mytable PRINT @tsql EXEC(@tsql)