0

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

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

  • 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. – RedArmy Feb 9 '17 at 18:15
  • 1
    @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. – T.H. Feb 9 '17 at 21:02
1

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.

  • try but not succeful:(((((( – RedArmy Feb 10 '17 at 9:16
  • Which error did u get? Because I've been using it since sql 2005 – Danilo Braga Feb 10 '17 at 12:38
0
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 ] 
  • Please, try to explain how to use it. – McNets Feb 9 '17 at 20:23
  • Incorrect syntax near the keyword 'OPENROWSET'. – RedArmy Feb 10 '17 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.