Windows service that can be used to run reports from SQL Server and persist the files to disk. Reports are run from a queue and saved to disk in a variety of formats. This can be useful in preventing too many reports running simultaneously, and save on network traffic in the case of SSRS reports.

Assuming the application is correctly installed and started as a Windows service:

1. Application loads up all connection strings, and for each connection string:
2. ConcurrencyService determines how many reports are running for the current server/host
3. If maximum allowed limit reached, sleep and return to step 1
4. Queries "ReportQueue" table (see config items) and fetches N reports that:
   1. have not been processed
   2. are not currently running per the ConcurrencyManager
   3. have not experienced an error
   4. have a ScheduleDate of NULL or less than or equal to the current UTC date/time
   5. where N is the maximum number of allowed reports minus the number of reports already running as per the ConcurrencyManager
5. Each unprocessed report from the ReportQueue is run on a background thread as follows:
   1. Report is added to the ConcurrencyManager
   2. ReportType is determined (StoredProcedure, SQL, Future: SSRS)
   3. Command is analysed depending on ReportType
   4. Parameters are loaded (applicable to all report types, even SQL as sp_executesql is used to run the text)
   5. StartTime is updated on ReportQueue to the current UTC date/time, and Status is set to "Processing"
   6. Report is executed
   7. Once data returns from the database server, the report is saved to disk in the format specified in the "ReportFormat" column
   8. On report completion:
      1. If report runs successfully, EndTime is updated on ReportQueue to the current UTC date/time, and Status is set to Complete
      2. If report fails, Status is set to Error and ErrorDetails column is updated with error information and the associated stack trace
   9. Report is removed from ConcurrencyManager as a processing report
6. Application sleeps for configured time period before polling again (goto Step 1)

The following source commands can be used:

1. Raw SQL statements (SQL)
2. Stored procedures (StoredProcedure)
3. SSRS Reports (SSRS)

SqlServerReportRunner supports the following file output formats:

1. CSV - Comma Separated Values
2. Delimited - Text-delimited file, where any delimiter can be specified
3. Excel - OpenDocument .xlsx format
4. NULL - This option is available if you wish to execute a report where you take control of the output e.g. via BCP. When using this option, all the Output* fields should be set to NULL, and no file will be generated by the service.

Note that the above applies to commands executed for SQL and StoredProcedure commands only. For SSRS reports, output format is determined by the format specified in the URL.

The program will export data into these files using the locale of the machine on which it is running, or the locale specified in the GlobalizationCulture configuration setting. However, the file format must also be taken into account.

1. CSV/Text-delimited - all values will be output using the current locale (or the GlobalizationCulture configuration setting if this has a value)
2. Excel - Excel itself will use the current locale to display all values, as data values are passed through to Excel using the same data types returned by from the database. The only exception to this is dates. If you are using Excel as an output option, then you must specify your default date/time format in the application configuration setting "ExcelDefaultDateTimeFormat". This is because the underlying component requires a format to be specified - if this is not done you may end up with numbers displayed in date/time cells.

If you want to completely override the output values you can always format the data in the source query. Just be careful if you convert values to string data types (e.g. varchar) if you are using Excel, as they will be written to Excel as text (as opposed to a number or a date).

Dates in YYYY-MM-DD format, converted to string:

SELECT CONVERT(char(10), GETDATE(), 126) AS MyDate

Dates in YYYY-MM-DD HH:mm:ss format, converted to string:

SELECT CONVERT(CHAR(19), CONVERT(DATETIME, GETDATE(), 101), 120) AS MyDate

Numbers in 0.00 format:

SELECT FORMAT(MyMoneyField, '0.00') AS MyNumber

Round numbers to 2 decimal places:

SELECT ROUND(MyMoneyField, 2) AS MyNumber

In order to run a report against a database that has been configured for the SqlServerReportRunner, you need to insert a record into the

DECLARE @xml XML 
SELECT @xml = (
	select 1 AS Param1
	, '29' AS Param2 
	, '2017-02-01' AS DateParam 
	for XML PATH, type)

DECLARE @fileName varchar(255) = 'MyFileName.txt'
INSERT INTO ReportJobQueue
	(
	ReportName
	, CommandType
	, Command
	, [Parameters]
	, OutputFileName
	, OutputFilePath
	, OutputFormat
	, Delimiter
	, UserName
	, EmailAddress
	, [Status]
	, CreateDate
	, ScheduleDate
	, Priority
	, SingleExecutionGroup
	)
VALUES
	(
	'Test Report'					-- the name of the report - for your usage
	, 'StoredProcedure'				-- SQL, StoredProcedure or SSRS
	, 'dbo.MyStoredProcedure'			-- the actual command (will be a SQL query, the name of a stored procedure, or a full URL for SSRS)
	, @xml						-- the actual parameters as an XML data type
	, @fileName					-- the name of the output file
	, '\\' + @@SERVERNAME + '\Test\Reporting'	-- the location of the output folder (UNC)
	, 'Delimited'					-- CSV, Delimited or Excel
	, '|'						-- only used in the case of Delimited files, can be an string value
	, 'matt'					-- the name of the user (optional)
	, 'matt@test.com'				-- the email address of the user (optional)
	, 'Pending'					-- status of the report
	, GETUTCDATE()					-- date the report is created (must be UTC)
	, DATEADD(minute, 2, GETUTCDATE())		-- when you want the report to run (leave as NULL for immediate execution) (must be UTC)
	, 1						-- set the priority for the report, where 1 is higher priority than 2 (optional)
	, NULL						-- if set, the SingleExecutionGroup value will prevent the report from running concurrently with other reports with the same value in this field (optional)
	)

Support for SSRS is relatively crude - the engine effectively just executes a web request to the report server and saves the stream returned to disk. As such, it is up to you to specify an output format that corresponds to the file name provided. Output formats available will depend on the extensions installed on your SQL Server instance:

Microsoft documentation here: https://docs.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-2017

For example, to create an Excel file (xlsx) from your report server, the insert statement would be constructed as follows:

INSERT INTO ReportJobQueue
(
       ReportName
       , CommandType
       , Command
       , OutputFileName
       , OutputFilePath
       , UserName
       , [Status]
       , CreateDate
)
VALUES
(
       'Matt Test'
       , 'SSRS'
       , 'http://myserver/ReportServer/Pages/ReportViewer.aspx?%2fIT%2fTest_Report&rs:Command=Render&startdate=2010-01-01&enddate=2019-01-31&rs:Format=EXCELOPENXML'
       , 'MattTest.xlsx'
       , '\\fileserver\Test'
       , 'MyDomain\matt'
       , 'Pending'
       , GETUTCDATE()
)

The report runner provides two options for helping you set the importance and concurrency of reports:

Priority: If set, the engine will run reports in ASCending order using this value, where a NULL value is always seen as low priority. A priority 1 record will always run BEFORE a priority 2 record, even if it was inserted after the latter. SingleExecutionGroup: If set, the engine will ensure that any reports with the SAME value in this field do not run at the same time. NULL values are ignored.

1. Copy all binary files into a folder on the server that will be processing the reports (not necessarily the database server)
2. Open the "SqlServerReportRunner.exe.config file, and:
   1. Under the connectionStrings section, add connection strings for each server/database pair that will host a ReportQueue table
   2. Set application configuration options in the appSettings section (See Configuration Options section). Save and close the file.
3. Run the SQL Script "scripts/ReportJobQueueTable.sql" on any database that will host a ReportQueue table
4. (Optional) Configure NLog.config file to add logging. By default, the application will log to a text file in the application folder, but the service can be configured to (for example) log errors via email if you have access to a mail server
5. Open a console in Administrator mode and change the current directory to your installation folder
6. Run "SqlServerReportRunner.exe install"
7. To start the service, run "SqlServerReportRunner.exe start", or go to the Services control panel and start the service

Below are some queries that are useful for looking at your reporting statistics.

-- Get average execution time for all reports since a specified date
SELECT
	ReportName
	, COUNT(*) AS ExecutionCount
	, ROUND(CAST(AVG(DATEDIFF(ss, ProcessStartDate, ProcessEndDate)) as float) / 60, 2) AS AverageTimeInMinutes 
FROM ReportJobQueue 
WHERE Status <> 'Error'
AND ProcessEndDate IS NOT NULL
AND ProcessStartDate > '1 January 2018'
GROUP BY ReportName

SqlServerReportRunner comes by default with a basic dashboard that can be used to view stats for the connections configured for the application.

Access to the dashboard is by default on port 8832 on the machine where the service is installed, although this can be configured by adjusting the ConsolePort value in the application configuration file.