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:
|
1 2 3 4 |
sp_configure 'clr enabled', 1 GO RECONFIGURE GO |
To enable the trustworthy setting for the database, run the following code:
|
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
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.
|
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:
@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.
|
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.
|
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.
|
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.
|
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.
|
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.
|
1 |
EXEC dbo.SQLIO_DeleteFile 'C:\test.txt' |
SQLIO_DeleteFolder
Takes the path of a folder as a parameter and deletes the specified folder.
|
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.
|
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.
|
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.
|
1 |
EXEC dbo.SQLIO_CreateFolder 'C:\Temp\New Folder' |
SQLIO_CreateFile
Creates a new file, by taking a file path and varbinary data value.
|
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.
|
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.
|
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.
|
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.
|
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.
|
1 |
EXEC dbo.SQLIO_MoveFolder 'C:\Old Folder\MyFolder','C:\New Folder\MyFolder |