Exporting to Excel Using a CLR Stored Procedure

Set up a button on the sheet with the SQL statement (like "select * from TABLE where something like '%whatever' " in cell B3. setting up the following

codes in the Macro page:

Private Sub GoQuery_Click()

Dim rs As ADODB.Recordset

Dim cmdCommand As ADODB.Command

Dim strCriteria As String

Dim vtSql

Dim fld As ADODB.Field

Range("B3").Select

Selection.Interior.ColorIndex = 3 ' high light the statement until done

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbname;Data Source=servername"

vtSql = Cells(3, 2).Value

Set cmdCommand = New ADODB.Command

Set cmdCommand.ActiveConnection = cn

With cmdCommand

.CommandText = vtSql

.CommandType = adCmdText

.Execute

End With

Set rs = New ADODB.Recordset

' Set fs = ADODB.Field

Set rs.ActiveConnection = cn

rs.Open cmdCommand

Range("B5:FF20000").Select ' aims to clear the big block for 2000 lines

Selection.Clear

' setting up the field name header

n = 2

For Each fld In rs.Fields

Cells(5, n).Value = fld.Name

n = n + 1

Next fld

numberOfRows = Cells(7, 2).CopyFromRecordset(rs)

Range("B5:FF20000").Select

Selection.Font.Size = 8

Exiting:

' Set cmdCommand = Nothing

' rs.Close

' Set rs = Nothing

' cn.Close

' Set cn = Nothing

Range("B3").Select ' reset the colour of cell B3 to signify completion

Selection.Interior.ColorIndex = 0

End Sub

ross.cecil Mr or Mrs. 500 Points: 543 More actions · Answer 4

Like most of those who post, I see this as a potentially valuable tool that could make my customers happier and my life easier. And like most, I'll reserve judgement on the security issues until I see the C# code. I've used CLR code within SQL server judiciously, after detailed review of both performance and security issues.:-)

Rob Fisk SSCommitted Points: 1861 More actions · Answer 5

alex-743051 (12/2/2009)
I just can use VBA in the Excel worksheet to perform same task:

I've used similar in the past but it does not cover the fact that with this, the destination is configurable, if your SP returns multiple results sets then the required worksheets are created and the end product is a clean, macro free spreadsheet.

In addition, the above could be used with a SQL job to run to a schedule, could be followed by a database mail execution to then mail out the generated sheet etc.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

ross.cecil Mr or Mrs. 500 Points: 543 More actions · Answer 6

Good suggestion. I use similar techniques when the user interface is an Office application (such as Excel). In many of my work situations, the user interface is a custom developed application in some .NET language and performance is always an issue. In my testing, CLR procedures can be 10 to 1000 times faster than TRANSACT-SQL or VBA code.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 7

tejwanigulab (12/1/2009)
But its of no use for somebody whose application supports the three databases MSSQL, Sybase and Oracle. I guess we don't have anything like this for Sybase and Oracle.

True to a certain extent. You could of course make a linked server, leave your stored procedure on SQL, and pull the data from those sources.

Or you could switch to SQL 😉

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 8

Anders,

Thank you for a really nice, step-by-step walk-through.

Looking forward to giving it a try.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 9

ross.cecil (12/2/2009)
Good suggestion. I use similar techniques when the user interface is an Office application (such as Excel). In many of my work situations, the user interface is a custom developed application in some .NET language and performance is always an issue. In my testing, CLR procedures can be 10 to 1000 times faster than TRANSACT-SQL or VBA code.

My experience has been both ways with the CLR. In those situations where using CLR is faster, it is usually a magnitude of 10 or more faster. One that we have developed, but still testing, shows run time on average going from 50ms to 8ms. Huge deal when this function is called about 100,000 times a day.

ross.cecil Mr or Mrs. 500 Points: 543 More actions · Answer 10

In one specific test, the stored procedure was to return a set of data (about 3000 rows) the content of which was defined by a metadata table. Each row in the metadata table specified a table or view and a field which sourced the data to be returned in the final set. The metadata was dynamic and subject to change between exections of the stored procedure. The task of returning the data set was implemented using five techniques and the time required to return 3250 rows was measured over multiple trials.

1) VB6, using ADO, one recordset returned by row of metadata - 4250 ms

2) VB6, using ADO, returning one recordset for each unique table or view in the metadata -2130 ms

3) VB.NET, using ADO.NET, one recordset per uniquie table or view - 1910 ms

4) CLR (in C#), using one read per row of metadata - 125 ms

5) CLR (in C#), using one read per unique table or view in metadata - 32 ms

This gave a 132 times improvement in this one operation which is done hundreds of times in each user interaction will the database and there are dozens of users accessing the database at the same time.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 11

The source code is attached to the article as a zip file now.

sang-lee SSC Enthusiast Points: 104 More actions · Answer 12

I haven't looked at the source code, but will this be limited to the 65K rows for excel or will it allow the 1 Million row limit for Excel 2007? I could use this for some datasets that are 500K+.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 13

sang-lee (12/2/2009)
I haven't looked at the source code, but will this be limited to the 65K rows for excel or will it allow the 1 Million row limit for Excel 2007? I could use this for some datasets that are 500K+.

Since the code has no concept of Excel, there should be no limit. If anything, a limit should be put in place to make sure you do not make Excel files larger than the limit of the Excel you are using.

Nick Hanson Grasshopper Points: 20 More actions · Answer 14

Just thought I'd chime in since my name is attached to the C# code. Theoretically, the file produced can be opened in other spreadsheet compatible programs, if they understand SpreadsheetML, and rename the file extension to something other than .xls. SpreadsheetML is a published standard.

Nick Hanson

INCREDIBLEmouse SSC Eights! Points: 835 More actions · Answer 15

sang-lee (12/2/2009)
I haven't looked at the source code, but will this be limited to the 65K rows for excel or will it allow the 1 Million row limit for Excel 2007? I could use this for some datasets that are 500K+.

I want to meet the guy who receives, opens and reads the content within million row spreadsheets.

Obviously their job needs some serious efficiency review.