How to Export Table sql to Excel file in sql server 2014?

Question

I want to export tbl_category to excel file with code. I can export with the wizard but I want to create procedures to create excel file daily. tbl_category : 2 column contain : [id] is int and [category] is nvarchar(max)

code:

INSERT INTO OPENROWSET 
('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\template.xlsx;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]')
SELECT [id]
 ,[Category]
 FROM [dbo].[TBL_Category]

windows 7 32 bit and 64bit. sql server2014. ms office 2010.

error:

Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

pic of excel1

Answer 1

This may be a file permissions/uac issue.

To prove/disprove that, create a new folder and copy (not move) the excel file into it, then give the windows security group "Everyone" Full control on the folder and its files. Update your export query with the new file location and try running it again.

If that works (or if you at least get a different error), then bear in mind that wherever you want to have the export file in the end, the file will need read/write permission assigned for the user account your SQL Server service runs under. (Or the "Everyone" group, but that wouldn't be best security practice.)

• RedArmy: The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" supplied invalid metadata for column "CategoryName". The data type is not supported.
• T.H.: @RedArmy No problem, please mark my answer as accepted (stackoverflow.com/help/accepted-answer), thanks. As for your new error, it suggests that nvarchar(max) can't be exported to excel. To test this theory, replace [Category] in your query with CAST([Category] AS nvarchar(255)). If you need more characters to export, it looks like something at stackoverflow.com/questions/28842095/… might help.

Answer 2

Try this stored procedure

USE master
GO
IF object_id('spExportData') IS NOT NULL
 DROP PROCEDURE spExportData
GO
CREATE PROCEDURE spExportData (
 @dbName VARCHAR(100) = 'master'
 ,@sql VARCHAR(5000) = ''
 ,@fullFileName VARCHAR(100) = ''
 )
AS
IF @sql = '' OR @fullFileName = ''
BEGIN
 SELECT 0 AS ReturnValue -- failure
 RETURN
END
-- if DB isn't passed in set it to master
SELECT @dbName = 'use ' + @dbName + ';'
IF object_id('##TempExportData') IS NOT NULL
 DROP TABLE ##TempExportData
IF object_id('##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
 SELECT 0 AS ReturnValue -- failure
 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 + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] 
 from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
EXEC (@sql)
-- build full BCP query
SELECT @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
EXEC master..xp_cmdshell @sql
IF @@error > 0
BEGIN
 SELECT 0 AS ReturnValue -- failure
 RETURN
END
DROP TABLE ##TempExportData
DROP TABLE ##TempExportData2
SELECT 1 AS ReturnValue -- success

Here you execute the query you want. For example:

DECLARE @sql VARCHAR(6800)
 ,@dbName VARCHAR(100)
 ,@fullFileName VARCHAR(100)
SELECT @dbName = 'northwind'
 ,@sql = 'select * from orders order by orderdate'
 ,@fullFileName = 'e:\test.xls'
EXEC master..spExportData @dbName
 ,@sql
 ,@fullFileName

I've got the query from here a couple years ago, and it's pretty neat.

• RedArmy: try but not succeful:((((((
• Danilo Braga: Which error did u get? Because I've been using it since sql 2005

Answer 3

If you run some tests with different numbers of rows in the source you'll probably see that there is a point where the load starts to fail.

At this point the OLEDB driver attempts to create a temp file in the C:\users.. folder. The Windows Authentication Login that is running the load needs to have appropriate permissions on the C drive. (A SQL Authentication Login will not work)

Also consider that if this a distributed SQL Job running under a proxy user locally, then the Temp file may be created at the remote server.

Answer 4

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
 | 'provider_string' } 
 , { [ catalog. ] [ schema. ] object 
 | 'query' 
 } 
 | BULK 'data_file' , 
 { FORMATFILE = 'format_file_path' [ ]
 | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 
 ::=
 [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
 [ , ERRORFILE = 'file_name' ]
 [ , FIRSTROW = first_row ] 
 [ , LASTROW = last_row ] 
 [ , MAXERRORS = maximum_errors ] 
 [ , ROWS_PER_BATCH = rows_per_batch ] 

• McNets: Please, try to explain how to use it.
• RedArmy: Incorrect syntax near the keyword 'OPENROWSET'.