如何在Linux Docker容器的SQL Server存储过程中写入文本文件

针对你的场景（Linux Docker容器中的SQL Server，无容器命令行权限，需通过存储过程实现文件导出），以下是几种可行的解决方案：

方案1：启用xp_cmdshell并调用bcp命令 #

Linux版SQL Server同样支持xp_cmdshell扩展存储过程，可通过它调用系统自带的bcp工具完成数据导出：

1.1 启用xp_cmdshell #

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

1.2 创建导出存储过程 #

CREATE PROCEDURE dbo.ExportBCPOutputToCSV
    @OutputPath NVARCHAR(255) = '/data/readings/Data.csv'
AS
BEGIN
    SET NOCOUNT ON;

    -- 构造bcp命令，替换YourPassword为实际密码，建议后续改用凭据存储避免明文
    DECLARE @bcpCommand NVARCHAR(4000) = 
        'bcp "SELECT CSVLine FROM Metrology.dbo.BCPOutput" queryout "' + @OutputPath + 
        '" -S localhost -U sa -P YourPassword -c -t,';

    EXEC xp_cmdshell @bcpCommand;
END;
GO

注意事项 #

• 确保SQL Server服务账号（Linux容器中默认是mssql用户）对目标路径/data/readings/拥有写入权限，若为宿主机挂载目录，需在宿主机执行chown mssql:mssql /data/readings调整权限。
• 避免硬编码密码，可通过创建SQL Server凭据存储账号密码：

CREATE CREDENTIAL BcpExportCred WITH IDENTITY = 'sa', SECRET = 'YourPassword';

方案2：使用CLR存储过程直接写入文件 #

若不想依赖系统命令，可通过CLR存储过程实现原生文件写入，可控性更强：

2.1 启用CLR集成 #

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
-- 授予数据库信任权限以支持不安全的CLR操作
ALTER DATABASE Metrology SET TRUSTWORTHY ON;
GO

2.2 编写并部署CLR程序集 #

1. 创建C#类库代码（编译为FileWriter.dll）：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public class FileWriter
{
    [SqlProcedure]
    public static void ExportCSVToFile(SqlString outputPath)
    {
        string filePath = outputPath.Value;
        // 使用上下文连接访问当前数据库
        using (SqlConnection conn = new SqlConnection("Context Connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT CSVLine FROM dbo.BCPOutput", conn);
            using (SqlDataReader reader = cmd.ExecuteReader())
            using (StreamWriter sw = new StreamWriter(filePath))
            {
                while (reader.Read())
                {
                    sw.WriteLine(reader.GetString(0));
                }
            }
        }
    }
}

2. 将编译好的DLL复制到容器内可访问路径（如/var/opt/mssql/data/），然后注册程序集并创建存储过程：

CREATE ASSEMBLY FileWriterAssembly
FROM '/var/opt/mssql/data/FileWriter.dll'
WITH PERMISSION_SET = UNSAFE; -- 需要文件系统访问权限

CREATE PROCEDURE dbo.ExportBCPOutputToCSV_CLR
    @OutputPath NVARCHAR(255)
AS
EXTERNAL NAME FileWriterAssembly.FileWriter.ExportCSVToFile;
GO

方案3：通过xp_cmdshell调用sqlcmd命令 #

利用Linux下的sqlcmd工具，直接将查询结果输出到文件：

CREATE PROCEDURE dbo.ExportViaSQLCMD
    @OutputPath NVARCHAR(255) = '/data/readings/Data.csv'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlcmdCommand NVARCHAR(4000) = 
        'sqlcmd -S localhost -U sa -P YourPassword -d Metrology -Q "SELECT CSVLine FROM dbo.BCPOutput" -o "' + 
        @OutputPath + '" -h -1'; -- -h -1 用于去除结果表头

    EXEC xp_cmdshell @sqlcmdCommand;
END;
GO

内容的提问来源于stack exchange，提问作者ChrisAsi71