How do I create a document on the server by runnin

Question

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.

Answer 1

     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