export data to Excel using sql stored procedure

http://stackoverflow.com/questions/8740280/sql-server-stored-procedure-export-to-excel-workbook-with-multiple-sheets[^]


http://stackoverflow.com/questions/87735/how-do-you-transfer-or-export-sql-server-2005-data-to-excel[^]

--To check the driver is installed or not open ODBC Data Source Administrator (Start > Run > type ODBCAD32.EXE and hit enter) and check under the Drivers tab
SET NOCOUNT ON;

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
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

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
           'Data Source=C:\upload_test.xlsx;Extended Properties=Excel 12.0')...[Sheet2$]
SELECT Columns FROM table_Name
-- Sheet Should be already Present along with headers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

hi Raj

Thanks code for export excel. I have tried and this sp export only one column, it works fine. If I add multiple column, like code, name, address,

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
           'Data Source=D:\abcReports\importdata\Book1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT code,name,address FROM dbo.tbl_temp

it shows the error

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Pls do you have any idea to export Multiple column

Thank you

maideen