Export varbinary(max) data from SQL Server to file system with CLR UDF

First, we can create a CLR user-defined function from Visual  Studio and deploy the function to SQL Server.
Here is a link to sample code snippet:


In SQL Server we can write a simple recursive CTE block to retrieve all files to be exported. The finl code is to use the CLR-UDF in the T_SQL code.

;with mycte as
select id, parentid, 0 as lvl, path, isfolder,[name], [externalid]
from samples
where id = 1
Union all
Select s.id, s.parentid, m.lvl+1 as lvl , s.path, s.isfolder, s.[name],s.[externalid]
from samples s join mycte m
on s.parentid=m.id


select dbo.WriteToFile(n.storagevalue ,N’c:\temp\myFolder’ +path, 0)
from mycte m JOIN [dbo].[dataStores] n on m.externalid=n.externalid
This method is super fast compared to using cursor to execute bcp command line by line.

Here is a nice post about CLR from DBA’s view.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s