Use the CLR Stored Procedure to conveniently and quickly export data to excel

I saw an article this morning.Article("Href =" http://www.sqlservercentral.com/articles/SQLCLR/68842/ "target =" _ blank "> exporting to excel using a CLR Stored Procedure) I think it is very practical to share with you.
This article mainly provides a quick and convenient way for SQL Server to export data to excel. If you often need to export data from the database to excel in your daily work, you will find this useful to you.

Let's just talk about how to use it:
1. DownloadCodeExcelexport.zip
2. compile the project and copy the generated DLL to a directory, for example, C: \ CLR.
3. Enable CLR on SQL Server

Sp_configure ' CLR ' , 1
Reconfigure

4. Run the alter database command to open trustworthy database attributes.

Alter database name set trustworthy on

5. CreateProgramSet

Create Assembly exporttoexcel
From ' C: \ CLR \ excelexport. dll '   -- Where CLR assembly is stored
With permission_set = External_access

6. Create a stored procedure and associate it with the Assembly created in the previous step.

Create procedure [DBO]. [prc_exporttoexcel]
@ Proc [nvarchar] (100 ),
@ Path [nvarchar] (200 ),
@ Filename [nvarchar] (100 ),
@ Params XML
As
External name [exporttoexcel]. [storedprocedures]. [exporttoexcel]

7. Now, the preparation is complete. The following is an example:

Declare @ Params XML
Set @ Params =   ' <Params> <Param name = "lastname" value = "Smith"/> <Param name = "country" value = "us"/> </Params> '
Exec prc_exporttoexcel ' Procname ' , 'Drive: \ directory \ ' , ' Filename ' , @ Params

A) The first parameter is the name of the stored procedure for obtaining data.

B) The second parameter is the output directory.

C) The third parameter is the output file name without a suffix.

D) The fourth parameter is required for the data stored procedure. Name indicates the parameter name, and value indicates the parameter value.

If the stored procedure for obtaining data has no parameters, use: Set @ Params = '<Params> </Params>'