Writing data from within user-defined function is not permitted. You cannot use any INSERT/UPDATE/DELETE/MERGE statement because they’re “side-effecting” for SQL Server. My colleague recently needed to log something from within function so we were searching for solution how to do that with least work. There is one option to call sp_OAxxxx extended stored procedures from within function to write anything to debug file.  It can be done but performance is horrible even for debug purposes because OA procedures use OLE-Automation model to create COM objects (and call their methods to write to file).

Much better solution is to develop simple SQL CLR UDF in C# to write to file for you.

1) Create CLR UDF in C#:

using System.IO;
using Microsoft.SqlServer.Server;
public class Debug
{
    [SqlFunction]
    public static int ToFile(string fileName, string text)
    {
        return Write(fileName, text);
    }
    private static int Write (string fileName, string line)
    {
        if (File.Exists(fileName))
        {
            using (var sw = File.AppendText(fileName))
            {
                sw.WriteLine(line);
                return 0;
            }
        }
        return -1;
    }
}

2) I will not go deep into step of registering this function in SQL Server because it was described many times on many places. However, this function is manipulating with files, some security changes must be done to your database. In short, you need to set your database trustworthy, register assembly in SQL Server (with external access permissions) and create function.

-- set your database trustworthy because of 
-- need for external access
ALTER DATABASE <your_database>
    SET TRUSTWORTHY ON
GO
-- Register assembly to SQL Server
CREATE ASSEMBLY AUTHORIZATION [dbo]
FROM
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
-- Create encapsulation for SQL CLR UDF.
-- Function has same signature as its C# counterpart.
CREATE FUNCTION fn_ToFile(@fileName [nvarchar](255), @text [nvarchar](4000))
RETURNS INT WITH EXECUTE AS CALLER
AS
--reference C# ToFile method of Debug class
EXTERNAL NAME .Debug.ToFile
GO
-- This is some business logic function you 
-- want to log anything in.
CREATE FUNCTION [fn_SomeBusinessLogicFunction]()
RETURNS INT WITH EXECUTE AS CALLER
AS
BEGIN
    -- log to file    
    RETURN dbo.fn_ToFile('your_file_path', 'Debug text')
END

Tags: access permissions, AppendText, AUTHORIZATION, automation, CALLER, CLR, CLR UDF, csharpcode, EXECUTE, EXTERNAL, OAxxxx, OLE-Automation, PERMISSION, Register assembly, security changes, SQL, SQL Server, SqlFunction, SqlServer, trustworthy, udf, writeline