直接在 SQL 中进行 FTP (使用 SQL CLR)

• 下载演示项目 - 4.83 KB
• 下载源代码 - 8.49 KB

引言

在一个使用多种技术的复杂 SQL 环境中工作时，我们遇到的问题之一是使用 SQL 作业通过 FTP 传输文件。

我们环境中的现有 SQL 作业使用外部应用程序（命令行应用程序 ftp.exe），通过 xp_cmdshell 扩展存储过程调用，将文件传输到 FTP 服务器。

它在大多数时候都有效，但当它失败时，没有简单的方法可以直接在 SQL 中跟踪失败。

Transact-SQL 可以很强大并且很有趣，但它有一些局限性，并且缺乏托管代码的优势。

我提供的解决方案允许更好地处理代码... 隆重推出 CLR 存储过程...

CLR 存储过程

定义 CLR 存储过程允许从 SQL 内部使用托管代码。 这在扩展 SQL 存储过程的功能方面提供了几乎无限的可能性，通过利用 .NET Framework 和您喜欢的语言（例如 C# 或 VB.NET）。

通过使用调用在预编译的 .NET DLL 中定义的函数的 CLR 存储过程，我们能够通过 FTP 将文件传输到 FTP 服务器，并从 SQL 存储过程中捕获和处理异常，最后将结果返回到 SQL 变量。

利用这一点，我们使用一个跟踪表来记录文件何时成功上传或下载，如果未成功，我们将错误记录下来，以帮助 SQL 数据库管理员进行调试/故障排除。

此解决方案的灵感来自 Virat Kothari 撰写的文章 CLR 存储过程及其逐步创建。

入门

这是一个创建 CLR 存储过程的步骤的高级概述。

1. 创建一个 Visual Studio 项目作为类库。
2. 使用 [Microsoft.SqlServer.Server.SqlFunction] 修饰要公开的函数。
3. 编译项目并将 .DLL 文件复制到适当的文件夹。
4. 从 SQL Server Management Studio 中注册程序集

   CREATE ASSEMBLY clr_sqlFTP AUTHORIZATION dbo
   FROM 'c:\Windows\System32\sqlFTP.dll'
   WITH PERMISSION_SET = UNSAFE 

5. 创建 CLR 存储过程

   CREATE PROCEDURE ftpDownload
          @ftp_server nvarchar(MAX),
          @ftp_user nvarchar(MAX), 
          @ftp_pwd nvarchar(MAX), 
          @local_file nvarchar(MAX),
          @rmt_file nvarchar(MAX),
          @ftp_mode int,
          @result nvarchar(MAX) OUTPUT
    AS EXTERNAL NNAME clr_sqlFTP.[sqlFTP.FTP].ftpDownload

6. 像任何普通的存储过程一样执行 CLR 存储过程，并将结果保存到跟踪表中。

Using the Code

我提供了源代码和编译后的解决方案，这将允许任何想要快速修复或想要了解有关该解决方案的更多信息的人开始使用 CLR 存储过程和一个在 Microsoft SQL 内部直接工作的 FTP 解决方案。

FTP 功能由 .NET 类 FtpWebRequest 提供，该类继承自 WebRequest。

通过执行 CLR 存储过程并将结果保存到表中，您可以记录上传或下载的结果。

安装 SQL FTP 解决方案

如果您下载编译后的解决方案，请确保将 .dll 文件复制到 c:\windows\system32 文件夹，或在 Create Assembly 语句中修改文件的位置。

要在 SQL 数据库中启用 CLR，请使用以下命令

sp_configure 'clr enable', 1
GO
RECONFIGURE
GO
ALTER DATABASE <databasename> SET TRUSTWORTHY ON

关注点

问题不会创造解决方案，它们会创造对解决方案的渴望，人们创造解决方案。

我希望我创建了一个您能从中受益的解决方案。

历史

这是本文的第一个版本，我欢迎任何信息或建议以改进所提供的解决方案。