question

ramkumar avatar image
ramkumar asked

Export sql table to excel sheet

I need to export sql table to excel sheet in query....Here is the query insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=E:\test\testing.xlsx;HDR=YES;', 'SELECT * FROM [Sheet1$]') select * from login_tbl error: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.". Msg 7350, Level 16, State 2, Line 2 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". I have not create excel sheet in the specified drive,eventhough i have created an excel sheet and give column values as header in excel sheet,it throws error insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=E:\test\testing.xlsx;HDR=YES;', 'SELECT * FROM [Sheet1$]') select user_name from login_tbl error: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.". 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 indicates that the user did not have the permission to perform the operation. Msg 7343, Level 16, State 2, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]".
mssqlmsdb
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ya,thnx a lot....its about file write permission to that specific folder.But how could i export sql table to create excel file with header
0 Likes 0 ·
Ya thanks,but i already had this code. To create an excel sheet we need drivers for it,but if we supposed to create excel sheet using bcp command,.xls file would be created and it can be openedbut with some warning,so that this type of excel sheet cant be opened in c# through Microsoft.jet.oledb driver
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Here's the method I use. It's a stored procedure that actually creates a CSV file with a header row but you give the file name a .xls extension. NB: This needs xp_cmdshell. if exists ( select 1 from dbo.sysobjects where id = object_id(N'dbo.dba_ExportData') and objectproperty(id, N'IsProcedure') = 1 ) drop proc dbo.dba_ExportData go set ansi_nulls on go set quoted_identifier on go create proc dbo.dba_ExportData ( @dbName varchar(100) = 'master' , @sql varchar(5000) = '' , @fullFileName varchar(500) = '' ) as /* --------------------------------------------------------------------------------------------------------------------------------------------------- Version : 1.03 Date : 23/10/2013 Purpose : Executes a query and saves the reresults to an Excel file. Credit : Copied from http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx --Test: declare @sql varchar(6800) , @dbName varchar(100) , @fullFileName varchar(100) select @dbName = 'master', @sql = 'select * from sys.databases', @fullFileName = 'c:\temp\testing.xls'; exec master.dbo.dba_ExportData @dbName, @sql, @fullFileName; --------------------------------------------------------------------------------------------------------------------------------------------------- */ set nocount on; if @sql is null or @sql = '' begin raiserror('No query supplied.', 16, 1); return; end if @fullFileName is null or @fullFileName = '' begin raiserror('No file name supplied.', 16, 1); return; end -- if DB isn't passed in set it to master select @dbName = 'use ' + @dbName + ';' if object_id('tempdb..##TempExportData') is not null drop table ##TempExportData; if object_id('tempdb..##TempExportData2') is not null drop table ##TempExportData2; -- insert data into a global temp table declare @columnNames varchar(8000) , @columnConvert varchar(8000) , @tempSQL varchar(8000); select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + substring(@sql, charindex('from', @sql)-1, len(@sql)); exec(@dbName + @tempSQL); if @@error > 0 begin raiserror('Query execution failed.', 16, 1); return; end -- build 2 lists -- 1. column names -- 2. columns converted to nvarchar select @columnNames = coalesce( @columnNames + ',', '') + column_name, @columnConvert = coalesce( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121' when data_type in ('numeric', 'decimal') then ',128' when data_type in ('float', 'real', 'money', 'smallmoney') then ',2' when data_type in ('datetime', 'smalldatetime') then ',120' else '' end + ') as ' + column_name from tempdb.INFORMATION_SCHEMA.Columns where table_name = '##TempExportData' -- execute select query to insert data and column names into new temp table select @sql = 'select ' + @columnNames + ' from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'; -- build full BCP query select @sql = 'bcp "' + @dbName + ' ' + @sql + '" queryout "' + @fullFileName + '" -Sreporting4 -c -CRAW -T'; -- execute BCP declare @ReturnCode int; exec @ReturnCode = master..xp_cmdshell @sql; if @ReturnCode <> 0 begin raiserror('xp_cmdshell failed (return code was %i).', 16, 1, @ReturnCode); return; end if object_id('tempdb..##TempExportData') is not null drop table ##TempExportData; if object_id('tempdb..##TempExportData2') is not null drop table ##TempExportData2; go
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Apologies. That looks much better in my script than it does here. But the code box choked on it and when I tried to attach the .SQL file it said that was invalid file type!
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Just a bit of a guess but is it just Windows permissions? Does the SQL Server service account have write permission on the target folder (E:\test)?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.