当 MS SQL SERVER 允许远程连接时，如何上传文件并将它们存储在服务器本地路径中? [英] How to upload files and store them in a server local path when MS SQL SERVER allows remote connections?

问题描述

我正在用 Delphi 和 MS SQL Server 开发一个 win32 windows 应用程序.它在 LAN 中运行良好，但我正在尝试添加对 SQL Server 远程连接的支持(= 使用可以通过外部 IP 访问的数据库，如本文所述:http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277).

I am developing a win32 windows application with Delphi and MS SQL Server. it works fine in LAN but I am trying to add the support for SQL Server remote connections (= working with a DB that can be accessed with an external IP, as described in this article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277).

基本上我在数据库中有一个表，我在其中保存 DocumentID、文档描述和文档路径(如 \\FILESERVER\MyApplicationDocuments\45.zip).

Basically I have a Table in DB where I keep the DocumentID, the document description and the Document path (like \\FILESERVER\MyApplicationDocuments\45.zip).

当然 \\FILESERVER 是服务器的本地 (LAN) 路径，而不是客户端的(因为我现在正在尝试添加对远程连接的支持).

Of course \\FILESERVER is a local (LAN) path for the server but not for the client (as I am now trying to add the support for remote connections).

所以我需要一种访问 \\FILESERVER 的方法，即使我在局域网中看不到它.

So I need a way to access \\FILESERVER even if of course I cannot see it in LAN.

我发现以下 T-SQL 代码片段非常适合下载技巧":

I found the following T-SQL code snippet that is perfect for the "download trick":

SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\FILESERVER\MyApplicationDocuments\45.zip' , SINGLE_BLOB) AS X

SELECT BulkColumn as MyFile FROM OPENROWSET(BULK '\FILESERVER\MyApplicationDocuments\45.zip' , SINGLE_BLOB) AS X

通过上面的代码，我可以在客户端下载一个文件.

With the code above I can download a file on the client.

但是怎么上传呢?我需要一个上传技巧"才能插入新文件，还需要删除或替换现有文件.

But how to upload it? I need an "Uppload trick" to be able to insert new files, but also to delete or replace existing files.

有人可以推荐吗?如果没有技巧，你能提出替代方案吗?就像扩展存储过程或从服务器调用某些 .net 程序集一样.

Can anyone suggest? If a trick is not available could you suggest an alternative? Like an extended stored procedure or calling some .net assembly from the server.

推荐答案

如果您有 sql 2008，那么您可以使用 FILESTREAM，然后sql server会自动把它扔到磁盘上.

If you have sql 2008, then you can use FILESTREAM, then sql server will automatically throw it out to disk.

如果您有 sql 2005，我会考虑将数据移动到 varbinary(max) 列并以这种方式处理它(也很简单).

If you have sql 2005, I'd consider just moving the data into a varbinary(max) column and deal with it that way (also pretty simple).

如果这些都不适用或者你不能把它推到一个 varbinary 列中，那么我不会使用 sql server 来处理实际的文件内容，而只是有一个将文件存储在文件系统上的 web 服务或Web 服务可以轻松访问的 SAN.(同恕我直言)

If neither of those apply OR you can't shove it into a varbinary column, then I would not use sql server to handle the actual file contents and instead just have a web service which stored the file on the file system or a SAN that the web service can easily access. (same as IMHO)

更新:我脑海中闪过的另一个想法.如果您使用的是 SQL 2005/08，那么您可以编写一个 CLR 存储过程在 .Net 中.这可以处理与本地文件系统之间的 blob 数据传输.

UPDATE: One other idea that crossed my mind. If you are using SQL 2005/08 then you can write a CLR Stored procedure in .Net. This could handle transferring the blob data to / from the local file system.

这篇关于当 MS SQL SERVER 允许远程连接时，如何上传文件并将它们存储在服务器本地路径中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！