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.
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:
USE master;
GO
CREATE DATABASE LoggerTest;
GO
USE LoggerTest;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
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 {
[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:
- Assembly registration
- 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.
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:
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
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:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TruncateFile(string fileName) {
System.IO.FileStream logStream;
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:
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:
- drop all procedures, functions, etc. that are defined from this assembly
- drop the old assembly
- create the new assembly
- 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 this, the file should exist, but be empty.
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:
[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:
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.
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 {
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WriteLine(string fileName1,
string fileName2, int maxSize, string line) {
string fileNameToUse;
bool truncate = false;
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).
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.
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:
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 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
