This sample code copies files from an Azure file share to an Azure Blob container via SQL CLR
On a native SQL Server, you can reference assemblies locally on the file system but on an SQL Server Managed Instance you can not reference assemblies locally so the CLR SQL install script contains assemblies to be imported via a binary string to allow the CLR functionality in SQL Managed instance.
The code sample has also been included for reference.
-
Open and run the SQL script named 'ADD_SYS_TRUSTED_ASSEMBLIES.sql' from the repository. This script adds the assemblies to the system table 'sys.trusted_assemblies', by listing the assemblies as trusted they can be loaded into SQL server when the default setting of
clr strict security
is on and without having to set the entire database asTRUSTWORTY
for security purposes. -
Open the SQL script named 'CLR_INSTALL.sql' update the using statement in the script to reference the database you wish to use with the CLR function and run the script, this adds the assemblies needed to execute the CLR function.
The installation script creates a SQL function named MoveFileToBlob which executes the CLR code taking the following parameters.
- @storageAccountConnectionString = the connection string for the Azure storage account containing the Azure File share and Blob container.
- @blobContainerName = The name of the Blob container which the files will be copied to.
- @fileUrl the URL of the file copied from Azure File Share.
Sample execution
SELECT dbo.MoveFileToBlob('ConnectionString','BlobContainerName','FileUrl')
Currently for testing simplicity the select statement returns a string of 'TRUE' or the Exception Text if an error occurs.
DECLARE @resultvalue nvarchar(max) = (SELECT dbo.MoveFileToBlob('ConnectionString','BlobContainerName','FileUrl')))'
IF @resultvalue = 'TRUE'
PRINT 'Transfer Was Successful'
ELSE
PRINT 'Transfer Failed'
Managing CLR Integration Assemblies
SQL Server CLR Function on Azure SQL Server Managed Instance