即时将查询结果导出到文件 [英] Exporting query results to a file on the fly

问题描述

我需要将查询结果导出到 csv 文件并将该文件放在网络共享文件夹中.

I need to export the results of a query to a csv file and put the file on a network shared folder.

1. 是否可以在存储过程中实现这一点?
2. 如果是，还有另一个限制:我可以在没有系统管理员权限的情况下实现这一点，也就是不使用 xp_cmdshell + BCP 实用程序吗?
3. 如果不是 2.，调用者是否必须具有系统管理员权限，或者如果 SP 所有者具有系统管理员权限就足够了?

以下是该问题的更多详细信息: SP 必须即时导出和传输文件，并在出现问题时引发错误.调用者必须立即得到响应，即在没有错误的情况下，他可以假定结果已成功传输到文件夹.因此，不能选择每 N 分钟运行一次的 DTS/SSIS 作业.我知道这个问题听起来像是我必须在应用程序级别执行此操作，但如果所有这些工作都可以通过 T-SQL 完成，我会非常高兴.

Here are some more details to the problem: The SP must export and transfer the file on the fly and raise error if something went wrong. The caller must get a response immediately, i.e. in case of no error, he can assume that the results are successfully transferred to the folder. Therefore, a DTS/SSIS job that runs every N minutes is not an option. I know the problem smells like I will have to do this at application level, but I would be more than happy if all those stuff could be done from T-SQL.

推荐答案

在我看来，您不是在等待问题答案中的 SQL 代码.您问题的主要方面是安全方面.在没有sysadmin 权限和新的安全漏洞的情况下，您应该怎么做才能实现您的要求?我认为这是你真正的问题.

It seems to me, that you are not waiting for an SQL code in the answer on your question. The main aspect of you question is the security aspect. What should you do to implement your requirement without sysadmin privileges and without a new security hole? This is your real question I think.

我认为至少有 3 种方法可以解决您的问题.但首先简要说明为什么所有基于扩展存储过程的解决方案中都存在系统管理员权限.像 xp_cmdshell 这样的扩展存储过程已经很老了.它们至少在 SQL Server 4.2 之前就存在了，SQL Server 4.2 是第一个在第一个 Windows NT (NT 3.1) 下运行的 Microsoft SQL Server.在旧版本的SQL Server 中我并没有安全限制来执行这样的程序，但后来有人做了这样的限制.重要的是要了解，所有允许在 SQL Server 帐户下启动任何进程的通用过程，例如 xp_cmdshell 和 sp_OACreate 必须具有系统管理员权限限制.只有面向任务的程序，具有明确的使用区域和基于角色的权限，才能在没有安全漏洞的情况下解决问题.所以这是我之前承诺的 3 种解决方法:

I see at least 3 ways to solve your problem. But first of all a short explanation why sysadmin privileges exists in all solutions based on Extended Stored Procedures. Extended Stored Procedures like xp_cmdshell are very old. They existed at least before SQL Server 4.2, the first Microsoft SQL Server running under the first Windows NT (NT 3.1). In the old version of SQL Server I was not security restriction to execute such procedures, but later one made such restrictions. It is important to understand, that all general purpose procedures which allow starting any process under SQL Server account like xp_cmdshell and sp_OACreate must have sysadmin privileges restriction. Only a task oriented procedures with a clear area of usage and role based permissions can solve the problem without a security hole. So this is the 3 solution ways which I promised before:

• 您在 SQL 服务器上创建了一个具有 sysadmin 权限的新 SQL 帐户.然后你创建一个存储过程，它使用 Extended Stored Procedures 中的一些，比如 xp_cmdshell 或 sp_OACreate 并在技术上实现你的要求(将一些信息导出到 CSV文件).关于EXECUTE AS 条款(参见http:///msdn.microsoft.com/en-us/library/ms188354.aspx) 配置创建的存储过程，使其在具有 sysadmin 权限的帐户下运行.您可以将此过程的执行委托给具有某些 SQL 角色的用户，以便在权限委托方面更加灵活.
• 您可以使用 CLR 存储过程代替 xp_cmdshell 和 sp_OACreate.您还应该对创建的过程使用基于角色的权限.
• 最终用户不会直接调用您创建的任何 SQL 存储过程.存在一个调用 SQL 存储过程的软件(如 WCF 服务或网站).您可以在此软件内部而不是在任何 SQL 存储过程内部实现导出到 CSV 文件.

• You create a new SQL account on you SQL server with sysadmin privileges. Then you create a stored procedure which use some from Extended Stored Procedures like xp_cmdshell or sp_OACreate and technically implement you requirements (export some information into a CSV file). With respect of EXECUTE AS Clause (see http://msdn.microsoft.com/en-us/library/ms188354.aspx) you configure the created stored procedure so, that it runs under the account with sysadmin privileges. You delegate the execution of this procedure to users with a some SQL role, to be more flexible from the side of delegation of permission.
• You can use CLR Stored Procedures instead of xp_cmdshell and sp_OACreate. You should also use role based permissions on the procedure created.
• The end-user doesn’t call directly any SQL stored procedure what you create. There is exists a piece of software (like WCF service or a Web Site) which call your SQL stored procedure. You can implement the export to CSV file inside of this software and not inside of any SQL stored procedure.

在所有实施方式中，您应该准确定义您将在何处保存密码，用于访问文件系统.你有不同的选择，都有相应的优点和缺点.可以使用模拟来允许使用最终用户的帐户访问文件系统.最好的方法取决于您所在环境的情况.

In all implementation ways you should exactly define where you will hold the password of the account with which you access to the file system. There are different options which you have, all with corresponding advantages and disadvantages. It's possible to use impersonation to allow access to the file system with the end-user‘s account. The best way depends on the situation which you have in your environment.

这篇关于即时将查询结果导出到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！