Click here to Skip to main content
14,739,205 members
Please Sign up or sign in to vote.
2.25/5 (3 votes)
See more:
I'm having a Sql Table which contains 1000 records...Now i want to export this records to excel sheet by using stored Procedure...Please any One Help me...
Posted
Updated 27-Apr-20 20:34pm
Comments
Boothalingam 3-Dec-13 5:22am
   
No,i dont want this as a coding,Using Sql how to export into excel

   
Comments
Boothalingam 3-Dec-13 5:40am
   
EXEC master.dbo.xp_cmdshell 'bcp Test.dbo.Login out C:\bcp\SomeFile.csv -S @@servername -T -t, -r\r\n -c'

CSV file doesnt contains any data
--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
   
Comments
Member 8732801 25-Feb-16 0:44am
   
Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\abcReports\importdata\Book1.xlsx;Extended Properties=Excel 12.0','select code, name, address from [sheet1$]')
SELECT code,name,address FROM dbo.tbl_temp
Maideen Abdul Kader 25-Feb-16 2:17am
   
Pls check the link

http://www.aspsnippets.com/Articles/Export-data-from-SQL-Server-to-Excel-in-ASPNet-using-C-and-VBNet.aspx

It will help you...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900