Click here to Skip to main content
12,622,032 members (36,566 online)
Click here to Skip to main content
Add your own
alternative version

Stats

8.1K views
352 downloads
21 bookmarked
Posted

Writing into a file from database

, 4 Nov 2016 CPOL
Rate this:
Please Sign up or sign in to vote.
Few CLR procedure concepts to write into a file from SQL Server database

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 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

-----------------------------------------------
-- 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

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

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.

-- 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.

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

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

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

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

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 following:

      /// <summary>
      /// Procedure to trucate 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 if 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 to an error

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

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

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

After his 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 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

/// <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 the 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

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

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

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 exists 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).

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

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 into unwanted situations.

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

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

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

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

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

All objects from 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 Octobre, 2016: Created.

License

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

Share

About the Author

Mika Wendelius
Architect
Finland Finland
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
QuestionExtremely well written article! Pin
Member 1232914323-Nov-16 16:57
memberMember 1232914323-Nov-16 16:57 
AnswerRe: Extremely well written article! Pin
Mika Wendelius29-Nov-16 9:01
mvpMika Wendelius29-Nov-16 9: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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161128.1 | Last Updated 4 Nov 2016
Article Copyright 2016 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid