SQLCLR Procedure to export query / SP results into CSV

We as database developers, many times have to export data into csv files and send them across to the Business users. The data the needs to be exported can be a retrieved by executing an adhoc-query or a stored procedure based on the users requirements. In this article we will look at a SQL CLR Stored procedure which can be used to export data into CSV from within the Database

The code of the CLR looks as below:

Now we will go through the code:

The SP declaration

  1. Query – The adhoc query / SP that needs to be executed to get the required data.
  2. FileSpec – The file directory path along with the filename where data needs to be exported. This path is local to the SQL Server itself and not the client machine.
  3. OverwriteIfExists – A flag to say whether to overwrite the file. 1 = true, 0 = false.
  4. HeaderRow – Flag to export column header in the file. 1 = true , 0 = false

Preliminary checks

Checks to see whether the query param is set, filepath is valid, filename is passed, file already exists and such other checks.

Query the DB and exporting the data to file

Next we open connection to the DB and populate a datatable. After that we export the datatable to the file.

SQLCLR Deployment

Now once you have written the code build the solution and deploy it to SQL Server. BIDS can automatically deploy the code to sql server or you can do it manually using the following script.

Sample RUN