问题描述
我有一个运行，其生成动态形式的结果的存储过程的应用程序。
相同的过程被用于许多形式，然后这将有不同的列
我要调用存储过程来运行现有的存储过程和转储在服务器上的结果，新创建的文件和路径返回文件。
从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语句在服务器上创建的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！