By David Wiseman (Administrator)published 12 Jul 2009
My Rating:
Vote
Rating:
Not Rated
Views:14321

SQL Server CLR IO Utility

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

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

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

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

Save T-SQL CodeT-SQL Code

Save Visual Studio Source CodeVisual Studio Source Code

CLR Functions

SQLIO_fnGetDrives

Returns information about the drives installed on your computer/server. 

SELECT * FROM dbo.SQLIO_fnGetDrives()

SQLIO_fnGetDrives function output

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)

 SQLIO_fnGetFiles function output

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.

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

SQLIO_fnGetFolders function output

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')

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')

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')

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')

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'

SQLIO_DeleteFolder

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

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

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'

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'

SQLIO_CreateFile

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

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','http://www.wisesoft.co.uk',

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'

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'

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'

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
 




  Post Comment
Order By:  
User Comments
      
PedroDCardoso
Posted On: 6/7/2013 3:57:55 PM

I successfully installed and tested your code, but I have one question I am hoping you can help with. When one of the SQLIO_* routines are called, what user/security context does it run in? Specifically when running the dbo.SQLIO_fnGetFiles command, I am trying to retrieve a directory listing from a remote server and am getting a security exception. I have no problem when accessing a local folder (on the SQL box), as you would expect.

I checked the SQL Server Agent account and it DOES have sufficient privileges to the folder in question, so I am confused about how to determine what user it is using. Is their another proxy account I need to set? Please advise.

MobDBA
Posted On: 8/9/2016 7:14:17 AM

Hi, When I tested i'm getting below error. Please resolve this issue. Attaching the error along with this message.

Failed to initialize sqlcmd library with error number -2147024809. [SQLSTATE 42000] (Error 22050). The step failed.