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.