Question:
How can a table be exported from SQL Server to Excel?
Solution:
To do so, follow these procedures:
1. Create a spreadsheet whose first row works as a column reader.
2. Create a Query in Elipse E3, executing SQL’s OPENROWSET method to open a connection to the desired Excel spreadsheet. Then, use SELECT method to select the columns in the table. Example:
SELECT E3TimeStamp, Nivel1, Nivel2 FROM Niveis
If the Server is not set up to allow executing AD Hoc queries, the following access error can be generated: “SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’… “. To fix this, you must execute the code below in a query at the SQL Server Studio:
RECONFIGURE;
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
In case it generates an error informing you that it was not possible to create an instance of OLE DB 12 server to the bound server, you will need to download the AccessDatabaseEngine.exe application, which contains the Microsoft.ACE.OLEDB.12.0 driver.
If this issue persists, execute the code below in a query at the SQL Server Studio:
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′,N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′,N’DynamicParameters’, 1
GO
NOTE: This demo application was developed with SQL Server Express 2008 R2 and Microsoft Excel 2010.
Attachment:
