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

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.

lcklck16
Posted On: 8/18/2017 4:29:04 AM

20170819 junda
michael kors canada
michael kors outlet online
ugg boots clearance
michael kors outlet
swarovski crystal
canada goose outlet
ray ban sunglasses
longchamp outlet online
coach outlet online
christian louboutin outlet
cheap ray bans
michael kors outlet online
ray ban sunglasses outlet
coach outlet online
nike air huarache
kobe 9 elite
coach outlet
coach outlet online
michael kors outlet store
roshe run
polo ralph lauren
tory burch outlet stores
michael kors outlet
ralph lauren polo
michael kors outlet online
cheap oakley sunglasses
fred perry polo
coach outlet online
tods outlet online
christian louboutin sale
ralph lauren outlet
ugg boots clearance
cheap oakley sunglasses
jerseys wholesale
ugg boots outlet
prada outlet online
nfl jersey
cheap ray ban sunglasses
polo pas cher
uggs outlet
polo outlet online
coach outlet online
coach outlet online
michael kors outlet online
polo ralph lauren
nba jerseys
nike shoes
cheap jordans
oakley sunglasses wholesale
snapbacks wholesale
air max 90
michael kors handbags clearance
max 1
coach outlet
snapbacks wholesale
mulberry handbags
mulberry bags
oakley sunglasses sale
burberry outlet sale
michael kors outlet online
polo outlet
ugg outlet,ugg outlet store,uggs outlet,ugg outlet online clearance,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale
nhl jerseys
canada goose outlet store
nike trainers uk
mbt shoes
christian louboutin outlet
Basketball Shoes
cheap ray ban sunglasses
fitflops uk
coach outlet online coach factory outlet
asics,asics israel,asics shoes,asics running shoes,asics israel,asics gel,asics running,asics gel nimbus,asics gel kayano
canada goose jackets on sale
tory burch outlet online
ferragamo shoes sale
ferragamo outlet
links of london jewellery
oakley sunglasses sale
pandora outlet
cheap ray ban sunglasses
ed hardy outlet
oakley sunglasses wholesale
coach outlet
true religion outlet uk
canada goose outlet
michael kors handbags
coach factory outlet
longchamp handbags
polo ralph lauren
canada goose jackets men
michael kors uk
cazal outlet
adidas nmd runner
christian louboutin
ugg boots clearance,ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet
uggs outlet
mulberry handbags
coach factory outlet
ugg outlet store
oakley sunglasses wholesale
oakley sunglasses wholesale
ralph lauren?uk
true religion outlet
true religion outlet
mont blanc pens
michael kors outlet clearance
ray ban sunglasses
nike shoes
ugg clearance
michael kors handbags
uggs outlet
cheap jordans free shipping
fitflops uk
coach outlet store online
canada goose outlet
ferragamo shoes
coach outlet
coach outlet
air max trainers
michael kors outlet stores
cheap oakley sunglasses
canada goose outlet online
canada goose outlet store
michael kors outlet online
true religion outlet store
coach outlet
ugg outlet online
michael kors outlet clearance
adidas shoes
uggs outlet
michael kors outlet online
kobe shoes
longchamp handbags
air max 90
chrome hearts outlet
ugg outlet stores
cheap nfl jerseys
coach outlet online
michael kors outlet clearance
michael kors handbags outlet
michael kors uk
birkenstock shoes
ralph lauren uk
christian louboutin shoes
cheap oakley sunglasses
cheap ray ban sunglasses
adidas nmd
air force 1 shoes
swarovski jewellery
tory burch outlet online
coach outlet
cheap jordan shoes
longchamp handbags
oakley sunglasses wholesale
cheap oakley sunglasses
polo outlet stores
adidas shoes
michael kors outlet
christian louboutin
christian louboutin uk
ugg clearance
ralph lauren uk
coach outlet online
canada goose outlet
oakley sunglasses
coach outlet store online
pandora jewelry
chrome hearts online store
swarovski outlet
burberry outlet store
ralph?lauren
mbt shoes outlet
true religion canada
true religion jeans sale
vans outlet
coach outlet store online
adidas shoes
canada goose jackets for women
kate spade handbags
coach outlet online coach factory outlet
christian louboutin sale
foamposite shoes
hermes belts
cheap ray bans
coach outlet online
uggs outlet
ugg boots australia
polo outlet
ugg boots outlet
nfl jersey wholesale
michael kors outlet online
oakley sunglasses wholesale
cheap michael kors handbags
mlb jerseys
prada outlet
cheap jordans
fitflops
nike free running
mlb jerseys
michael kors outlet clearance
pandora jewellery
nike outlet store online
air max 90
herve leger dresses
salomon outlet
michael kors outlet clearance
oakley sunglasses
cheap soccer jerseys
ugg outlet
ralph lauren polo
nike shoes outlet
nike air huarache
adidas uk store
christian louboutin shoes
longchamp outlet
cheap uggs outlet,cheap uggs for sale,cheap ugg boots,cheap uggs for kids,cheap uggs for women,cheap uggs bailey bow,cheap uggs china,cheap uggs for men,cheap uggs for girls,cheap uggs boots for women
ugg boots,ugg boots outlet,cheap uggs boots,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots clearance sale women,ugg boots clearance outlet,ugg boots for men,ugg boots for kids
ugg outlet store
coach outlet store online
uggs outlet
lebron shoes
ugg outlet
canada goose jackets
pandora outlet
thomas sabo charms
mont blanc outlet
adidas outlet
ralph lauren uk
pandora outlet
polo outlet store
michael kors outlet clearance
coach outlet online
coach outlet online
max 90
coach outlet clearance
nike air max 1
michael kors outlet
ralph lauren outlet
oakley sunglasses wholesale
puma shoes
cheap uggs
mac tools
cheap jerseys wholesale
kate spade uk
canada goose jackets outlet
mulberry outlet,mulberry handbags outlet
ralph lauren
true religion jeans
air max 90
michael kors outlet clearance
ray ban sunglasses uk
ysl outlet online
polo outlet stores
ralph lauren outlet
cheap football shirts
nike outlet store online
ugg boots clearance
michael kors outlet clearance
michael kors outlet clearance
longchamp handbags
christian louboutin shoes
cheap ray ban sunglasses
ray ban sunglasses uk
coach outlet store online
ralph lauren
michael kors outlet clearance
ray-ban sunglasses
longchamp outlet
ralph lauren
coach outlet
nike store uk
ugg outlet,ugg outlet sale,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store
canada goose coats
coach outlet
coach outlet online
coach outlet
michael kors outlet online
christian louboutin shoes
tory burch sandals
michael kors outlet online
coach factory outlet
kd shoes
ugg outlet clearance,ugg outlet online clearance,ugg outlet,uggs outlet,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale
oakley sunglasses wholesale
michael kors outlet
nhl jerseys wholesale
coach factory outlet
michael kors outlet
ray ban sunglasses
hermes outlet store
mulberry bags
new balance outlet
coach outlet clearance
fitflops sale clearance
jordan shoes
coach factory outlet
jordan 4
true religion jeans outlet
canada goose uk
ray ban sunglasses wholesale
coach outlet
cheap ray ban sunglasses
michael kors outlet online
michael kors outlet clearance
soccer jerseys wholesale
reebok shoes
uggs outlet
ferragamo shoes
nike air max 90
michael kors handbags outlet
air max shoes
coach outlet
coach outlet store online
prada outlet
cheap jordans for sale
michael kors bags
ugg boots clearance
polo outlet
coach outlet online
michael kors handbags
nike roshe run
polo ralph lauren
ferragamo outlet
polo ralph lauren
cheap oakley sunglasses
mulberry handbags
ugg boots outlet
michael kors outlet store
cheap oakley sunglasses
coach outlet store online
reebok shoes
bottega veneta outlet online
ugg outlet clearance
polo ralph lauren outlet
coach outlet
michael kors outlet clearance
cheap oakley sunglasses
michael kors outlet online
christian louboutin shoes
coach factory outlet
coach outlet
hermes outlet
ugg outlet online
ralph lauren outlet
ralph?lauren?polo?shirts
canada goose jackets
mac outlet
ralph lauren shirts
burberry canada
mcm backpacks
prada sunglasses for women
ugg boots for women,ugg boots,ugg boots outlet,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet
ray ban wayfarer
Nike Free shoes
michael kors handbags
true religion outlet
yeezy boost 350
oakley sunglasses wholesale
nfl jerseys
nfl jersey wholesale
nike shoes uk
ugg boots clearance
canada goose jackets
ralph lauren outlet stores
cheap nfl jerseys
yeezy boost
nike uk store
coach outlet clearance
nfl jerseys
oakley sunglasses wholesale
ralph lauren outlet
true religion jeans
marc jacobs sale
fitflops sale clearance
longchamp bags
michael kors outlet
coach outlet
oakley sunglasses sale
lacoste outlet
true religion outlet
polo ralph lauren outlet
fitflops
michael kors outlet clearance
michael kors handbags
tory burch handbags
nike air max 90
hermes birkin bag
coach outlet online
michael kors outlet online
true religion uk
adidas shoes
adidas outlet
tory burch outlet
cheap jordan shoes
ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet
cheap oakley sunglasses
kate spade
canada goose outlet
lebron shoes
mulberry outlet
pandora jewelery
true religion jeans
pandora charms
burberry outlet store
oakley sunglasses
cheap ray ban sunglasses
mulberry uk
cheap oakley sunglasses
cheap oakley sunglasses
kate spade outlet online
nike trainers
cheap oakley sunglasses
kate spade handbags
michael kors outlet
polo outlet
polo ralph lauren outlet
birkenstock shoes
nike shoes for women
ralph lauren polo
coach factory outlet
canada goose
coach outlet
soccer cleats
coach outlet store online
oakley sunglasses
true religion jeans sale
cheap oakley sunglasses
michael kors outlet online
coach outlet clearance
supra shoes sale
nba jerseys wholesale
swarovski uk
mulberry outlet
coach handbags outlet
swarovski jewelry
valentino shoes
mulberry handbags sale
michael kors outlet online
christian louboutin outlet
canada goose jackets
ugg outlet
michael kors outlet clearance
christian louboutin shoes
pandora bracelet
los angeles lakers
swarovski crystal
tory burch outlet online
ray-ban sunglasses
oakley sunglasses wholesale
lacoste polo
michael kors uk
polo ralph lauren outlet
adidas wings shoes
coach factory outlet
coach outlet online
ugg outlet,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale
polo ralph lauren outlet
coach outlet online
cheap jerseys wholesale
coach outlet online
coach factory outlet
coach outlet online
cheap nba jerseys
nike outle
giuseppe zanotti shoes
michael kors outlet
nike outlet online
ralph lauren outlet
christian louboutin sale
nike roshe run
coach factory outlet
ugg boots clearance
tory burch outlet online
nike blazer pas cher
ralph lauren outlet
kate spade uk
canada goose jackets
snapback hats
canada goose outlet
swarovski jewelry
ecco outlet
polo ralph lauren
oakley sunglasses sale
true religion jeans
cheap jordan shoes
michael kors outlet clearance
coach outlet online
fitflops sale clearance
uggs outlet
cheap oakley sunglasses
cheap ray ban sunglasses
lacoste pas cher
basketball shoes,basketball sneakers,lebron james shoes,sports shoes,kobe bryant shoes,kobe sneakers,nike basketball shoes,running shoes,mens sport shoes,nike shoes
canada goose outlet
cheap ray ban sunglasses
coach outlet store online
ugg boots outlet
michael kors outlet online
cheap oakley sunglasses
polo ralph lauren
ferragamo outlet
polo ralph lauren
michael kors wallets for women
nike outlet
ray ban sunglasses
canada goose jackets
lacoste shirts
cheap oakley sunglasses
cheap mlb jerseys
coach outlet
coach outlet store online
oakley sunglasses wholesale
cheap jordan shoes
michael kors outlet online
ugg outlet,ugg outlet clearance,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet online store,ugg outlet sale,ugg outlet coupons,ugg outlet boots
nike air max
true religion outlet
polo ralph lauren
oakley sunglasses wholesale
camisetas futbol baratas
mbt shoes outlet
michael kors outlet clearance
ferragamo shoes
ray bans
michael kors outlet clearance
coach factory outlet
coach outlet store
michael kors outlet clearance
ralph lauren outlet
coach outlet
air max 2015
fitflops sale clearance
ugg outlet store
pandora charms sale clearance
ralph lauren
michael kors uk
nike trainers
fitflops
cleveland cavaliers jerseys
coach outlet
cheap oakley sunglasses
michael kors outlet clearance
cheap nike shoes
cheap ray ban sunglasses
kobe bryants shoes
converse shoes sale
coach factory outlet
cheap oakley sunglasses
coach outlet online
michael kors factory outlet
christian louboutin sale
ray ban sunglasses outlet
pandora outlet
ralph lauren outlet