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 ()
          ' Add your code here</pre>
 End Sub
End Class

Now just replace ‘ Add your code here’ with:

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.

About these ads

, , , ,

  1. #1 by Tom Mundy on December 10, 2011 - 12:20 am

    I must say this is a great article i enjoyed reading it keep the good work :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: