June 10, 2012

Export data to Excel file

Here's a T-SQL query that uses OPENROWSET to export data from SQL Server to an Excel file:

You may need to grant permissions to the temp directory used by the service account running SQL Server or the Windows directory the export file is being written to from the cmd line as follows:

> icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Temp /grant "NT Service":(R,W)
> icacls C:\Temp /grant "Local Service":(R,W)

(substitute the system volume and directory names as appropriate)