Posted on Leave a comment

Compress/Decompress Using Linked Server

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)