将查询结果即时导出到文件中

在我看来,您没有在问题的答案中等待SQL代码。问题的主要方面是安全方面。如果没有 系统管理员 没有新的安全漏洞?我想这是你真正的问题。

我看到了至少三种解决你问题的方法。但首先简单解释为什么所有基于 扩展存储过程 . 扩展存储过程,如 xp_cmdshell 都很老了。它们至少存在于SQL Server 4.2之前,这是在第一个Windows NT(NT 3.1)下运行的第一个Microsoft SQL Server。在旧版本的SQL Server中,我没有执行此类过程的安全限制,但后来的一个版本做出了此类限制。重要的是要理解 一般用途 允许启动的程序 任何过程 在SQL Server帐户下,如 xpmi-cdS壳 和 sp_OACreate 必须 有 系统管理员 权限限制。只有一个 面向任务 具有明确的使用区域和基于角色的权限的过程可以在没有安全漏洞的情况下解决问题。所以这是我之前承诺的3种解决方法:

• 在SQL Server上使用 系统管理员 特权。然后创建一个使用 扩展存储过程 喜欢 xpmi-cdS壳 或 鳞片状的 技术上实现您的需求(将一些信息导出到csv文件中)。关于 执行为 子句(见) http://msdn.microsoft.com/en-us/library/ms188354.aspx )将创建的存储过程配置为在帐户下运行 系统管理员 特权。您可以将此过程的执行委托给具有某些SQL角色的用户,以便从权限委托的角度更加灵活。
• 你可以使用 clr存储过程 而不是 xpmi-cdS壳 和 鳞片状的 . 您还应该对创建的过程使用基于角色的权限。
• 最终用户不会直接调用您创建的任何SQL存储过程。存在一个调用SQL存储过程的软件(如WCF服务或网站)。您可以在此软件内部而不是任何SQL存储过程中实现导出到csv文件。

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

您可以构建一个SQL代理作业,并从触发器或SP通过系统SP将其启动。该作业可能会调用ssis或大容量转储脚本…但是,返回即时错误消息可能是一个问题

总的来说,这是一个非常不寻常的要求——你想完成什么?

更新: 经过更多的思考之后——这是一个设计问题,我还没有能够简单地通过使用SQL Server SP找到解决方案。

在过去-这就是我所做的:

• 在应用程序级别-实现异步过程,用户按下按钮,请求文件下载;应用程序接受并释放用户。
• 用户可以通过状态页检查状态,或者在完成或发生错误时收到电子邮件。
• 在应用程序层的同时,可以调用ssis包或SQL代理作业
• 如果需要参数-使用设计和实现特殊表:作业参数-将参数放在哪里
• 您还需要创建更多的表来管理作业和存储作业状态,并与应用程序层通信。
• 您可能希望在数据库级别上使用SQL Server代理
• 您可能希望在应用程序级别使用msmq

这并不容易,但这是导出数据最有效的方法,数据从数据库传输到文件,而不需要通过浏览器传输到应用服务器和用户PC。

你能使用OLE自动化吗?这很难看,你可以使用一些基于集合的字符串构建技术,而不是光标,但是这里……

    Declare @Dir varchar(4000)
    Set @Dir = 'c:\some\path\accessible\to\SQLServer'

    If @Dir IS NULL
       Begin
        print 'dir is null.'
        Return 1
       End

    declare
        @FilePath as varchar(255),
        @DataToWrite as varchar(8000)

    If right(@DataDir,1) <> '\'
       Set @DataDir = @DataDir + '\'

    Set @FilePath = @DataDir + 'filename.csv' 

    DECLARE @RetCode int , @FileSystem int , @FileHandle int

    EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
    begin
      RAISERROR ('could not create FileSystemObject',16,1)

    End

    declare @FileExists int

    Execute @RetCode = sp_OAMethod @FileSystem, 'FileExists', @FileExists OUTPUT, @FilePath
    --print '@FileExists = ' + cast(@FileExists as varchar)

    If @FileExists = 1
    Begin
        RAISERROR ('file does not exist',16,1)
        /*return 1*/
    End

    --1 = for reading, 2 = for writing (will overwrite contents), 8 = for appending
    EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @FilePath, 8, 1
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
    begin
        RAISERROR ('could not create open text file',16,1)
    End


    DECLARE CSV CURSOR
    READ_ONLY
    FOR 

    Select Anything From MyDataTable
    order by whatever

    DECLARE @fld1 nvarchar(50)
        ,@fld2 nvarchar(50)

    OPEN CSV

    FETCH NEXT FROM CSV INTO @fld1, @fld2 

    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN

            Set @DataToWrite = @fld1 + ',' + @fld2 + char(13) + char(10) 
            EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Write' , NULL , @DataToWrite 

            IF (@@ERROR|@RetCode > 0)
               begin
                RAISERROR ('could not write to file',16,1)

               End
        END

        FETCH NEXT FROM OpenOrders INTO @fld1, @fld2 

    END


CLOSE CSV
DEALLOCATE CSV

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' , NULL
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not close file',16,1)

EXEC sp_OADestroy @FileSystem

return 0

End

一般来说,不,如果没有大量的麻烦和努力以及系统管理权限,就无法完成这类工作。

SQL是一个数据库引擎,专注于数据库问题,因此,非常正确地说,它有非常糟糕的文件操作工具。解决办法包括:

• xp_cmdshell是文件操作的首选工具。
• 我自己也喜欢sp_oa*解决方案,因为它让我想起了SQL 7.0。但是使用这些功能总是让我紧张。
• 您可能可以对openrowset执行一些操作,其中insert的目标是使用此函数定义的文件。听起来不太可能,也许值得一看。
• 类似地,链接服务器定义可以用作插入或选择…到…的目标。声明。

保安似乎是你的替罪羊。总的来说,当SQL解包到操作系统时,它拥有启动SQL服务所使用的NT帐户的所有权利;如果要限制网络访问,请仔细配置该帐户(不要将其设为域管理!)

可以以没有系统管理权限的用户的身份调用xp_cmdshell,并将这些调用配置为不具有与SQL服务NT帐户相同的访问权限。根据BOL(SQL 2005及以上版本):

xp命令shell代理帐户
当不是sysadmin固定服务器角色成员的用户调用它时,xp_cmdshell使用存储在名为xp_cmdshell_proxy的凭据中的帐户名和密码连接到Windows。如果此代理凭据不存在,xp_cmdshell将失败。

可以通过执行sp_xp_cmdshell_proxy_帐户来创建代理帐户凭据。作为参数,此存储过程使用Windows用户名和密码。例如,下面的命令为具有Windows密码sdfh%dkc93vcmt0的Windows域用户传送\kober创建代理凭据。

因此,您的用户以任何用户权限登录( 不 系统管理员!)并执行存储过程,该过程调用xp命令行程序,它将“获取”已配置的任何代理权限。再次,很尴尬,但听起来它会做你想做的。(一个可能的限制因素是,您只能获得一个代理帐户,因此它必须满足所有可能的需求。)

老实说,我觉得最好的解决办法是:

• 标识对存储过程的调用源,
• 让过程返回要写入文件的数据(如果需要,可以在过程中执行所有格式化和布局),以及
• 让调用例程管理所有的文件准备步骤(这可能与将从SQL返回的数据重定向到打开的文件一样简单)

那么,启动对存储过程的调用是什么?