## Ever needed to write a text file from within SQL Server?

Well, there are a three of ways of doing this:  Extended Stored Procedures, OLE Automation and SQLCLR.

While it is out of the scope of this post, Extended Stored Procedures and OLE Automation are things that have been retained in the SQL Server product in order to maintain a measure of backwards compatibility.  Microsoft often recommends that they be avoided, and instead, SQLCLR be used.

What this means is:  Just about anything you can do with .NET programming languages can be done with SQL Server.

Before deploying a SQLCLR function, you have to enable CLR (Common Language Runtime) features within SQL Server.  As a sysadmin, do the following:

IF NOT EXISTS (SELECT * FROM [master].[sys].[configurations] WHERE [name] = 'clr enabled' AND [value_in_use] = 1)
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
END


Next, you need to set the database you are going to deploy your SQLCLR function marked as TRUSTWORTHY.  Assuming your database is called [UTILITIES], do this:

IF NOT EXISTS (SELECT * FROM [master].[sys].[databases] WHERE [name] = 'UTILITIES' AND [is_trustworthy_on] = 1)
BEGIN
ALTER DATABASE [UTILITIES] SET TRUSTWORTHY ON
END


Now, you need a SQLCLR stored procedure to write files to a text file.  Within Visual Studio, create a new “Visual Basic SQL CLR Database Project”.  Have it communicate with a database reference (use a test database first, I’ll show you how to create a deployment script later).

You’ll also need to go into  My Project/Compile/Advanced Compile Options and set the Target Framework for .NET Framework 2.0.  This will allow your function to run under SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.  It should run under Denali, but I haven’t tested that yet, so I don’t want to commit to it.

Add a new stored procedure called usp_WriteToTextFile.

You’ll see this:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
_
Public Shared Sub usp_WriteToTextFile ()
End Sub
End Class


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
_
Public Shared Sub usp_WriteToTextFile (ByVal FileName As SqlString, ByVal Contents As SqlString, ByRef ErrorMessage As SqlString) As SqlInt32
Dim intReturn As SqlInt32
Dim file As System.IO.StreamWriter

intReturn = 1
ErrorMessage = SqlString.Null

Try
file = System.IO.File.AppendText(FileName.ToString)
file.Write(Contents)
file.Close()
Catch ex As Exception
ErrorMessage = "An Exception of Type [" & ex.GetType.ToString & "] occurred with the message [" & ex.Message & "]"
intReturn = 0
End Try

Return intReturn
End Sub
End Class


Now it’s just a matter of compiling, deploying and testing.

NOTE:  The SQL Server service account must have the required NTFS file permissions (and if writing to a CIFS share, the required share permissions) to write to the text file.  If the file doesn’t exist, it is created. If the file exists, it is appended to.

Now for that deployment script.

When you compile and deploy, look at the Output windows, and select Build from the “Show output from” dropdown.

You’ll see something like this:

  Deployment script generated to:
D:\Users\Marc\Documents\Visual Studio 2010\Projects\FileFunctions\FileFunctions\bin\Debug\FileFunctions.sql

Visual Studio will create a .SQL file that gives you a lot of insight into how to create a deployment script for SQLCLR functions.  It looks to me like this is meant to be run from a SQLCMD.EXE-type utility, but I’ll generally clean it up a bit, then use it to deploy to production servers (after testing).

You’ll see within this file a CREATE ASSEMBLY statement, an ALTER ASSEMBLY statement and a CREATE PROCEDURE statement.  These are really all that you need to deploy to multiple servers.  The calls to sp_addextendedproperty aid in using the Visual Studio debugger, but I don’t think they are strictly necessary.

After that, you use the stored procedure like this:

DECLARE @filename [nvarchar](260)
DECLARE @text_to_write [nvarchar](max)
DECLARE @error_message [nvarchar](max)
DECLARE @return [int]
SET @filename = 'C:\foo\bar.txt'
SET @text_to_write = 'this is a test'
EXEC @return = usp_WriteToTextFile @FileName = @filename, @Contents = @text_to_write, @ErrorMessage = @error_message OUTPUT


NOTE: You’ll have to GRANT EXECUTE rights to logins/users/roles to the stored procedure, just like any other stored proc, or use EXECUTE AS within other stored procs in order to execute the stored procedure as a non-sysadmin user.