how to export stored procedure data with headers into a xls file in sql server?

Question

Hi every MS Sql server user .

I have a stored procedure in my server that make a lot of join and in the result shows them.

Now I want to make a job in the server and this job should run this SP and copy the results into a xls file.I have seen a lot of topics and article about this but they didn't solve my problem.

with this code I do that but I @filename in the query get error.

declare @filename nvarchar(100)
set @filename =concat('d:\JointHistory-',CONVERT(nvarchar(12), getdate(), 105),'.csv')
print @filename
exec master..xp_cmdshell N'sqlcmd -s, -W -Q "set nocount on; EXEC [dbo].[JointHistory_TO]" | findstr /v /c:"-" /b > "'+@filename+'"'

Answer 1

Put param string into a variable first, then execute xp_cmdshell with that variable. Like this

declare @filename nvarchar(100)
declare @cmd SYSNAME
set @filename =concat('d:\JointHistory-',CONVERT(nvarchar(12), getdate(), 105),'.csv')
print @filename
SET @cmd = N'sqlcmd -s, -W -Q "set nocount on; SELECT 1" | findstr /v /c:"-" /b > "' + @filename+ '"'
exec master..xp_cmdshell @cmd

Answer 2

You say you want an .xls file and then you create a .csv file?

In any case, SQL Server is not a file-export tool. If you want to make this a job, make it a CMDExec or PowerShell step and run SQLCMD directly.

If you actually want an XLS file, you can drive this from PowerShell directly. Here is a demo script that I have that runs a query and writes to an Excel file:

using namespace System.Data;
using namespace System.Data.SqlClient;
$excel = New-Object -ComObject Excel.Application;
$book = $excel.WorkBooks.Add();
$sheet = $book.WorkSheets.Item(1);
$connstr = "Database=Northgale;Integrated Security=SSPI;Server=.";  # Beware to change the Server name to your environment!
$cn = New-Object SqlConnection $connstr;
$cn.Open();
$cmd = New-Object SqlCommand(
       "SELECT TOP 1000 * FROM dbo.Customers", $cn);
$cmd.CommandType = [CommandType]::Text;
$da = New-Object SqlDataAdapter($cmd);
$dt = New-Object DataTable;
$nrows = $da.Fill($dt);
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew();
$ncols = $dt.Columns.Count;
$values = [System.Object[,]]::new($nrows, $ncols);
$rowno = 0;
foreach ($row in $dt.Rows) {
   $colno = 0;
   foreach ($item in $row.ItemArray) {
      $values[$rowno, $colno++] = $item.ToString();
   }
   $rowno++;
}
$sheet.Range($sheet.Cells(1, 1),
             $sheet.Cells($nrows, $ncols)) = $values;
$stopwatch.Stop();
[console]::WriteLine("Duration {0:F2} seconds.",
    $stopwatch.Elapsed.TotalSeconds);
$cmd.Dispose();
$cn.Close();
$cn.Dispose();
$excel.Visible = 1;    # DON'T INCLUDE THIS LINE IN AGENT JOB!

Note that since is for demo purposes, I don't actually save the file. That is left as an exercise to the reader. As is adding the column headers.

Answer 3

Hi @Farshad Valizade

I want to make a job in the server and this job should run this SP and copy the results into a xls file.

If you are interested in SSIS, here are the step-by-step instructions to pass output of a stored procedure as input to file system task.

1. Create an object variable (User::objResults).
2. Create an Execute SQL Task
   • In General tab
   1. Add the name of the stored procedure
   2. Set ResultSet (single row or full resultset)
   • In the Result Set tab
   1. Add new item ResultName is 0
   2. Set VariableName as User::objResults
3. Attach your Foreach Loop Container to the end of the `Execute SQL Task and set the enumerator as ADO Enumerator.

Refer to this similar thread for more details: how to pass output of a stored procedure , as input to file system task in ssis to move a file.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".