Hey there! Let's work through your problem step by step. First, a quick heads-up: sp_makewebtask is a deprecated feature in SQL Server—so it's not the most reliable or future-proof tool for this job. Let's cover better approaches to export your stored procedure's two result sets to separate Excel worksheets, plus fix that parameter issue you ran into.

First, Fixing Your Original sp_makewebtask Code (If You Still Want to Use It) #

Just for context, your original code had syntax errors. The @query parameter needs to be a complete, valid SQL string that calls your stored procedure correctly. Here's the corrected version:

EXEC sp_makewebtask 
    @outputfile = 'C:\Users\me\Documents\testing.xls', 
    @query = N'EXEC ExportAsExcel @id = 123',  -- Fixed the query string formatting
    @colheaders = 1, 
    @FixedFont = 0,
    @lastupdated = 0,
    @resultstitle = 'Testing details'

But keep in mind: this will dump both result sets into a single worksheet, which doesn't meet your requirement of separating them. So let's move to better, purpose-built solutions.

Recommended Approach 1: PowerShell (Flexible, Easy to Automate) #

PowerShell is perfect for this scenario—it can capture multiple result sets from a stored procedure and write each to its own Excel worksheet with minimal setup.

Step 1: Install Required Modules #

First, install the modules needed to query SQL Server and write to Excel:

Install-Module -Name SqlServer -Scope CurrentUser
Install-Module -Name ImportExcel -Scope CurrentUser

Step 2: Export Script #

Create a PowerShell script (e.g., Export-SprocResults.ps1) with this code:

# Define your configuration
$sqlServer = "YourServerName\YourInstance"
$database = "YourDatabaseName"
$sprocName = "ExportAsExcel"
$idParam = 123
$excelPath = "C:\Users\me\Documents\testing.xlsx"

# Run the stored procedure and capture both result sets
$results = Invoke-SqlCmd -ServerInstance $sqlServer -Database $database `
    -Query "EXEC $sprocName @id = $idParam" -OutputAs DataTables

# Write each result set to a separate worksheet
$results[0] | Export-Excel -Path $excelPath -WorksheetName "First Result Set" -AutoSize
$results[1] | Export-Excel -Path $excelPath -WorksheetName "Second Result Set" -AutoSize -Append

This script grabs both result sets and saves them to distinct sheets in your Excel file.

Recommended Approach 2: SSIS (For Repeatable, Enterprise Tasks) #

If you need to run this export regularly, SSIS (SQL Server Integration Services) is a robust, scalable option.

Step 1: Set Up Connections #

1. Open SQL Server Data Tools (SSDT) and create a new Integration Services project.
2. Add an OLE DB Connection Manager for your SQL Server database.
3. Add an Excel Connection Manager pointing to your target Excel file (choose "Excel 2016" or newer format).

Step 2: Configure the Stored Procedure Source #

1. Drag an OLE DB Source onto the design surface.
2. In the editor:
   • Select your SQL Server connection.
   • Choose "SQL command" as the data access mode, then enter:

     EXEC ExportAsExcel @id = ?
     

   • Click Parameters to map the ? to your @id parameter (set the value to 123, or use a package variable for dynamic inputs).
   • Go to the Result Set tab, set "Result set" to Full result set, and assign a variable (e.g., User::AllResults) to store the output.

Step 3: Split and Write to Excel #

1. Add a Script Task to the package, and pass the AllResults variable as a read-only input.
2. In the script (C# or VB.NET), iterate over each result set in the variable and write them to separate worksheets using the Excel connection manager.
3. Alternatively, use two separate OLE DB Source components (each calling the stored procedure) and map each to a distinct Excel Destination pointing to different worksheets.

Recommended Approach 3: Temporary Tables + OPENROWSET (Pure SQL, One-Time Tasks) #

If you prefer a pure SQL solution for a one-time export, use temporary tables to capture each result set, then export them to Excel:

Step 1: Capture Results to Temp Tables #

First, create temp tables that match the schema of your stored procedure's result sets:

-- Match the schema of your first result set
CREATE TABLE #TempResult1 (
    Column1 INT,
    Column2 VARCHAR(100),
    -- Add all other columns from the first result set
)

-- Match the schema of your second result set
CREATE TABLE #TempResult2 (
    ColumnA DATETIME,
    ColumnB DECIMAL(18,2),
    -- Add all other columns from the second result set
)

-- Capture the first result set
INSERT INTO #TempResult1
EXEC ExportAsExcel @id = 123

-- Note: To capture the second result set, you'll need to modify your stored procedure to output it to a separate temp table, or use a CLR procedure. This method has limitations for multi-result sets unless you can adjust the stored procedure.

Step 2: Export to Excel #

Use OPENROWSET to export each temp table to a separate worksheet (you'll need the Microsoft ACE OLEDB driver installed):

-- Export to Sheet1
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\Users\me\Documents\testing.xlsx;',
    'SELECT * FROM [Sheet1$]')
SELECT * FROM #TempResult1

-- Export to Sheet2
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\Users\me\Documents\testing.xlsx;',
    'SELECT * FROM [Sheet2$]')
SELECT * FROM #TempResult2

内容的提问来源于stack exchange，提问作者bjjrolls