Create Store Procedure with an input parameters from a report and create excel file with the result set

Question

So I want to build a store procedure with input parameters, the user will enter the input values using a reporting tool(Microstrategy) then I will build that logic to extract that to an excel or flat file to a destination folder for the user. I'm going to use SSIS to create the file or I can do that in the store procedure if so how will I do that and also I want to have something like a message box that will say your file is now ready in the destination folder. Below is the input store procedure I came up with:

create PROCEDURE dbo.uspGetReportData
@Startdate Date = NULL,
@Enddate DATE = NULL,
@FileID BIGINT = NULL,
@ClientName VARCHAR(50) = NULL

AS
BEGIN
SET NOCOUNT ON;
SELECT
o.FileID,
dl.AddressLine1 AS [PropertyAddress],
dl.City,
dl.[State],
dl.Zip,
c.ClientName,
o. OrderDate

FROM [dbo].[Orders] o
LEFT JOIN dbo.OrderLoan dl ON dl.OrderLoanID = o.OrderLoanID
LEFT JOIN dbo.Client c ON c.ClientID = o.ClientID

WHERE orderDate >= ISNULL(@Startdate,orderDate)
AND orderDate <= ISNULL(@Enddate,orderDate)
AND FileID IN( ISNULL(@FileID,FileID))
AND c.ClientName IN ( ISNULL(@ClientName,ClientName))

END

Answer 1

WHERE orderDate >= ISNULL(@Startdate,orderDate)
AND orderDate <= ISNULL(@Enddate,orderDate)
AND FileID IN( ISNULL(@FileID,FileID))
AND c.ClientName IN ( ISNULL(@ClientName,ClientName))

Watch out! If any of these columns are nullable, this may not work as you presumably intend. Better write this as:

WHERE (orderDate >= @Startdate OR @Startdate IS NULL)
 AND (orderDate <= @Enddate OR @Enddate IS NULL)
 AND (FileID = @FileID OR @FileID IS NULL)
 AND (c.ClientName = @ClientName OR ClientName IS NULL)

I also replace the IN operators with = to make the code more straightforward.

As for creating the Excel file, don't do that from the stored procedure. Your DBA may not agree to change that configuration parameter that Melissa saw you. Furthermore, I have seen so many posts over the years where people have tried it, and it has failed with incomprehensible error messages.

Answer 2

One option would be to store the result in a table, from where you can pick it up with SSIS. Since there may be more than one of these procedure, you could store the result as XML or JSON, using the FOR XML or FOR JSON clauses.

Then again, have you explored what abilities that Microstrategy has in this area? It sounds awkward with a starting a stored procedure from a reporting tool, and then have SSIS to pick up the data to produce a file. (I'm not familiar with Microstrategy myself.)

Answer 3

@MelissaMa-MSFT when i create the blank excel file at the directory should it have the column names in it?

Answer 4

Even from SSIS - you will find that creating an Excel file can be a challenge. It is much easier to create a CSV file that can be opened with Excel - which can be done using SSIS or Powershell.

Since you want something that is interactive - I would recommend using Powershell. However, that all depends on how you are going to get the parameters from Microstrategy to the stored procedure.

Answer 5

Hi @pizaro ,

Welcome to Microsoft Q&A!

The Transact-SQL OPENROWSET could be used to export SQL Server data to an Excel file via SSMS.

First create a blank excel file at the directory of your choosing(for example: C:\SQL\test.xlsx).

Then create procedure like below:

create PROCEDURE dbo.uspGetReportData  
@Startdate Date = NULL,  
@Enddate DATE = NULL,  
@FileID BIGINT = NULL,  
@ClientName VARCHAR(50) = NULL  
  
AS  
BEGIN  
SET NOCOUNT ON;  
  
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;  
Database=C:\SQL\test.xlsx;','SELECT * FROM [sheet1$]')   
SELECT  
o.FileID,  
dl.AddressLine1 AS [PropertyAddress],  
dl.City,  
dl.[State],  
dl.Zip,  
c.ClientName,  
o. OrderDate  
FROM [dbo].[Orders] o  
LEFT JOIN dbo.OrderLoan dl ON dl.OrderLoanID = o.OrderLoanID  
LEFT JOIN dbo.Client c ON c.ClientID = o.ClientID  
WHERE orderDate >= ISNULL(@Startdate,orderDate)  
AND orderDate <= ISNULL(@Enddate,orderDate)  
AND FileID IN( ISNULL(@FileID,FileID))  
AND c.ClientName IN ( ISNULL(@ClientName,ClientName))  
  
END  

Configuring this can be tricky and dependent on your level of permissions. Make sure you have the correct Linked Server/Provider installed (‘Microsoft.ACE.OLEDB.12.0’) And check your Database user settings to this server .

But, when executing the above code, the following error may occur:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

To recolve above issue, you could execute below:

EXEC sp_configure 'show advanced options', 1  
RECONFIGURE  
EXEC sp_configure 'Ad Hoc Distributed Queries', 1  
RECONFIGURE  

You may also face below issue:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.
Msg 7303, Level 16, State 1, Line 1

This usually happens because of inadequate permissions.

If you face another issue as below:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

You could open excel file and enter the column names which will represent the column names from the table.

In addition, you could refer more details about this or other methods including SSIS in this link.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.