Output a query as an Excel file

Here is an adhoc method, to run when you want ... uses just the basics

http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/

Thanks mister.magoo and bitbucket! Wow, I didn't know it was so complex to do that! SQL Server being a Microsoft product (same as Excel), I believe there should have been an built-in procedure to do the export!

I've tried a copy/paste of the query result and it seems to work till now. I will need to go through the excel file to see if it evrything's ok.

The built-in procedure is called the import/export wizard. (which is basically SSIS behind the scenes)

I don't know whether the following might meet your needs: Create an ODBC connection to Sql Server using ODBC manager within Excel, then use the Data Import menu in Excel to create the sql query and retrieve results. I prefer ODBC because it allows you to use parameters. The process is quite straightforward.

From 2003, Excel files are just XML documents (as are all office documents)

so you could generate the xls or xlsm file dynamically - its quite a bit of work to set up .xls headers and footers but may be worth it if you need to do a lot of reports. One nice thing about this solution is that you can embed Excel formulas and conditional formatting in the spreadsheet output.1

SSIS is another alternative, but in my experience Excel and SSIS do not play nicely together, especially if you are developing in 32 bit and deploying on 64 bit. There are so many ways it can go wrong and the error messages are even more unhelpful than ususal (which you would have through would be pretty hard :-D)

Pulling data from SQL using the Excel application is probably the simplest and safest solution, provided your network permissions allow it. SSRS reports saved as Excel is another option - and probably the best if you have sharepoint installed.

Perhaps the most pragmatic approach would be to output the SQL results set FOR XML and then import the XML into Excel. If you provide a DTD then Excel is pretty good at parsing XML and producing decent looking reports.

Whatever option you choose, it will require an intermediate-advanced knowlege of the technology stack but is definately worth the investment in time and effort.

For one-off stuff I do for myself, I cheat and use sp_Send_DBMail to create a tab delimited file named like it is an Excel spreadsheet.

DECLARE @Delimiter Char(1)

SET @Delimiter = CHAR(9)

EXEC MSDB.dbo.sp_Send_DBMail

@Recipients='My.Email.Address@company.com',

@Subject='Some Audit',

@Body='Attached is some audit information.',

@Query='SELECT Left(Name, 50) AS Name, Left(Description, 50) AS Description FROM MSDB.dbo.SysJobs',

@Attach_Query_Result_As_File = 1,

@Query_Result_Header = 1,

@Query_Attachment_Filename = 'Audit.xls',

@Query_Result_Separator = @Delimiter

I know this is old, but I needed a refresher and came across this. I've found it's better not to do the query in the sendmail. Export the file like this:

Execute xp_cmdshell 'SQLCMD -S VSQL2 -d urdatabase -U urname -P urpassword -Q "Select * from urfilename" -o C:\urexportedfile.csv -W -w 1500 -s","'

then just do the sendmail with an attachment- no query needed:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'urSQLemailprofile',

@recipients = 'anyone@anywhere',

@subject = 'blah blah blah',

@body = 'more blah ',

@file_attachments= 'C:\urexportedfile.csv'

End

If your Windows Default Programs are set for Excel to open .csv files, it will open purty as you please.

A possible, easy and repeatable option: connect to SQL via Access (ODBC, for example).In SQL, create a View that has your query results.Access attaches to Views and the results can be copied/pasted or exported (via Access) to Excel.

Certainly a viable option, but the above code is part of a stored procedure, the first part of which populates a table which is then exported. The proc is called by an agent job which runs twice a day. I don't have to touch it anymore. Schweet!