Thanks to information from sqlteam and kodyaz, I was able to export data from SQL Server to Excel in seconds (70,000 rows of data were exported from a table in 8 seconds)…Wow!!

The first step requires that you turn on the option ‘Show Advanced Options’ via the built in system stored procedure sp_configure.

?View Code T-SQL
1

exec sp_configure 'show advanced options', 1

Next, you must run ‘Reconfigure’ to “install”, as SQL Server calls it, to set the changes:

?View Code T-SQL
1

Reconfigure

Once advanced options are available, you can set the ‘Web Assistant Procedures’ option to true (i.e. 1):

?View Code T-SQL
1

exec sp_configure 'Web Assistant Procedures', 1

Again, you must run ‘Reconfigure’ to reset the SQL Server to accept your changes:

?View Code T-SQL
1

Reconfigure

SQL Server has a built-in stored procedure for writing data to files (I’m assuming this would work just as well to output a CSV or other delimited file type, but I haven’t tried it yet) called ’sp_makewebtask’. Using the following code, I was able to export an excel complete with column headers & a report title (hence the @colheaders & @resultstitle Parameters):

?View Code T-SQL
1
2
3
4
5

EXEC sp_makewebtask 
	@outputfile = 'D:\MyDirectory\vwNodeTrackingMatrix.xls', 
	@query = 'SELECT * FROM LoopManagement.dbo.vwNodeTrackingMatrix', 
	@colheaders = 1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='LoopManagement.dbo.vwNodeTrackingMatrix'

For more thorough information, you can see the links above.