Sql-server – How to Export Table sql to Excel file in sql server 2014

openrowsetsql serversql server 2014

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

Best Answer

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

Related Question