Export in Excel With using OPENROWSET from Stored Procedure

Question

 

Hi

I am Utarsh Gajjar.

I am working on SQL Server 2005.

 

I have following Stored Procedure.

 

----------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ExportInExcel]
    @QueryString VarChar(8000) =''
    AS
BEGIN TRY
 
    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Customers.xls;IMEX=1','SELECT * FROM [Sheet1$]')   
    EXEC (@QueryString )
  
 
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage VARCHAR(8000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

-------------------------------------------------------------------------------

Error is

-------------------------------------------------------------------------------
(0 row(s) affected)

Msg 50000, Level 16, State 2, Procedure ExportInExcel, Line 31
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.

(1 row(s) affected)

 

how can i solve this problem?

 

 

Thanks in advance.

 

Answer 1

Don’t use OPENROWSET it is read-only.

 

Use the OPENDATASOURCE you can read & write data into it…

 

Code Block

Insert into

OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source={Your Excel File Path};Extended Properties=Excel 8.0')...[SheetName$]

Values (....)

 

 

 

Answer 2

Thank you Manivannan.D.Sekaran.

With OPENDATASOURCE(), i got solution.

 

again

Thank you.

 

Answer 3

I'm also interested on this issue, however, I encounter this problem when using opendatsource:

 

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '.Sheet1$A1:H1'.

 

The query string is:

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source={\\Operations\Output\CR_20071130.xls};Extended Properties=Excel 8.0')..[Sheet1$A1:H1] exec sp_get_CR

 

the excel file has one line header, but I've tried to remove the header and replace [Sheet1$A1:H1] with [Sheet1$] in code.
The Error message became to:

 

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '.Sheet1$'.

 

I was able to export the query result into excel file with openrowset function, but I would like to use stored procedure to avoid constructing query string dynamically.

 

Could you please shed some light on it?

 

Thanks a million.

 

Erwin

 

 

Answer 4

 

Hi Erwin,

 

I have tried with OPENROWSET() And OPENDATASOURCE().

But sometimes it was giving error, so i found another solution..

 

try it.

 

 

 

SQL Server has several tools for exporting and importing data. The simple utilities like the T-SQL statement BULK INSERT or the BCP utility does the data transfer by using flat text files. In order to deal with any other type of file to be exported or imported we must construct a DTS package using the DTS designer or the DTS wizard. This will eventually create a DTS package that is an independent object that we must test, maintain and handle. The work of transferring data becomes more complicated even if we want to transfer just a simple Excel file to the file system.

Here I suggest a simple but useful T-SQL stored procedure that does the export to Excel by using the linked server technology.

In order to achieve the export, you must first create an empty Excel file with a fixed name and place on the server. I called it Empty.xls and placed it in the c: emp directory. This file is not to be deleted and acts like a template for the target Excel file before it is populated by data.

The Empty.xls file is built with a single spread sheet called ExcelTable and the first (and only) line in the file consists of the letters A,B,C,...Z. These letters act like the column names of the Excel table. That means that up to 26 columns can be exported in a single query. (The given stored procedure code can be altered to support more columns in the result set. Simply write F1, F2 ,F3... in the Excel template and change the column list in the procedure to reflect the change.)

The T-SQL stored procedure, sp_write2Excel, gets the target Excel file name and path, the number of columns in the result set and the T-SQL query. The query should use the convert function for every non-string exported column since the resulting Excel cells are actually strings.

The procedure copies the empty.xls template file to the new target Excel file. Then it builds a linked server to that file and uses dynamic T-SQL to construct and Insert/Select statements that write the data to the Excel file.

Here is the procedure code:

Create proc sp_write2Excel (@fileName varchar(100),

                                   @NumOfColumns tinyint,

                                   @query     varchar(200))

as

begin

        declare @dosStmt  varchar(200)

        declare @tsqlStmt varchar(500)

        declare @colList  varchar(200)

        declare @charInd  tinyint

       

        set nocount on

 

        -- construct the  columnList A,B,C ...

        -- until Num Of columns is reached.

 

        set @charInd=0

        set @colList = 'A'

        while @charInd < @NumOfColumns - 1

        begin

          set @charInd = @charInd + 1

          set @colList = @colList + ',' + char(65 + @charInd)

        end

 

        -- Create an Empty Excel file as the target file name by copying the template Empty excel File

        set @dosStmt = ' copy c:      empempty.xls ' + @fileName

        exec master..xp_cmdshell @dosStmt

 

        -- Create a "temporary" linked server to that file in order to "Export" Data

        EXEC sp_addlinkedserver 'ExcelSource',

        'Jet 4.0',

        'Microsoft.Jet.OLEDB.4.0',

        @fileName,

        NULL,

        'Excel 5.0'

 

        -- construct a T-SQL statement that will actually export the query results

        -- to the Table in the target linked server

        set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' +  ' ( ' + @colList + ' ) '+ @query

       

        print @tsqlStmt

 

        -- execute dynamically the TSQL statement

        exec (@tsqlStmt)

 

        -- drop the linked server

        EXEC sp_dropserver 'ExcelSource'

        set nocount off

end

GO

Example for procedure usage:

 

Use master

go

exec sp_write2Excel

           -- Target Excel file

           'c: empNorthProducts.xls ' ,            

 

           -- Number of columns in result          

           3,                                                 

 

           -- The query to be exported    

           'select convert(varchar(10),ProductId), 

            ProductName,

            Convert (varchar(20),UnitPrice) from Northwind..Products'

In conclusion, the procedure can be used as a general tool for exporting data to an Excel spreadsheet since the bcp utility can only export to a text file.

The usage of this procedure can substitute the usage of DTS package designer or the DTS wizard in order to export data to excel each time such an action is required.

Answer 5

Thank you very much for the input, Utkarsh.

 

I had tried this method before thanks for Eli Leiba's article:

"Write query results to Excel using a linked server and T-SQL",

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1057989,00.html?bucket=ETA&topic=301370

 

Not sure I have made it clear. My intention was to put a fairly large query into a stored procedure, then use insert...execute... method to export the result returned from sp to excel file. I got the folling error:

 

Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not support the required transaction interface.
Insert ExcelSource...[Sheet1$]  ( 'First name', 'Last name', 'Balance', 'Phone no' ) exec opr_get_balance
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IUnknown::QueryInterface returned 0x80004002].

 

When I tried opendatasource function before, I remember I got similar error message. The sp_get_balance caintain just a select statement with proper 'create proc' statements  and sql server option settings.

 

I would like more flexibility on the excel template and header so I modify the sp_write2excel a little bit, here is my proc and the call for this sp:

 

caller:

 

exec opr_write2Excel
 'Balance',
 'test-balance',
 'Sheet1',
 '''First name'', ''Last name'', ''Balance'', ''Phone no''',
 'execute billing.dbo.opr_get_balance '

 

sp to export query to an excel file:

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- creating the store procedure
IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'opr_write2Excel'
    AND    type = 'P')
    DROP PROCEDURE opr_write2Excel
GO

create proc opr_write2Excel (
 @template varchar(100),
 @fileName varchar(100),
 @SheetName varchar(40),
 @colList varchar(400),
 @query varchar(2000))
as
begin

declare @dosStmt varchar(400)
declare @tsqlStmt varchar(4000)

declare @root_dir varchar(100)
declare @TableName varchar(40)

set nocount on

if 0 < charindex(' ', @SheetName)
 select @TableName = '''' + @SheetName + '$'''
else
 select @TableName = @SheetName + '$'

print @TableName

select @root_dir = '\\Operations\'

select @template = @root_dir + '\Templates\' + @template + '.xls'
select @fileName = @root_dir + '\Output\' + @fileName + '.xls'

-- CREATE MY EXCEL FILE BY COPING EXCEL TEMPLATE
set @dosStmt = ' copy "' + @template + '" "' + @fileName + '"'
--print @dosstmt

exec master..xp_cmdshell @dosStmt

-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver
 'ExcelSource',
 'Jet 4.0',
 'Microsoft.Jet.OLEDB.4.0',
 @fileName,
 NULL,
 'Excel 5.0'

-- add remote login
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'FALSE'

-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[' + @TableName + '] ' + ' ( ' + @colList + ' ) '+ @query
--print @tsqlStmt

-- execute dynamically the TSQL statement
exec (@tsqlStmt)

-- drop the remote login
EXEC sp_droplinkedsrvlogin 'ExcelSource', NULL

-- drop the linked server
EXEC sp_dropserver 'ExcelSource'

set nocount off

end

 

A side note to linked server, I was just able to make it work today because I got an error when I tried it couple days ago, I got this error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
Insert ExcelSource...[Sheet1$]  ( A,B,C ) select 'A','B','C'
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].

 

After some search on web today, I found William Wang's post and finally got the linked server work with excel:

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic11139.aspx

 

 

To test the sp I created above, I create a temple file \\Operation\Template\test-template.xls, with three header column named 'Col1', 'Col2', 'Col3', respectively, renamed the Sheet1 to 'Linked Table', for testing purpose.

 

Then call sp with:

 

exec opr_write2Excel
 'test-template',
 'test-output',
 'Linked Table',
 'Col1, Col2, Col3',
 'select ''A'',''B'',''C'' '

A file named \\Operation\Template\test-output.xls is create 'A', 'B', 'C' correctly inserted into the second row.

 

I am still pursuing a method to generate the column list automatically, but with no success at this moment. Any input is welcomed.

 

For the insert linkedserv.table execute sp method, I'm almost negative for its feasibility.

 

Erwin

Answer 6

 

More thoughts, I can use a global temp table to hold results returned from stored procedure and use select statement in insert...select to excel file. That works, but I would prefer an approach w/o using temp tables.

 

Erwin

Answer 7

 

hi Erwin,

 

If you can use global temp table than it is very wonderfull solution.

I dont know how to do it. In SP i need to declare lots of variables and 2-3 Cursors, and it occupy lot of space. so you have very wonderfull solution.

Here, My requirement is to Export 5 result to 1 Workbook and 5 different Sheets like (Mainpage|Total|Minimum|Maximum|Average).  So for that i need to SP.

 

But i have done same thing which is mentioned in Eli Leiba's article:

"Write query results to Excel using a linked server and T-SQL",

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1057989,00.html?bucket=ETA&topic=301370

 

but i am not getting any error.

 

I think once you have to check all propertis and criterias like file names, columns, LinkServer properties, etc.

 

yesterday i search lot about your errors, but i dont get any solution. so you should continue with your new solution.

 

 

 

 

 

Answer 8

 

Thanks for the reseach. Actually a local temp table works too. I have a report with 3 workseets with different column numbers. I create sp for each worksheet, each take @start_date and @end_date as paramerters; then use one single temp table to relay the result to each worksheet with openrowset function. I think it work for your situation too.

 

Here is my code:

 

CREATE PROCEDURE dbo.opr_rpt_MediaStat
 @StartDate datetime,
 @EndDate datetime
AS

declare @ReportName varchar(255), @cmd varchar(255), @sqlstmt varchar(4000), @query varchar(4000)
declare @Provider varchar(255), @ExcelString varchar(255)
declare @ReportFile varchar(255), @ReportTemplate varchar(255), @ReportDir varchar(255), @FileName varchar(255)
declare @report_root_dir varchar(255)

create table #repdata (
 col1 varchar(80) null,
 col2 varchar(80) null,
 col3 varchar(80) null,
 col4 varchar(80) null,
 col5 varchar(80) null,
 col6 varchar(80) null,
 col7 varchar(80) null,
 col8 varchar(80) null,
 col9 varchar(80) null
)

 

select @ReportName = 'Media Statistic Report'

select @report_root_dir = '\\Operation\'

select @ReportTemplate = @report_root_dir + 'Templates\MediaStat.xls'
select @ReportDir = @report_root_dir + 'Output\Weekly\MediaStat\'
select @FileName = 'MediaStat_' + convert(char(8), isnull(@EndDate ,getdate()), 112) + '.xls'

select @ReportFile = @ReportDir + @FileName

 

-- create report excel file from template file
select @cmd = 'copy "' + @ReportTemplate + '" "' + @ReportFile + '"'

exec master..xp_cmdshell @cmd, NO_OUTPUT

 

-- Set OLEDB Provider
select @Provider = 'Microsoft.Jet.OLEDB.4.0'

-- Set OLEDB excel destination filename and enable header for OPENROWSET fucntion
select @ExcelString = 'Excel 8.0;Database=' + @ReportFile + ';HDR=YES'

 

-- first worksheet

 

-- populate temp table with result by week
insert into #repdata(col1, col2, col3, col4,col5)
exec opr_sql_MediaStatByWeek @start_date = @StartDate, @end_date = @EndDate

 

--construct query
select @query = 'select col1, col2, col3, col4 from #repdata'
select @sqlstmt = 'insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [By Week$A1: D1]'') ' + @query
exec(@sqlstmt)

 

-- second worksheet

truncate table #repdata

 

-- populate temp table with result by day
insert into #repdata(col1, col2, col3, col4, col5, col6)
exec opr_sql_MediaStatByDay @start_date = @StartDate, @end_date = @EndDate

 

--construct query
select @query = 'select col1, col2, col3, col4, col5 from #repdata'
select @sqlstmt = 'insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [By Day$A1:E1]'') ' + @query
exec(@sqlstmt)

 

-- third worksheet

truncate table #repdata

 

-- populate temp table with result by hour
insert into #repdata(col1, col2, col3, col4, col5, col6, col7)
exec opr_sql_MediaStatByHour @start_date = @StartDate, @end_date = @EndDate

 

--construct query
select @query = 'select col1, col2, col3, col4, col5, col6 from #repdata'
select @sqlstmt = 'insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [By Hour$A1:F1]'') ' + @query
exec(@sqlstmt)

 

drop table #repdata

 

 

If you notice the insertion into #repdata does not match the select from #repdata, that's because the sp retuns one more column for grouping purpose that I don't want to show on the worksheet.

 

Since some of the columns are numbers, I create a vbscript in the template workbook to remove the little green tags:

 

in one worksheet:

Private Sub Worksheet_Activate()
    Dim x_rng As Range
   
    Set x_rng = Range("D2", Range("E2").End(xlDown))
   
    For Each y In x_rng.Cells
        If y.Errors.Item(xlNumberAsText).Value = True Then
            y.Value = y.Value * 1
        End If
    Next

End Sub

 

HTH

Answer 9

 

Thanks

 

I have used procedure to remove the little green tags.

 

Private Sub Worksheet_Activate()
    Dim x_rng As Range
   
    Set x_rng = Range("D2", Range("E2").End(xlDown))
   
    For Each y In x_rng.Cells
        If y.Errors.Item(xlNumberAsText).Value = True Then
            y.Value = y.Value * 1
        End If
    Next

End Sub

 

It is wonderful, but i can't use it,because in my excel workbook there are 5 sheets and each sheet contents morethan 500 rows and 160 columns so it comes 2500 rows and 800 columns.

 

so it is taking 10 - 15 minutes to remove green tags.

 

so instade of this i m using Protect sheet method.

 

With Ex1   ' Excel Object

.Range(RangeOfProtect).Select()

If .ActiveWindow.DisplayGridlines = True Then .ActiveWindow.DisplayGridlines = False

.ActiveSheet.Protect(DrawingObjects:=True, Contents:=True, Scenarios:=True)

End With

 

After using this code excel sheet will become readonly, means user will not be allowed to edit.

and it removes green tags.

Here my requirment is like that, so i am using it.

 

 

 

 

Answer 10

Great.

 

The method I use above with temp table has a problem, the order of the records returned may not follow the order by clause in sp. To solve the problem, I need to specify the true datatype of the columns used in order by clause  in sp and use order by clause as well when inserting the records from temp table into openrowset object. 

 

Answer 11

I'm trying to export rows from SQL Server to Excel. I created a template XLS file with 'COL1' in cell A1 and 'COL2' in cell B1. When I export data without the IMEX flag, the numbers are considered text and do not sort or behave as they should. However, when I try adding the IMEX flag, I get an error.

 

-- THIS WORKS (but numbers are treated as text):

Code Snippet

INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 'Data Source="C:\Test_Sql2Xls.xls"; Extended Properties="Excel 8.0;HDR=Yes"')...[Sheet1$] (COL1, COL2)
SELECT 1, 'A' UNION SELECT 2, 'B'

 

 

-- THIS DOES NOT:

Code Snippet

INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 'Data Source="C:\Test_Sql2Xls.xls"; Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$] (COL1, COL2)
SELECT 1, 'A' UNION SELECT 2, 'B'

 

 

-- HERE IS THE ERROR:
OLE DB provider "Microsoft.Jet.OleDB.4.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.Jet.OleDB.4.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.Jet.OleDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OleDB.4.0]".

 

I'd love anybody who can help. I know this is going to be the first complaint I get from the users.

Answer 12

I would just like to warn those who may not know this already.  There is NO MS Jet 4.0 driver support on a 64 bit SQL server 2005 installation.  Microsoft jet drivers are only available in 32 bit.  This means that if you upgrade your server to a 64 bit server, with a 64 bit SQL Server installation, your code will break.  In SQL Server 2005 you have to use SSIS 32 bit mode to create any files using the Jet driver.

Answer 13

 

Can anyone tell me how to accomplish exporting data to an excel spreadsheet on a 64 bit machine without using SSIS since OPENROWSET (Jet driver) is not supported for x64? 

 

thanks

Answer 14

You cant run it from a 64 bit installation of SQL 2005.  You have to use a tool that runs or emulates 32 bit.  This means your full proof options are to create a SSIS package, or have the import/export wizard create the SSIS package.  You can try executing a bcp or sqlcmd command, as these applications may run in 32 bit, but I am not sure.  It cant hurt to try.

Answer 15

Download the SQLEXPRESS 32 bit version and install it on the 64bit box. Then put your export proc on that instance and call it from the 64bit instance of SQL.

 

Works a dream although the dog leg is a bit annoying. Believe this may be fixed in later versions due out 2011 or so.

Answer 16

I know this is a very old thread but someone PLEASE unmark this as an answer.   OPENROWSET is definitely NOT read-only nor has it EVER been.

---

--Jeff Moden