Click here to Skip to main content
15,608,157 members
Articles / Database Development / SQL Server
Article
Posted 2 Oct 2016

Tagged as

Stats

22.3K views
547 downloads
19 bookmarked

Writing into a File from Database

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
4 Nov 2016CPOL8 min read
Few CLR procedure concepts to write into a file from SQL Server database
Writing into a file from an SQL Server database can be done in several ways. Perhaps an easier way than using FileSystemObject via OLE automation is to use a small CLR procedure or procedures.

Introduction

Sometimes, it’s necessary to write into a file from an SQL Server database. This can be done in several ways, for example using FileSystemObject via OLE automation. Perhaps an easier way is to use a small CLR procedure or procedures.

This article covers the following topics:

As a concrete example where writing to a file is used, have a look at Restricting logon to SQL Server.

Preparing the Database

First of all, let’s make a fresh database where to test and also make some preliminary configurations. By default, CLR integration is disabled in SQL Server so it has to be enabled. Creating the database could look like this:

SQL
-----------------------------------------------
-- Create the test database 
-----------------------------------------------
USE master;
GO

CREATE DATABASE LoggerTest;
GO

USE LoggerTest;
GO

-- Enable CLR
sp_configure 'show advanced options', 1; 
GO  

RECONFIGURE; 
GO  

sp_configure 'clr enabled', 1; 
GO  

RECONFIGURE; 
GO

Create the Procedure to Write a Single Line

Now the next part is to create the C# code for the CLR procedure. The code is quite simple.

C#
namespace Logger {
   public static class ContinuousLogger {
      /// <summary>
      /// Procedure to write a row of data into a specified file
      /// </summary>
      /// <param name="fileName">Full name of file to write into</param>
      /// <param name="line">Line to write</param>
      [Microsoft.SqlServer.Server.SqlProcedure]
      public static void WriteLine(string fileName, string line) {
         System.IO.FileStream logStream;
         System.IO.StreamWriter logFile;

         using (logStream = new System.IO.FileStream(fileName, System.IO.FileMode.Append, 
                            System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite)) {
            using (logFile = new System.IO.StreamWriter(logStream)) {
               logFile.WriteLine(line);
               logFile.Flush();
               logFile.Close();
            }
         }
      }
   }
}

The procedure simply appends the desired row into the file using StreamWriter object. Few things to notice:

  • FileMode.Append creates the file if it doesn’t exist.
  • Write permission is required but simultaneous read and write operations are allowed in order to avoid locking situations.
  • The method needs to be static. SQL Server won't instantiate objects.
  • The method is decorated with SqlProcedure attribute in order to be usable by SQL Server.

The next step is to define the procedure in SQL Server. This takes two steps:

  1. Assembly registration
  2. Procedure definition

The assembly is created as follows:

SQL
CREATE ASSEMBLY WriteLogFile  
FROM 'C:\......\WriteLogFile\bin\Debug\WriteLogFile.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

Remember to define correct path pointing to the newly compiled assembly.

When you try to run the statement, you’ll receive an error message like the following:

Msg 10327, Level 14, State 1, Line 51
CREATE ASSEMBLY for assembly 'WriteLogFile' failed because assembly 
'WriteLogFile' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. 
The assembly is authorized when either of the following is true:
the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the
TRUSTWORTHY database property on; or the assembly is signed with a certificate or 
an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

EXTERNAL_ACCESS needs to be defined since we’re going to do file system operations. As the error message states we have two options, either sign the assembly or make the database trustworthy.

For the sake of simplicity, in this example, the database is marked as trustworthy, but in real life it would be far better option to sign the assembly and create a key based on the signature. Marking the whole database as trustworthy means that all CLR procedures inside that database can access external resources. By using a specific key, we can control which assemblies receive the external access privilege thus reducing security risks.

SQL
-- Make the database trustworthy for external access
ALTER DATABASE LoggerTest SET TRUSTWORTHY ON;
GO

Now if we run the assembly creation again, it’ll succeed.

SQL
CREATE ASSEMBLY WriteLogFile  
FROM 'C:\......\WriteLogFile\bin\Debug\WriteLogFile.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

The next operation is to define the procedure as follows:

SQL
CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000))  
AS EXTERNAL NAME WriteLogFile.[Logger.ContinuousLogger].WriteLine;
GO

A nuance worth noticing is how the namespace is defined. SQL Server uses three-part notation in external names:

  • Assembly
  • Class
  • Method

So if the method is wrapped inside a namespace, the namespace and the class name need to be surrounded using brackets: [Logger.ContinuousLogger]

For informational purposes, if you would forget the brackets like this:

SQL
CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000))  
AS EXTERNAL NAME WriteLogFile.Logger.ContinuousLogger.WriteLine;
GO

You’d receive an error:

Msg 102, Level 15, State 1, Procedure WriteLine, Line 2 [Batch Start Line 54]
Incorrect syntax near '.'.

Or if you leave out the namespace like:

SQL
CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000))  
AS EXTERNAL NAME WriteLogFile.ContinuousLogger.WriteLine;
GO

The method cannot be found anymore and you receive:

Msg 6505, Level 16, State 2, Procedure WriteLine, Line 1 [Batch Start Line 54]
Could not find Type 'ContinuousLogger' in assembly 'WriteLogFile'.

Of course, one way to tackle this is to leave out the namespace also from the assembly. In such case, the CREATE PROCEDURE statement above would work just fine.

Time to test the procedure. Let’s write something to a file in C:\TEMP folder:

SQL
DECLARE
 @message varchar(2000)
BEGIN
   SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) + _
   ': Hello from ' + ORIGINAL_LOGIN() ;
   EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line=@message
END;
GO

Running the statement above should first create the file LoggingData.txt and write a piece of line into it, as follows:

2016-10-02T20:43:28.2344396: Hello from MyWorkstation\MyUsername

Truncating the File

Since the WriteLine procedure always adds new lines into the file, in time, it grows too big to handle. Of course the file can be truncated or deleted from the file system but as the idea is to control the file from the database, let’s add some functionality to the assembly.

A simple scenario is to truncate the file by creating a new empty file with the same name. To do this, let’s use a method like the following:

C#
/// <summary>
/// Procedure to truncate the log file
/// </summary>
/// <param name="fileName">Full name of file to truncate</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TruncateFile(string fileName) {
   System.IO.FileStream logStream;

   // Create or truncate the file
   using (logStream = new System.IO.FileStream(fileName,
                                               System.IO.FileMode.Create,
                                               System.IO.FileAccess.Write,
                                               System.IO.FileShare.ReadWrite)) {
   }
}

The method uses FileMode.Create option to create a new empty file regardless of whether a file with the same name already exists.

Now to define this in the database, we need to do a few things. I added the method to the assembly, built it but if I try to create the assembly again in the database, I receive an error.

Msg 6246, Level 16, State 1, Line 49
Assembly "WriteLogFile" already exists in database "LoggerTest".

Okay, so I need to remove the assembly first, but this also results in an error:

SQL
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'WriteLogFile')
DROP ASSEMBLY WriteLogFile
GO

Gives:

Msg 6590, Level 16, State 1, Line 46 
DROP ASSEMBLY failed because 'WriteLogFile' is referenced by object 'WriteLine'.

So every time you make modifications into the assembly and want to load the new version into the database, you need to:

  1. drop all procedures, functions, etc. that are defined from this assembly
  2. drop the old assembly
  3. create the new assembly
  4. create all procedures, functions, etc.

So in our case, the commands would be:

SQL
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'WriteLine')
DROP PROCEDURE WriteLine
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'WriteLogFile')
DROP ASSEMBLY WriteLogFile 
GO

CREATE ASSEMBLY WriteLogFile  
FROM 'C:\......\WriteLogFile\bin\Debug\WriteLogFile.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000))  
AS EXTERNAL NAME WriteLogFile.[Logger.ContinuousLogger].WriteLine;
GO

CREATE PROCEDURE TruncateFile (@fileName nvarchar(2000))  
AS EXTERNAL NAME  WriteLogFile.[Logger.ContinuousLogger].TruncateFile;
GO

Again, remember to change the path to point to the assembly.

Now we can test truncating the file by calling the TruncateFile procedure:

SQL
EXEC TruncateFile @fileName='C:\TEMP\LoggingData.txt'
GO

After this, the file should exist, but be empty.

Shortening the File, Leaving Some Rows Behind

As the idea is to create a log file (of sorts), it may not be a feasible idea to truncate all the rows. Some information may be newly written and needs to be preserved. Because of this, let’s create another kind of method that will leave a desired amount of lines in the file:

C#
/// <summary>
/// Shortens the file but leaves desired number of rows intact
/// </summary>
/// <param name="fileName">Full name of file to shorten</param>
/// <param name="numRowsToLeave">Number of rows to leave</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ShortenFile(string fileName, int numRowsToLeave) {
   string[] lines;

   lines = System.IO.File.ReadAllLines(fileName);
   System.Array.Reverse(lines);
   System.Array.Resize(ref lines, numRowsToLeave);
   System.Array.Reverse(lines);
   System.IO.File.WriteAllLines(fileName, lines);
}

The code is short, read all lines, resize the array and write to a new file with the same name. However, since we need to leave the last N rows intact, the array containing the rows is reversed before resize. Also, after the resize, the array is again reserved to achieve the original order. This way, the oldest rows are removed during the resize operation.

Again, we need to define the procedure to the database. Before adding this procedure, remember to drop all older procedures, drop the assembly, create the assembly and create the old procedures, as explained earlier.

To define this procedure, use:

SQL
CREATE PROCEDURE ShortenFile (@fileName nvarchar(2000), @numRows int)  
AS EXTERNAL NAME  WriteLogFile.[Logger.ContinuousLogger].ShortenFile;
GO

And to test the procedure, let’s have a test case like this:

SQL
EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line='1'
EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line='2'
EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line='3'
EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line='4'
EXEC WriteLine @fileName='C:\TEMP\LoggingData.txt', @line='5'

EXEC ShortenFile @fileName='C:\TEMP\LoggingData.txt', @numRows=3
GO

The content of the file should now be:

3
4
5

A warning: Reading all lines and operating with them may consume a considerable amount of memory so take this into consideration.

Creating a Rotating Log

While a continuous log file serves certain purposes, in many cases a rotating log file is handy. A rotating log file works for example like this:

  • Create file 1
  • Write entries into file 1 until it’s full, switch to file 2
  • Create file 2
  • Write entries into file 2 until it’s full, switch to file 1
  • Write entries into file 1 until it’s full, switch to file 2 and so on…

We actually already have most of the operations in place so we only need to define a method that will decide which file to use in different situations. For this, let’s use the following:

C#
namespace Logger {
   public static class RotatingLogger {
      /// <summary>
      /// Procedure to write a row of data into a specified file
      /// </summary>
      /// <param name="fileName1">Full name of log file 1 to use</param>
      /// <param name="fileName2">Full name of log file 2 to use</param>
      /// <param name="maxSize">Maximum size of the file in bytes</param>
      /// <param name="line">Line to write</param>
      [Microsoft.SqlServer.Server.SqlProcedure]
      public static void WriteLine(string fileName1, 
                         string fileName2, int maxSize, string line) {
         string fileNameToUse;
         bool truncate = false;

         // Decide the file to use
         if (!System.IO.File.Exists(fileName1)) {
            fileNameToUse = fileName1;
         } else if ((new System.IO.FileInfo(fileName1)).Length < maxSize) {
            fileNameToUse = fileName1;
         } else if (!System.IO.File.Exists(fileName2)) {
            fileNameToUse = fileName2;
         } else if ((new System.IO.FileInfo(fileName2)).Length < maxSize) {
            fileNameToUse = fileName2;
         } else if (System.IO.File.GetLastWriteTime(fileName1) 
                   < System.IO.File.GetLastWriteTime(fileName2)) {
            fileNameToUse = fileName1;
            truncate = true;
         } else {
            fileNameToUse = fileName2;
            truncate = true;
         }
         if (truncate) {
            ContinuousLogger.TruncateFile(fileNameToUse);
         }
         ContinuousLogger.WriteLine(fileNameToUse, line);
      }
   }
}

If the file does not exist or still has room, then it’s used so that file 1 is preferred over file 2. If both files exist and are full, then the older one is replaced and truncated.

To define this in the database, use the following command. And remember again to first re-create both assembly and previous procedures (it’s a pain, isn’t it).

SQL
CREATE PROCEDURE RotatingLogWriteLine (@fileName1 nvarchar(2000), 
                                       @fileName2 nvarchar(2000), 
                                       @maxSize int, 
                                       @line nvarchar(2000))  
AS EXTERNAL NAME WriteLogFile.[Logger.RotatingLogger].WriteLine;
GO

To test the functionality, let’s run the following:

SQL
DECLARE
 @message varchar(2000),
 @counter int
BEGIN
   SET @counter = 1;
   WHILE @counter < 100
   BEGIN
      SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126) 
                     + ': Some logged text, round ' 
                     + CONVERT(nvarchar(10), @counter);
      EXEC RotatingLogWriteLine @fileName1='C:\TEMP\LoggingData1.txt', 
                                @fileName2='C:\TEMP\LoggingData2.txt', 
                                @maxSize=2048, 
                                @line=@message
      SET @counter = @counter + 1;
   END;
END;
GO

As a result, both files should be created and contain some of the logged messages. Since the amount of data was more than combined sizes of the log files, the first rows are lost. So in actual usage, you would carefully consider the maximum size of the file.

Using a Log Table In-Between

We now have proper procedures to handle logging into a file. However, if we use these procedures from different places, there’s always the risk that the syntax how to write into the files may differ between implementations or even different file names may be used in different places resulting in unwanted situations.

One way to tackle this is to use a log table in-between. Let’s create a table like this:

SQL
CREATE TABLE ExternalLog (
   LoggedAt  datetime      NOT NULL DEFAULT SYSDATETIME(),
   LoggedBy  varchar(128)  NOT NULL DEFAULT ORIGINAL_LOGIN(),
   LogText   varchar(2000) NOT NULL
);
GO

So now, we can add log rows into the table. Let’s extend the functionality with the following trigger:

SQL
CREATE TRIGGER ExtrernalLog_Writer  
ON ExternalLog
AFTER INSERT AS 
DECLARE
       @loggedAt datetime,
    @loggedBy varchar(128),
    @logText  varchar(2000),
    @logLine  varchar(3000);
BEGIN
   DECLARE logRows CURSOR FAST_FORWARD FOR 
      SELECT i.LoggedAt, i.LoggedBy, i.LogText
      FROM inserted i
      ORDER BY i.LoggedAt;  
  
   OPEN logRows;  
   FETCH NEXT FROM logRows INTO @loggedAt, @loggedBy, @logText;
   WHILE @@FETCH_STATUS = 0 
   BEGIN  
      SET @logLine = CONVERT(nvarchar(100), @loggedAt, 126) + _
      ': ' + @loggedBy + ': ' + @logText;
      EXEC RotatingLogWriteLine @fileName1='C:\TEMP\TriggeredLog1.txt', 
                                @fileName2='C:\TEMP\TriggeredLog2.txt', 
                                @maxSize=4096, 
                                @line=@logLine
      FETCH NEXT FROM logRows INTO @loggedAt, @loggedBy, @logText;
   END   ;
   CLOSE logRows;  
   DEALLOCATE logRows;  
END;
GO  

So what happens? Each time a new row is inserted into the ExternalLog table, the row is also written into a rotating log file. Let’s test this:

SQL
INSERT INTO ExternalLog (LogText) VALUES ('My single message');
GO

After running the statement above, you should find the file TriggeredLog1.txt in TEMP folder and it contains the desired text.

Just to check that this also works when multiple rows are inserted into the ExternalLog table simultaneously, try running:

SQL
INSERT INTO ExternalLog (LogText) SELECT so.name FROM sysobjects so;
GO

All objects from the current database should now be listed in the file (or files depending on the amount of the objects).

Using the table makes it easier to write into the file from different places:

  • You don’t need to know how to call the procedures
  • You don’t even need to have privileges to call the procedures
  • You don’t need to know the names of the files
  • File names and sizes are handled in a single place

As an enhancement, you could also create a small configuration table containing the file names and sizes. The trigger would then use this information from the configuration so in case the names change, you would not need to recreate the trigger, simply change the configuration.

History

  • 3rd October, 2016: Created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 1507871611-Jul-22 10:46
Member 1507871611-Jul-22 10:46 
QuestionExtremely well written article! Pin
Member 1232914323-Nov-16 15:57
Member 1232914323-Nov-16 15:57 
AnswerRe: Extremely well written article! Pin
Wendelius29-Nov-16 8:01
mveWendelius29-Nov-16 8:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.