SQL Server CLR IO Utility

Warning: the use of TRUSTWORTHY described in this article can be a potential security risk so you might want to consider alternative options.

Introduction

Using CLR (.NET Framework) code in SQL Server is a very powerful way of extending the built-in features and functionality. The .NET framework provides access to a library of useful code, allowing you to accomplish useful tasks with a minimal amount of code. The SQL Server IO project does just that; providing access to some useful IO utilities from within SQL Server by leveraging the power of the .NET Framework.

Installation Instructions

You need to ensure that CLR is enabled for the server and the trustworthy database setting is turned on. The trustworthy database setting is required to allow CLR code to have external access.

To enable CLR, run the following code:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

sp_configure 'clr enabled', 1

RECONFIGURE

To enable the trustworthy setting for the database, run the following code:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

1	ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

Warning: the use of TRUSTWORTHY can be a potential security risk.

Next, download the “T-SQL Code” and run it in SQL Server Management Studio to install the CLR functions and stored procedures in the current database.

Note: Consider the security implecations before running any of the code in this article.

Code Download

T-SQL Code

Visual Studio Source Code

CLR Functions

SQLIO_fnGetDrives

Returns information about the drives installed on your computer/server. SELECT * FROM dbo.SQLIO_fnGetDrives()

SQLIO_fnGetFiles

Returns a list of files in the specified folder. The function takes 3 parameters:

@path – The folder from which to start the search
@searchPattern – The search string to match against the names of files in path
@includeSubfolders – Specifies whether the search operation should include all subdirectories or only the current directory.

SELECT * FROM dbo.SQLIO_fnGetFiles('C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\LOG','',0)

1	SELECT * FROM dbo.SQLIO_fnGetFiles('C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\LOG','',0)

SQLIO_fnGetFolders

Returns a list of subfolders within the specified folder. The function takes 3 parameters:

SELECT * FROM dbo.SQLIO_fnGetFolders('C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL','',0)

1	SELECT * FROM dbo.SQLIO_fnGetFolders('C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL','',0)

SQLIO_fnFileExists

Takes a file path as a parameter and returns the bit value “1” if the file exists.

SELECT dbo.SQLIO_fnFileExists('C:\test.txt')

1	SELECT dbo.SQLIO_fnFileExists('C:\test.txt')

SQLIO_fnFolderExists

Takes a folder path as a parameter and returns the bit value “1” if the folder exists.

SELECT dbo.SQLIO_fnFileExists('C:\MyFolder')

1	SELECT dbo.SQLIO_fnFileExists('C:\MyFolder')

SQLIO_fnReadFile

Takes the path of a file as a parameter and returns the file data in varbinary format.

SELECT dbo.SQLIO_fnReadFile('C:\Test.txt')

1	SELECT dbo.SQLIO_fnReadFile('C:\Test.txt')

SQLIO_fnReadTextFile

Takes the path of a file as an parameter and returns the file data in nvarchar format.

SELECT dbo.SQLIO_fnReadTextFile('C:\test.txt')

1	SELECT dbo.SQLIO_fnReadTextFile('C:\test.txt')

CLR Stored Procedures

SQLIO_DeleteFile

Takes the path of a file as an parameter and deletes the specified file.

EXEC dbo.SQLIO_DeleteFile 'C:\test.txt'

1	EXEC dbo.SQLIO_DeleteFile 'C:\test.txt'

SQLIO_DeleteFolder

Takes the path of a folder as a parameter and deletes the specified folder.

EXEC dbo.SQLIO_DeleteFolder 'C:\MyFolder'

1	EXEC dbo.SQLIO_DeleteFolder 'C:\MyFolder'

SQLIO_DeleteFiles

Takes the path of a folder, a search pattern and an include subfolders parameter, similar to the dbo.SQLIO_fnGetFiles function and deletes all the files matched from the search.

EXEC dbo.SQLIO_DeleteFiles 'C:\Temp','*.tmp',1

1	EXEC dbo.SQLIO_DeleteFiles 'C:\Temp','*.tmp',1

SQLIO_DeleteFilesOlderThan

This stored procedure is the same as the dbo.SQLIO_DeleteFiles procedure, but with an additional maxAge date parameter. Only files older than the maxAge date specified will be deleted.

EXEC dbo.SQLIO_DeleteFilesOlderThan 'C:\Temp','*.tmp',1,'20090101'

1	EXEC dbo.SQLIO_DeleteFilesOlderThan 'C:\Temp','*.tmp',1,'20090101'

SQLIO_CreateFolder

Takes a path as a parameter and creates the new folder on the file system.

EXEC dbo.SQLIO_CreateFolder 'C:\Temp\New Folder'

1	EXEC dbo.SQLIO_CreateFolder 'C:\Temp\New Folder'

SQLIO_CreateFile

Creates a new file, by taking a file path and varbinary data value.

EXEC dbo.SQLIO_CreateFile 'C:\Temp\NewFile.txt',0x68007400740070003A002F002F007700770077002E00770069007300650073006F00660074002E0063006F002E0075006B00

1	EXEC dbo.SQLIO_CreateFile 'C:\Temp\NewFile.txt',0x68007400740070003A002F002F007700770077002E00770069007300650073006F00660074002E0063006F002E0075006B00

SQLIO_CreateTextFile

Creates a new text file, by specifying a file path and string value.

EXEC dbo.SQLIO_CreateTextFile 'C:\Temp\NewFile.txt','Text Here'

1	EXEC dbo.SQLIO_CreateTextFile 'C:\Temp\NewFile.txt','Text Here'

SQLIO_AppendTextFile

Appends text to the end of a file, by specifying a file path and text to append.

EXEC dbo.SQLIO_AppendTextFile 'C:\Temp\myfile.txt','My Appended Text'

1	EXEC dbo.SQLIO_AppendTextFile 'C:\Temp\myfile.txt','My Appended Text'

SQLIO_MoveFile

Takes source and destination parameters, moving a file from one location to the other.

EXEC dbo.SQLIO_MoveFile 'C:\Old Folder\MyFile.txt','C:\New Folder\MyFile.txt'

1	EXEC dbo.SQLIO_MoveFile 'C:\Old Folder\MyFile.txt','C:\New Folder\MyFile.txt'

SQLIO_CopyFile

This stored procedure takes source and destination parameters, copying a file from one location to the other.

EXEC dbo.SQLIO_CopyFile 'C:\temp\MyFile.txt','C:\temp\Copy Of MyFile.txt'

1	EXEC dbo.SQLIO_CopyFile 'C:\temp\MyFile.txt','C:\temp\Copy Of MyFile.txt'

SQLIO_MoveFolder

Takes source and destination parameters, copying a folder from one location to the other.

EXEC dbo.SQLIO_MoveFolder 'C:\Old Folder\MyFolder','C:\New Folder\MyFolder

1	EXEC dbo.SQLIO_MoveFolder 'C:\Old Folder\MyFolder','C:\New Folder\MyFolder