Problem
Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options are available to do so? Do I need to learn another tool or can I use some T-SQL commands? Does SQL Server 2005 offer any new options to enhance this process?
Solution
Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP). Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.
Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
Source – SQL Server 2005 Books Online
Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO
Using the OPENROWSET command creates two caveats. The first caveat is the need to have an Excel spreadsheet serve as a template in the needed directory with the correct worksheets and columns. Without this the you would receive an error message. The second caveat is that it is necessary to enable the OPENROWSET command with the SQL Server 2005 Surface Area Configuration utility. Note – This step is not needed for SQL Server 2000. With the loop example you could copy and paste the Excel spreadsheet and load the data as needed.
Although the example above is very simple, you could integrate this simple example into your code. For example, if you had the need to write out a number of reports in the same format, you could loop (WHILE loop or Cursor) over the records and write them out to different Excel spreadsheets based on the name or report type. In addition, you could integrate this code with either SQL Server 2000 mail or Database mail (SQL Server 2005) and mail the results to the users with very little effort and build the process entirely with T-SQL.
Next Steps
- Depending on how your users analyze data and the software infrastructure you have in place, Excel may be a simple and easy solution for your users to receive their data in a familiar interface for them to conduct the needed analysis. I am surprised by the sophisticated Excel infrastructure at some organizations, but these sorts of applications seem to grow organically.
- The next time you are faced with exporting SQL Server data to Excel take a step back and understand the requirements then determine which of the following options best meets the need:
- Data Transformation Services
- SQL Server Integration Services
- Bulk Copy (BCP)
- OPENROWSET
- Check out the following MSSQLTips.com tips related to data import\export:
