我如何通过运行一个现有的StoredProcedure的或程序的R2008 SQL Server上的SQL语句在服务器上创建的文档 [英] How do I create a document on the server by running an existing storedprocedure or the sql statement of that procedure on a R2008 sql server

问题描述

我有一个运行，其生成动态形式的结果的存储过程的应用程序。

相同的过程被用于许多形式，然后这将有不同的列

我要调用存储过程来运行现有的存储过程和转储在服务器上的结果，新创建的文件和路径返回文件。

从ASP经典节目一边，我就可以生成一个链接，该报告的下载。

像下面的内容可能已经上了年纪的SQL Server版本的工作。

  EXEC sp_makewebtask
    @outputfile ='E：\\ NMCMS_DEMO \\ UploadedMedia \\报告\\ Form.xls'，
    @query =SELECT MAX（情况下，当fe.FLabel =''账号''然后按[ItemValue]年底）为[帐号]，MAX（情况下，当fe.FLabel =''细胞数量''然后按[ItemValue]结束）为[细胞数量]，MAX（情况下，当fe.FLabel =''注释''然后按[ItemValue]年底）为[注释]，MAX（情况下，当fe.FLabel =''电子邮件地址''，那么[ItemValue ] END）为[电子邮件地址]，MAX（情况下，当fe.FLabel =''全名''然后按[ItemValue]年底）为[全名]，fs.SessionID FROM [FormSubmissions] FS内部联接formelements FE上fs.FormID = fe.FID和fe.ID = fs.FormChildID哪里FormID = 12和F型及下;→4组由fs.SessionID'，
    @colheaders = 1，
    @ FixedFont = 0，@ LASTUPDATED = 0，@ resultstitle ='测试细节
 

我不能用我的存储过程，但是与上述方法。

解决方案

  EXEC sp_configure显示高级选项，1
     走
      - 要更新的高级选项当前配置的值。
     RECONFIGURE
     走
      - 要启用该功能。
     版EXEC sp_configure的xp_cmdshell'，1
     走
      - 要更新此功能的当前配置的值。
     RECONFIGURE    CREATE PROCEDURE [DBO]。[exportSubmissionsReport]
    @login VARCHAR（50）='X'，
    @password VARCHAR（10）='Y $'，
    @server VARCHAR（50）='我/实例，
    @Database VARCHAR（50）='database_DEMO'，
    @Statement VARCHAR（5000），
    @filename VARCHAR（70）='TESD    如
    声明@query VARCHAR（8000）;
    设置@query ='sqlps.exe -command援引-SQLCMD';
    设置@query = @查询+ @ +的声明'';
    设置@query = @查询+'-ServerInstance'+ @ +服务器-Username';
    设置@query = @查询+ @ +登录-Password'+ @ +密码'-Database'+ @ +数据库|出口-CSV E：\\ NMCMS_DEMO \\ UploadedMedia \\报告\\'+ @文件名+;CSV'。
    执行xp_cmdshell @query
 

I have an application that runs a stored procedure which generates the results of a dynamic form.

the same procedure is used for many forms which would then have different columns.

I want to call a stored procedure to run the existing stored procedure and dump the results to a newly created document on the server and return the path to the file.

from the asp classic program side I can then generate a link for download of the report.

Something like the following might have worked on the older sql server version.

    EXEC sp_makewebtask 
    @outputfile = 'E:\NMCMS_DEMO\UploadedMedia\Reports\Form.xls', 
    @query ='Select max(case when fe.FLabel = ''Account Number'' then [ItemValue] end) as [Account Number] ,max(case when fe.FLabel = ''Cell Number'' then [ItemValue] end) as [Cell Number] ,max(case when fe.FLabel = ''Comment'' then [ItemValue] end) as [Comment] ,max(case when fe.FLabel = ''E-mail Address'' then [ItemValue] end) as [E-mail Address] ,max(case when fe.FLabel = ''Full Name'' then [ItemValue] end) as [Full Name] ,fs.SessionID FROM  [FormSubmissions]  fs inner join formelements fe on fs.FormID = fe.FID and fe.ID = fs.FormChildID    where FormID = 12 and FType<>4 group by fs.SessionID',
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

I cannot use my stored procedure with the above approach however.

解决方案

     EXEC sp_configure 'show advanced options', 1
     GO
     -- To update the currently configured value for advanced options.
     RECONFIGURE
     GO
     -- To enable the feature.
     EXEC sp_configure 'xp_cmdshell', 1
     GO
     -- To update the currently configured value for this feature.
     RECONFIGURE   

    create procedure [dbo].[exportSubmissionsReport] 
    @login varchar(50) ='x',
    @password varchar(10) ='y$',
    @Server varchar(50) = 'my/instance', 
    @Database varchar(50) = 'database_DEMO', 
    @Statement varchar(5000),
    @fileName varchar(70) = 'TESD'

    as
    declare @query varchar(8000);
    set @query = 'sqlps.exe -command "invoke-sqlcmd ';
    set @query = @query +@Statement+ ' ';
    set @query = @query + '  -ServerInstance '+@Server+' -Username ';
    set @query = @query + @login+' -Password '+@password+' -Database                     '+@Database+'         |Export-CSV E:\NMCMS_DEMO\UploadedMedia\Reports\'+@fileName+'.csv"';
    execute xp_cmdshell @query

这篇关于我如何通过运行一个现有的StoredProcedure的或程序的R2008 SQL Server上的SQL语句在服务器上创建的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！