Need Some help on sp_makewebtask

Question

Hi ,

 

I am using sp_makewebtask system proc to export results to excel file. Below is part of the code. I am getting below error message must delcare @process_id.

input_file_notification stores procedure expects parameters during run time. I have declared and cursor and took the process id valu to @process_id variable . how can I pass that @process_id  value to the @query

EXEC

 

@RC = sp_makewebtask

 

 

@outputfile = @OutputFileFullPath,

@query

= 'Exec input_File_Notification  @PROCESS_ID,@OUTPUT_DATE ',

@colheaders

=1,

@FixedFont

=0,

@lastupdated

=0,

@resultstitle

=@ReportTitle

Answer 1

Have you tried concatenating the values into a string variable, and passing that instead of specifying the string in the EXEC?

Something like:

DECLARE @QueryString nvarchar(256)

SELECT @ QueryString = 'EXEC input_File_Notification ' + CAST(@PROCESS_ID AS nvarchar(10)) + ', ' + CAST(@OUTPUT_DATE AS nvarchar(10))

Then in the EXEC sp_makewebtask, put in @Query = @QueryString

And if @PROCESS_ID or @OUTPUT_DATE come from user input, do a SQL injection scrub before building them in to an executable string.

---

Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

The SP is running fine but I am not getting any data ( I mean b0 records). For some reason the process_id value is not getting selected from the cursor.

Answer 3

I'm a little confused now. Are you saying that you modified your exec of sp_makewebtask to build the string as I suggested, and now you're getting past the original error and input_File_Notification is now running but returning no rows because the cursor fetch is not putting a value into @ProcessID? If so, we'd need to see the cursor code, like the declare/open/fetch. Or maybe try just running whatever SELECT statement is in the DECLARE CURSOR by itself, just to verify that it's returning the data you expect.

One thing I didn't realize when I posted earlier is that sp_makewebtask was deprecated in SQL Server 2005, and has been removed from the current SQL Server 2008 R2 release. If you are building new code based on sp_makewebtask, I would recommend looking into alternatives. The alternative recommended in Books Online is Reporting Services. Even if you're maintaining existing code, I'd still think about using an alternative.

---

Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.