Export a SQL Server 2005 table to an Excel file

I've been trying to implement an option on my application to export a database table from SQL Server 2005 to an excel .XLS file, with the name and location of the excel file specified by the user.

The best way I could come up with was to create an SSIS package to export the file using Tasks / Export Data on SQL Server and save this as a file. In my application I search and replace the name of the output file with the name specified by the user, save this as a new package and then run dtexec on the new package to create the Excel file.

Does anyone know if there is a better and more robust way of doing this?

Could not allocate ancillary table for view or function resolution

I received the error message "Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded."

This was because I had created a chain of views, many of which used the "union" SQL statement.

e.g. view 1, 3 queries joined with the union statement
view 2, 2 queries
view 3, 5 queries
view 4, 4 queries
view 5, 3 queries

3 * 2 * 5 * 4 * 3 is greater than 256, and caused this error. Rewriting some of the views to use CASE statements in the select statement, rather than the union statement rectified the problem.

C# COALESCE operator

A useful function in T-SQL is COALESCE, which takes two arguments and returns the first if it is not null and the second if the first is null.

I found out today about a feature in C# 2.0 that I had somehow missed which does the same thing (via Avner Kashtan's blog).

String a = null;
String b = "abc";
String c = a ?? b;

returns "abc".

These can also be chained, so the first non-null argument is returned, so

String a = null;
String b = null;
String c = "abc";
String d = a ?? b ?? c;

returns "abc".

It's a lot easier to read than the alternative:

String d = (a != null ? a : (b != null ? b : c))