I found solution of your problem. It’s a little bit tricky and looks like a hack, but it seems it’s impossible to solve in another way.
The idea is to create a .NET SQL function which logs data where you need (file, Windows EventLog, db and so on), next create SQL UDF which calls this .NET function and finally call this SQL function from your functions passing all parameters needed to be logged. SQL Server doesn't check what is inside .net function and you can write there all logic you need.
The idea of how to create a .net SQL function without any security limitations is taken from this post.
So, create a .net library project with this one file
using System;
namespace SqlTest
{
public class LogEvent
{
[Microsoft.SqlServer.Server.SqlFunction]
public static int Log(string data)
{
System.IO.File.AppendAllText(@"C:\Log\LogUDF.txt", data);
return 0;
}
}
}
Sign it with some pfx certificate (project properties -> signing tab).
Next, call this query
USE [master]
CREATE ASYMMETRIC KEY LogKey FROM EXECUTABLE FILE =
'C:\Work\ConsoleApplication1\SqlTest\bin\Debug\SqlTest.dll'
CREATE LOGIN LogLogin FROM ASYMMETRIC KEY LogKey
GRANT UNSAFE ASSEMBLY TO LogLogin
GO
USE [MyDB]
CREATE ASSEMBLY SqlTest FROM
'C:\Work\ConsoleApplication1\SqlTest\bin\Debug\SqlTest.dll'
WITH PERMISSION_SET = unsafe
GO
CREATE FUNCTION dbo.Log( @data as nvarchar(200) )
RETURNS int
AS EXTERNAL NAME SqlTest.[SqlTest.LogEvent].Log
Here you need to change path to your compiled library, MyDB - your database name.
And you will create dbo.Log SQL function. Next you can call it where you need. For example like from this TestFunction
CREATE FUNCTION TestFunction
(
@p1 int
)
RETURNS int
AS
BEGIN
DECLARE @temp int
SELECT @temp = [dbo].[Log] ('fff')
RETURN 1
END
So, calling SELECT TestFunction(1) will write 'fff' text to C:\Log\LogUDF.txt file.
That’s it. A few important notes:
- SQL server should have permissions (login/user) to write into file
C:\Log\LogUDF.txt.
- You should be SQL server admin
