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.

1

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.)

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.

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 ]