Is there a way in sql 2008 stored procedure to dump data to a file in CSV with column headers? Do i have to use BCP? I used to use openrowset for this but there are no jet drives on 64bit versions of sql 2008.
I don't want to do this from a command line using sqlcmd It has to be done in the stored proc.
64bit Driver:
Microsoft Access Database Engine 2010 Redistributable
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
Details on how to use here:
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/33436d82-085c-43e4-b991-a2d0d701c8fc
Answer from Link:
There is now a 64bit driver available, you can download it here:
Microsoft Access Database Engine 2010 Redistributable http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
This will register a driver which is listed under Server Objects -> Linked Servers -> Providers with the name "Microsoft.ACE.OLEDB.12.0" which you must use as the Provider string.
Connection string for 64-bit OLEDB Provider: For CSV / Text files, Add "Text" to the Extended Properties of the OLEDB connection string. Important: With the new 12.0 driver and text files the schema.ini file is compulsory in the directory of the csv/text file, otherwise you will receive a "Could not find installable ISAM" error. schema.ini documentation can be found here: http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
If you are connecting to Microsoft Office Excel data, add “Excel 14.0” to the Extended Properties of the OLEDB connection string.
You can check this example:
http://granadacoder.wordpress.com/2009/12/22/sql-server-data-to-a-jet-database/
I am exporting to a Jet database (aka, "Microsoft Access").
You can probably convert to Excel fairly easily.
But the code would be able to be put into a stored procedure.
We have used this code successfully in a number of instances:
http://prosqlserver.com/blog/2011/07/02/sqlclr-procedure-to-export-query-sp-results-into-csv/
