T-SQL脚本实现带Basic认证的GZIP下载解压、XML传存储过程及定时执行的可行性与入门

绝对没问题！这个需求完全可以用T-SQL结合SQL Server的其他功能实现，我给你梳理清楚每一步该怎么做，以及入手的优先级：

1. 带Basic Auth的HTTP/HTTPS下载（T-SQL实现） #

首先，你需要启用SQL Server的OLE Automation功能（默认可能关闭），然后通过sp_OACreate调用HTTP请求组件。这里要处理Basic Auth：把用户名:密码转成Base64编码，放在Authorization头里。

示例代码：

-- 启用OLE Automation（仅需执行一次）
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
GO

DECLARE @Url NVARCHAR(2000) = 'https://your-source-url/file.gz';
DECLARE @Username NVARCHAR(100) = 'your-username';
DECLARE @Password NVARCHAR(100) = 'your-password';
DECLARE @AuthHeader NVARCHAR(500);
DECLARE @HttpObj INT;
DECLARE @Response VARBINARY(MAX);

-- 生成Basic Auth头
SET @AuthHeader = 'Basic ' + CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)')
FROM (SELECT CAST(@Username + ':' + @Password AS VARBINARY(MAX)) AS bin) AS t;

-- 创建HTTP请求对象
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @HttpObj OUT;
EXEC sp_OAMethod @HttpObj, 'Open', NULL, 'GET', @Url, 'false';
EXEC sp_OAMethod @HttpObj, 'SetRequestHeader', NULL, 'Authorization', @AuthHeader;
-- 如果是HTTPS且需要忽略证书验证（仅测试环境用，生产不建议）
EXEC sp_OAMethod @HttpObj, 'SetOption', NULL, 4, 13056;

-- 发送请求
EXEC sp_OAMethod @HttpObj, 'Send';

-- 获取响应二进制内容（GZIP格式）
EXEC sp_OAGetProperty @HttpObj, 'ResponseBody', @Response OUT;

-- 清理对象
EXEC sp_OADestroy @HttpObj;
GO

2. GZIP解压（T-SQL实现） #

SQL Server没有内置GZIP解压函数，最可靠的方式是创建CLR自定义函数：

1. 用C#写一个简单的解压方法，接收byte[]返回byte[]：

using System.IO;
using System.IO.Compression;
using Microsoft.SqlServer.Server;

public class GzipHelper
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static byte[] DecompressGzip(byte[] compressedData)
    {
        using (var compressedStream = new MemoryStream(compressedData))
        using (var gzipStream = new GZipStream(compressedStream, CompressionMode.Decompress))
        using (var resultStream = new MemoryStream())
        {
            gzipStream.CopyTo(resultStream);
            return resultStream.ToArray();
        }
    }
}

2. 编译成DLL，在SQL Server中注册CLR程序集（需要开启CLR集成）：

-- 启用CLR集成（仅需执行一次）
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- 创建程序集（替换为你的DLL路径）
CREATE ASSEMBLY GzipAssembly
FROM 'C:\Path\To\Your\GzipHelper.dll'
WITH PERMISSION_SET = SAFE;
GO

-- 创建解压函数
CREATE FUNCTION dbo.DecompressGzip(@CompressedData VARBINARY(MAX))
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME GzipAssembly.GzipHelper.DecompressGzip;
GO

3. 调用函数解压下载的GZIP内容，转成XML类型：

DECLARE @CompressedGzip VARBINARY(MAX) = -- 上面下载的@Response变量
DECLARE @UncompressedXmlBytes VARBINARY(MAX) = dbo.DecompressGzip(@CompressedGzip);
DECLARE @XmlContent XML = CAST(@UncompressedXmlBytes AS XML);

3. 调用存储过程传入XML参数 #

这一步很直接，把上面得到的@XmlContent作为参数传入你的存储过程即可：

EXEC dbo.YourTargetStoredProcedure @XmlParameter = @XmlContent;

4. 每日定时执行（SQL Server Agent） #

用SQL Server Agent创建定时作业：

• 打开SQL Server Management Studio（SSMS），展开SQL Server Agent → Jobs，右键新建作业。
• 作业名称自定义，比如“每日同步XML数据”。
• 切换到步骤标签，新建步骤：类型选“Transact-SQL (T-SQL)”，数据库选你的目标数据库，把上面的下载、解压、调用存储过程的脚本粘贴进去。
• 切换到调度标签，新建调度：频率选“每天”，设置执行时间，比如凌晨2点。
• 保存作业即可，SQL Server Agent会自动每日执行。

1. 先验证下载功能：单独测试HTTP下载的T-SQL脚本，确保能正确获取GZIP二进制内容（可以把@Response写入临时表或者本地文件验证）。
2. 搞定解压环节：先在本地测试CLR函数的解压逻辑，再部署到SQL Server，验证解压后的内容是正确的XML。
3. 测试存储过程调用：手动构造一个XML变量，传入存储过程，确保业务逻辑正常。
4. 最后配置定时作业：前面的步骤都跑通后，再把整段脚本放到SQL Server Agent作业里，手动测试一次执行，确认没有问题后启用每日调度。

• 安全问题：不要在脚本里硬编码用户名密码，建议用SQL Server的**凭据（Credentials）**或者加密存储，然后在脚本中读取。
• HTTPS证书：如果目标站点是HTTPS且证书不被信任，测试环境可以临时忽略证书验证，但生产环境必须配置信任的证书。
• 权限：确保SQL Server服务账号和SQL Server Agent账号有访问外部HTTP站点的权限，以及CLR程序集的相关权限。

内容的提问来源于stack exchange，提问作者Eddie Ted Crocombe