Exporting SQL Server Records To Excel

We have a large amount of data that we want to provide to our users through Excel files downloaded from our website. We found that by using the Openrowset command in a stored procedure afforded us the most versatility and the easiest redundant execution than the other options. For a nice explanation of the process and a full listing of the variables available take a look at MSSQLTips.

The final code looks like this:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT * From dbo.MyTable Where Date = GetDate()
GO

There was a slight error with this implementation at one point in the day where we received this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Cannot open database ''.

A number of sites point you to the temp folder of the Administration login on the SQL Server. This is a good place to check read/write privileges for the account you are logged in as. But we found that restarting the SQL Server Agent correct the issue.