SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

« SQL SERVER – 2005 – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer

SQL SERVER – Execute Same Query and Statement Multiple Times Using Command GO »

SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

January 8, 2008 by pinaldave

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively you can download sample Spreadsheet from here.

Run following code in SQL Server Management Studio – Query Editor.
USE [AdventureWorks]; GO INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;', 'SELECT * FROM [Sheet1$]') SELECT TOP 5 FirstName, LastName FROM Person.Contact GO
Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 207 Comments

207 Responses

on January 10, 2008 at 3:38 pm | Reply Edgar

Hi, this codes work great but how about it done by reverse..
like i have database in Access file in my C: drive then i want this to insert my SQL server database..

how could i do this..?

please help…
- on February 10, 2012 at 9:58 pm | Reply Rams
  
  HI Pinal,
  
  I have an issue with SSIS package, I have designed a package and schedule it in the prod server, this package delete the exsiting detstination file through Script task and pump the data every time as I need to overwrite the existing data with new data.
  
  The issue is, if anybody open the destination excel file, the pakcage is failing and I am unbale to track those details via send mail task.
  
  Can any body help on this please..
  
  Thanks,
  Rams.
on January 10, 2008 at 7:58 pm | Reply Madhivanan

More informations are available here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
on January 10, 2008 at 8:00 pm | Reply Madhivanan

Edgar

Use Import/Export wizard from SQL Server
or read about OPENROWSET in sql server help file
on January 11, 2008 at 2:02 pm | Reply Gunjan Shah

when ever I tried to execute the below query it gives me an error.

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1]‘)
SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
FROM ACCTaxMaster where txem_id>32

The error is :

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Pls reply.
- on October 24, 2011 at 11:29 am | Reply Madhu
  
  Close the file if it is opened. i got the same error but after closing the file i had executed the query it is exporting perfrectly…
  - on October 24, 2011 at 7:42 pm | Reply madhivanan
    
    Yes. When a file is opened it is exclusively locked by the user/system. So it should be closed at the time of running a query that is accessing the EXCEL file
on January 11, 2008 at 2:09 pm | Reply ed9teen

thanks for advice.. all i saw there is inserting data using excel to SQL server. my question how about inserting data using access to SQL SERVER….

HOW COULD I DO IT..?

PLEASE HELP..
- on March 25, 2010 at 4:53 pm | Reply Madhivanan
  
  Read about OPENROWSET in SQL Server help file
  It has example on how to do it
on January 11, 2008 at 5:35 pm | Reply ed9teen

yes that is the error but i got correct link or path… and filename..
on January 11, 2008 at 5:38 pm | Reply ed9teen

hi, i tried this codes to transfer my data from access to sql server…
but it’s not working…
can someone modify my codes please..?

Insert into magpatoc.dbo.RSOTransfer
Select * FROM (‘Provider=Microsoft.Jet.OLEDB.4.0;’,
‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
‘SELECT * FROM FinalCustItemRSO’)

Help please..
- on March 25, 2010 at 4:55 pm | Reply Madhivanan
  
  What did you mean by “it is not working”?
  Did you get any error?
on January 16, 2008 at 11:56 pm | Reply neeraj

when ever I tried to execute the below query it gives me an error.

INSERT INTO OPENROWSET (’Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1]’)
SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
FROM ACCTaxMaster where txem_id>32

The error is :

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Person.Contact’.
- on March 16, 2011 at 10:59 am | Reply gaurav
  
  hi.use it-
  ==========
  insert into P
  values(‘gt’,'gt’,'gst1@in.com’)
  INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=c:\contact.xls;’,
  ‘SELECT * FROM [Sheet1$]‘)
  SELECT top 5 FirstName,LastName,email from P
  where FirstName>email
  =========
on January 17, 2008 at 10:14 pm | Reply Kostas Michas

ed9teen and neeraj instead of [Sheet1] write [Sheet1$]
on January 23, 2008 at 11:59 am | Reply siva

Is it possible to set a nullable column as a Foreign key
to PrimaryKey Column of another table?

Siva.
on January 23, 2008 at 8:52 pm | Reply raju

thankx
on January 23, 2008 at 8:59 pm | Reply raju

its also can do using DTSWizard.exe in command Prompt or in RUN in windows

Thankx
on January 28, 2008 at 11:45 am | Reply brajesh

sir,
i m working in sql server 2000, now i want to import server 2000 data in sql server 2005 ,how can i do this…help me plz
on February 5, 2008 at 9:14 pm | Reply Meesh

hi Dave
Trying to export a lot of data into Excel file but I get this error:
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.

I think it’s because there are line breaks and other characters in the data. Is there any way to get around this?
Thanks a lot!
on March 11, 2008 at 5:40 pm | Reply saraboji

TITLE: Microsoft SQL Server Management Studio
——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
ADDITIONAL INFORMATION:

String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3033&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

——————————
BUTTONS:

OK
——————————
on March 13, 2008 at 4:24 am | Reply aay

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
on March 13, 2008 at 7:59 pm | Reply Sean

Hi – I would like to do something very similar…

I want all of the databases tables and their columns inserted into an excel spreadsheet. So schema rather than data.

Do you know how this can be done?
on March 13, 2008 at 10:32 pm | Reply Daniel Serodio

Sean, see the SQL Server help for “Information Schema”, it’s a set of “meta-tables” (or views) which contain information about your database’s schema.
on March 25, 2008 at 5:18 am | Reply abdul

Puinal Hi:

Is there a way to use query like below………..BUT NOT give column names hardcoded in the excel file….

The reason being my sql is dynamically being passed and I donot want to have column names hardcoded into my excel template.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
on April 22, 2008 at 3:42 pm | Reply Tiong

if exceed field length 255 then how?
anyone can help or not
on May 13, 2008 at 3:18 am | Reply Monika

Hi,
I used your code

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

to export data from sql server to excel but i am getting this error
OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘SheetData1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

can you help me out to get rid of this?
Thanks!
Monika
- on March 25, 2010 at 4:59 pm | Reply Madhivanan
  
  The error message is very clear
  Make sure you spelled the sheet name correctly
  - on June 4, 2010 at 9:36 pm | Reply Damodharan
    
    Hi,
    
    I want to create new excel sheet. How to do?
    - on June 5, 2010 at 9:53 am | Reply Imran Mohammed
      
      @Damodharan.
      
      There is a very complex process to create Excel Sheet, which I do not suggest to anyone.
      
      Simplest way to create new Excel sheet is to follow below steps,
      
      1. Create a Template/Model Excel Sheet (Empty) in any folder.
      2. Use Xp_Cmdshell copy command to copy this file from above folder to another folder.
      
      EXEC master.dbo.xp_cmdshell ‘copy Folderlocation\ExcelFileTemplate.xls new_folder_location\ExcelFileTemplate.xls’
      
      Now you have a new Excel File.
      
      Perform you action, Write to Excel Sheet. Once done with your task.
      
      3. Using Xp_Cmdshell you can also rename Excel Sheet.
      
      ~IM.
  - on March 21, 2011 at 9:47 am | Reply saurabh
    
    what doed sheet signify?
    - on March 22, 2011 at 12:44 pm | Reply madhivanan
      
      The sheet name should be correctly specified
on May 21, 2008 at 4:03 pm | Reply ZR

Hi,
I have problem with Excel when I using Openrowset in sql 2k.
When I export sql table witch has column type decimal in excel I have column type string. I would like decimal in excel too.
What I can do?

THX
- on March 25, 2010 at 4:59 pm | Reply Madhivanan
  
  You should format the EXCEL cell to be of type decimal
  - on February 9, 2011 at 3:07 am | Reply Michelle
    
    I formatted the excel to number and it did not work.
on June 7, 2008 at 11:54 am | Reply Gufran Sheikh

Hi,
This Query runs successfully
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:\mydb.mdb’;'Admin’;”, Table1)

But this doesnt because it has password
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:\ACCESS.mdb’;'Admin’;'mypwd’, Table1)

Before it both queries giving authentication error but after setting the permissions i am able to run the first query, but i am still having problem to run second query
on June 25, 2008 at 1:37 am | Reply priya

Hi,
I am trying to create SSIS to export data to excel sheets(to multible sheets from different queries using views)
I tried to run one query and export to one of the sheets.
I got this error
[Excel Destination [153]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
[Excel Destination [153]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Excel Destination Input” (164)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Excel Destination Input” (164)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
and more…

Pinal or some Gurus ,
do you know how should I export data to excel from
2005 using SSIS?

priya
on June 25, 2008 at 2:59 am | Reply priya

I am adding this to my above question
I need to insert data from B6
on July 24, 2008 at 2:09 pm | Reply nanaji

when ever I tried to execute the below query it gives me an error.

USE [TEST];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\TEST.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT * FROM t11
GO

The error is :

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Pls reply.
on July 26, 2008 at 1:29 am | Reply Denny

Hello,

“OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”. ”

this error means your excel file do not have a sheet named Sheet1, so check the excel file
on July 30, 2008 at 9:25 am | Reply Shyam

This is not still working anybody know, how to overcome on this error
“OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”. ”

Regards
Shyam
on July 31, 2008 at 3:34 pm | Reply p s

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
- on March 25, 2010 at 5:03 pm | Reply Madhivanan
  
  Go thru this
  http://support.microsoft.com/kb/209805
on August 3, 2008 at 6:57 pm | Reply James

Works great, thanks
on December 20, 2008 at 12:14 am | Reply Brian

We use SQL Server 2005 & SQL Server Express to keep our Management Software data.

My question is simple, but may not be so easily implemented. I would like to take our data (*.mdf) files and export it to a different work station that has also SQL Server Express. From my understanding this is not easily done thru SQL Server 2005. Does anyone have any suggestions?
on January 6, 2009 at 3:26 pm | Reply sizwe

hi, please help, i’m new to this sql…i’m running sql server 2005 on ms server 2003(64x). everytime i click on management studio this is the message i get “The file C:\windows\microsoft.net\framework\\mscorlib.tlb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”
on January 20, 2009 at 1:33 pm | Reply Prashant Shetake

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

This code is working properly, but when i changed the path of excel file then it gives an error(Note:- excel file is on anthoer machine)

path is like that :- \\bhavin\AX-Config\contact.xls

Error :- Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
- on April 9, 2010 at 2:33 pm | Reply Madhivanan
  
  Make sure your accout has access to that specific file
on February 16, 2009 at 12:36 pm | Reply Pritesh Parmar (Pintoo)

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=D:\UserRights.xls;’,
‘SELECT * FROM [Sheet1]‘)
select * from dbtemp.dbo.Operators

here dbtemp is the database name and Operators is the table name

i have already created the file ‘D:\UserRights.xls;’

While using this query, i m getting following error whot should i do

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

PLs Reply soon……..
- on April 9, 2010 at 2:34 pm | Reply Madhivanan
  
  Most likely sheet name would be $sheet1 and not sheet1
on March 10, 2009 at 4:32 pm | Reply dona

can anyone please post the solution to this problem
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
- on March 25, 2010 at 5:04 pm | Reply Madhivanan
  
  The first row of EXCEL sheet should have column informations
on March 20, 2009 at 1:06 pm | Reply Sav

Hi,

The code provided works fine, but when i run the same second time the data in excel gets appended, where i want it to copy to a new sheet or delete the existing data and insert new data.. Awaiting for the response…
Thanks in advance
- on April 9, 2010 at 2:35 pm | Reply Madhivanan
  
  If you want to copy to a new sheet, use that sheet’s name in the query. Also it is not possible to delete data of excel sheet using a query
on March 29, 2009 at 1:25 am | Reply Anand

Hi, I would like to export data from store procedure (which has two input parameter) to Excel. The store procedure will return multipal record sets.

Please let me know if we have any way to do this.
- on April 9, 2010 at 2:38 pm | Reply Madhivanan
  
  Do all resultsets return same number of rows? Then it is possible. Otherwise you will get error
on March 29, 2009 at 1:27 am | Reply Anand

Sorry forgot to mension n think this is required.

I am using Sql Server Express 2005.
on April 8, 2009 at 7:09 pm | Reply Scott

I have procudures that successfully download data into Excel worksheets but run into issues if a field on the SQL Server has more than 255 characters it fails. I am using Excel 2003 and have found some post regarding “Extended Properties” of the connection string however I cannot get this to work correctly.

Do you have any suggestions on how to get around this type of issue?
on April 22, 2009 at 12:09 pm | Reply pramod

I tried u r code
It is showing Error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]

what is exact prob
is it with Excel Or with Sql
plz help
- on April 9, 2010 at 2:49 pm | Reply Madhivanan
  
  It is with the code used in sql
  Make sure the sheet name is sheet1 (but most likely it should be $sheet1)
on April 30, 2009 at 1:03 am | Reply Steve

The reason many are getting this message:
[ “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” ] is because Excel must exist on the same machine you have the xls file on. The spreadsheet itself does not carry the Jet database engine.
on May 14, 2009 at 11:39 am | Reply Rizwan

hi dave i tried above code its working for excel but i want to export data from access data base to sql server and vise versa how can do this

pls help me i am a ferser……………..
on May 15, 2009 at 8:42 am | Reply Imran Mohammed

@Rizwan

There are a tleast 5 ways of doing this, please look at the below link for more details.

http://support.microsoft.com/kb/321686

IM
on May 22, 2009 at 5:36 am | Reply jith

Hi,

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\temp\test.xls;’,
‘SELECT aftercare_id,participant_id,r_followup_period_id FROM [Sheet1$]‘)
SELECT [aftercare_id],[participant_id],[r_followup_period_id]
FROM view_1
go

The above code works, but it will allow me to put only 7 columns, if i put ‘*’ it is not inserting data into excel, could you please tell me how to fix this problem

i have 30 columns in my view. i want to use all columns, could you please respond to me as early as possible.

Thanks,
Roy
on May 22, 2009 at 10:49 am | Reply Imran Mohammed

@Jith,

Sample from below link :

insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=D:\testing.xls;’,
‘SELECT * FROM [SheetName$]‘) select * from SQLServerTable

Please visit this link to get more details.

Link : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

~ IM.
on May 25, 2009 at 2:17 pm | Reply Arjay Orcasitas

Hi,

I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I’m working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=C:\Documents and Settings\…analysis.xls’, [data$])

you can contact me here: jaythree03@gmail.com
on June 3, 2009 at 6:29 pm | Reply Test

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Destination – Configuration” (37).
(SQL Server Import and Export Wizard)
- on March 25, 2010 at 5:06 pm | Reply Madhivanan
  
  You need to install the latest service pack for SQL Server version you are using
on June 5, 2009 at 3:52 pm | Reply Thato

Hi I’ve tried this example with minor changes like:

INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0;Database=C:\Documents and Settings\Thato\My Documents\contact.xlsx;’,'SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
——————–
I get a syntax error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

Please help
- on April 9, 2010 at 3:44 pm | Reply Madhivanan
  
  That should be
  
  INSERT INTO OPENROWSET
  ('Microsoft.ACE.OLEDB.12.0',
  'Database=”C:\Documents and Settings\Thato\My Documents\contact.xlsx”;',
  'SELECT * FROM [Sheet1$]')
  SELECT TOP 5 CallName, Surname
  FROM DTIHeadcount
  Where Period = 200904
  - on January 28, 2012 at 6:45 pm | Reply Nilesh Kumar
    
    Thanks …. it helped me to upload xlsx data in sql 2005
    - on May 15, 2013 at 3:45 am | Reply neldimgomez
      
      it is not working with sql server 2008 and an office 2010 file.
on June 17, 2009 at 2:49 am | Reply Praveen

In a simple way, we can export the resultset to .csv file using the following scenario:

1. Go to Tools>>Options>>Query Results>>Sql Server>>Results to text: specify the output format to “Tab delimited”

2.While executing the query – set Resultset to show as text instead of in grid (Ctrl + Q + T)

3. Select all from the Resultset and save Results as “name.csv” or “name.xls”

- Praveen
- on June 5, 2010 at 1:21 am | Reply Anu
  
  Thanks Praveen.That was quick & simple
on June 30, 2009 at 2:45 am | Reply Corelio

Hi!!!

Is possible to insert data from SQL Server to an Excel Spreadsheet that has a password protection? I need this because I want to prevent User Editing…

Tks a lot!!!
on July 20, 2009 at 10:39 am | Reply Nisam

Great topic. Thanks
on July 27, 2009 at 11:52 pm | Reply Bevo

Worked for me, thanks!
on July 30, 2009 at 11:55 am | Reply Jeff Moden

I can’t find the URL that I wanted to cite, but just to let folks know, the “Jet” drivers for this type of wonderful thing are not supported on the 64 bit version of SQL Server. Nice, huh?
on August 3, 2009 at 6:40 pm | Reply Kishore

Error :- Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Solution :

Make sure your the file path and SQL server are in the same PC
on September 22, 2009 at 2:42 pm | Reply Allen

Hello sir,

For the solution you gave regarding exporting data from SQL server to Excel sheet, is there any alternative that the file should be manually created along wid a header row.

Is there any way the excel sheet too can be created with a header row defined by running a command line script from the sql promt so that the file along wid header is created at the same time just before data is inserted into that excel sheet i.e creating a dynamic query in which the file path , filename, column headers are specified in variables

Please help out.

Thanx
on October 11, 2009 at 10:49 pm | Reply mahmoud

USE [hrnew];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=d:\12345.xls;’,'SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 EMP_NO,EMP_FULL_NAME_AR FROM dbo.EMPLOYEES
GO

after run your script with my data i get the next error messge.
please help me

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
on October 26, 2009 at 3:05 pm | Reply karinloos

Hi, to all above that got the error :
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.

Ensure that you have a space between Excel and 8.0 in the OPENROWSET section ie

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0; Database=c:\somesheet.xls;’
,’SELECT * FROM [Sheet1$]‘)
on October 27, 2009 at 4:30 pm | Reply karinloos

to mahmoud

You generally see this type of error if you have not put the Column headers into Sheet1 of your spreadsheet .

ColumnHeaders must be the same as the Field Names.
on October 30, 2009 at 11:16 pm | Reply Rob

Thanks this works great.

One problem I am having. Numeric fields that I am exporting to excel are entered into the excel sheet as text.

I have formatted the column in the excel sheet to number and I am still getting text. Any ideas how to fix this?

Thanks
on November 14, 2009 at 2:41 pm | Reply Malleswarareddy

Hi Every one,this good and simple. I tried this but it is not Working For me.after that working fine because the same syntax could not be worked for Excel 2007.Finally i got and if we use .xls format and also it will give error if an excel file is open and also for wrong name and also for columns not mentioned
on November 18, 2009 at 2:43 pm | Reply Deepak

hi

I tried the same but i get th following error
OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Can u please help me how to solve this ,
can i use the same in server 2008.

Please help me
on December 4, 2009 at 9:33 pm | Reply Harmeet

This code works properly! Thanks
on December 5, 2009 at 11:01 pm | Reply Waji

this code is work fine

thanks
on December 24, 2009 at 2:18 pm | Reply Devdatta Bhosale

hi

I tried the same but i get th following error

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

please give me solution
on January 18, 2010 at 7:27 pm | Reply mike

dtswizard.exe .net framework initialization error unable to find a version at runtime t orun this application

can u tell me the the posiple problem,when i try
tnx for ur help
- on January 25, 2011 at 5:42 pm | Reply Ahmed
  
  I have the same problem. I get the same error msg when i try to import/export data. That is “dts wizard.exe -.net framework initialisation error. Unable to find a version of the runtime to run this application” can any one help please.
on January 19, 2010 at 3:51 pm | Reply kalyan

Hi , Prashant Shetake

Please close the contact.xls sheet and try to execute the query.it works fine

Regards
kalyan
on January 19, 2010 at 3:53 pm | Reply kalyan

Hi, Devdatta Bhosale

Please close the contact.xls sheet and try to execute the query.it works fine

Regards
kalyan
on January 31, 2010 at 7:50 am | Reply Bubbles

hi

Using the example above – I have a situation, where i need to save each person’s detail as a seperate excel file. The thing is i am not sure how many rows are going to be returned by my query each time. so can some one please help me I have the following done –

- I have the query
- I have the rows being returned in ssms
- the results are stored in a temp table
- I would like to get each row saved at a location and the file name appened with the person’s name so I know who it is for to be emailed.

Can some one please help me with the bit of storing each row as an excel file or flat file and adding the persons name to the file name..

Thanks
bubbles
on January 31, 2010 at 11:53 pm | Reply Pinal Dave

Hi,

If you want to store results in text files thaeI would like then SQLCMD is an easy method.
At first create a script to run on command prompt. customize the below statement:

SELECT ‘SQLCMD -S server_name -Q “SELECT * FROM db.schema.Table WHERE personname = ”’ + personname + ”’”
-o c:\’ + personname + ‘.txt’
from db.schema.Table

It would create a SQLCMD statement for each record. Then execute these SQLCMD command on command prompt. a new file would be created for each statement.

Regards,
Pinal Dave
on February 5, 2010 at 9:51 am | Reply karthik

hi,
I want to transfer the data from excel worksheet to a database table in sqlserver.

Kindly help.
on February 13, 2010 at 3:52 am | Reply VIJAYAKANTH

when ever I tried to execute the below query it gives me an error.

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1]‘)
SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
FROM ACCTaxMaster where txem_id>32

The error is :

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Pls reply.
on February 13, 2010 at 1:30 pm | Reply Biswaranjan Das

hi
i have use ur code to transfer data from execel sheet to sqlserver database with a little change
the code are given billow
USE [salary9]
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=D:\contact.xls;’,'SELECT * FROM [Sheet1$]‘)
SELECT FirstName, LastName
FROM testtable
GO
After executing this code in sql query editor
the following err is fired:
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 did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

plz Help.
- on March 25, 2010 at 5:10 pm | Reply Madhivanan
  
  Make sure Server has access to that file and also it is closed
on February 18, 2010 at 10:18 am | Reply suma

i have 3 sheets in an excel with names sheet1,sheet2,sheet3..

i have transfer all the columns in all the sheets into sqlserver
table in data flow task..

How can i do it??i need whole steps…
on February 19, 2010 at 7:50 pm | Reply vani

Hi Suma

I had this problem a few weeks ago. One of the best things to do is to run the Import Export Wizard or alternatively

in the data flow task in the OLEDB destination or SQL Server Destination which ever you are using select the Source to be SQL command from the drop down box – and in the SQL box enter –

select * from Sheet_Name;
select * from Sheet_Name;

and i think this should work.. try it out and let me know
on February 25, 2010 at 12:24 pm | Reply Colin

Hi,
I am exporting data from a sql 2008 database using ssis import/export wizrd to excel. The columns in excel are truncated. How can I autofit the columns in sql?

Please help.
on February 26, 2010 at 12:26 am | Reply Toan

Is there any way where you can automatically enter in cells(1,1) on contact.xls with a tag like “XYZ Company” and
cells(2,1) with “Location: texas”. I want to do this automatically when the scripts run.
on March 4, 2010 at 6:46 pm | Reply Neha Sachdeva

Hi Dave,

Can I export the Stored Procedure (SQL Server 2005) result to Excel (version 2007), like the way we do for Tables and Views (Creating SQL Server connection from excel and export table and view).
- on April 9, 2010 at 4:13 pm | Reply Madhivanan
  
  Yes
  
  Insert into OPENROWSET(….)
  EXEC procedure_name
on March 4, 2010 at 8:51 pm | Reply Neha Sachdeva

To elaborate it:

I am using SQL Server (version 2005) data connection in Excel 2007 to retrieve bulk of records from SQL Server table. Currently, the number of records are around 400,000 and they keep on increasing further going down the line. The query is taking too long to execute while refreshing the data.

I want to use a Stored Procedure instead of a SQL Query which helps in executing result faster and give me data without causing delays in my Excel spreadsheet.

Is there any way by which I can import data using a Stored Procedure from SQL Server ?

Any help would be appreciated.

Many Thanks.
on March 24, 2010 at 1:32 am | Reply Big D

I’ve tried the below script following your example:
USE [EPATEST1];
GO
INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\Dummy EPA Test Data1.xls’, ‘SELECT * FROM [Sheet1$]‘)

…but I get the below error message on SQL Server 2005:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘)’.

What is it that I’m doing wrong?
- on March 25, 2010 at 5:12 pm | Reply Madhivanan
  
  Your file path has spaces so put double quotes around file path and try it
on March 27, 2010 at 9:43 pm | Reply Ron J

It works nicely. Thank you.
on April 5, 2010 at 4:34 pm | Reply Chandrasekaran

Perfect!!!!!!!!!!!!!!
on April 9, 2010 at 2:43 am | Reply Valchiamoz

Hi,

I’m trying to run the simple Query below just to test the export operation, but getting an error.

USE hd
INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=D:\TesteInsercao.xls;’,
‘SELECT * FROM [SheetName$]‘)
SELECT * FROM helpdeskv2.dbo.rep

Error: Msg 7403, Level 16, State 1, Line 2
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.

Can anyone help, please.

Thank You
- on April 9, 2010 at 2:29 pm | Reply Madhivanan
  
  You need to register the OLEDB provider for 32 bit
  - on April 21, 2010 at 11:32 pm | Reply John
    
    And how do you register the OLEDB provider for 32 bit, Madhivanan?
    
    Thanks.
    - on April 22, 2010 at 1:37 pm | Reply Madhivanan
      
      This is listed under “more informations” here
      http://support.microsoft.com/kb/278604
on April 20, 2010 at 10:43 am | Reply Narsing

Hi Pinal

i tired to insert data into Excel sheet from the below query

INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]‘)

but it gives the following Errors

The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Pls give me the Suggestion for this Query
- on April 20, 2010 at 6:16 pm | Reply Madhivanan
  
  Make sure the file is closed when you try to run the query.
  Also make sure to read this for more troubleshootings
  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
on April 21, 2010 at 8:52 am | Reply larry

I need to query a SQL Server 2005 running on Server 2003 platform, for data to be put into an excel work book.
I do not want to run the query from the Server. I would like to have the query located in excel. (transportability)

example Server name Dell-2850SQL
database name =ClinicalData

I have the query I want to run and it returns the data I want. I just want it put in excel, and do not want anyone
to have access to the server, besides the query being run from a desktop workstation having MS office 2003.

I have tried this connection string am i on the right track?

Driver={SQL Native Client};server=Dell-2850;database=ClinicalData;Trusted_Connection=yes;
on April 21, 2010 at 11:09 pm | Reply sally

what can i do when i want to import data form exdel to database using sql 2005
i need it so badly
so plz replay it to me
thnks for advancer
on April 21, 2010 at 11:10 pm | Reply sally

sorry i mention excel not ecdel
really i want to konw it
on April 21, 2010 at 11:12 pm | Reply sally

i have another question
i had alot of tables in my database
i want know how can i get max value between that tables
- on April 22, 2010 at 1:40 pm | Reply Madhivanan
  
  Give us more informations
  
  Start with
  
  select max(col) from table
on April 25, 2010 at 1:42 pm | Reply sally

i want to Gathering the column that have the same ID in groups
then get the max col between that group

they told me use count distinct but i’m not sure for that
on April 28, 2010 at 1:05 am | Reply sally

The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Pls give me the Suggestion for this Query
- on April 28, 2010 at 12:30 pm | Reply Madhivanan
  
  Make sure the file is closed when you run the query
on April 28, 2010 at 2:43 am | Reply prabhav

I want to have live currency exchange rates from dollar to euro, dollar to pound in my database in a separate table in SQL Server 2005 Management Studio. How can I do this? Any response would be highly appreciated..
on April 29, 2010 at 11:16 am | Reply Owais A Farooqui

Kindly tell me if there is any way thorugh which we can also create a schema in Excel as well.
Waiting for you kind reply
Thanks & Regards
Owais A Farooqui
on May 4, 2010 at 5:57 pm | Reply siva

mr SAlly,

Make sure that you had created the excel file in the server.

When someone using sql server as client only gets this problem you get.

So give the path in the sql server machine.

i tried this way and solved
on May 5, 2010 at 1:49 am | Reply sally

thnks siva so much……i solve that error
by closing excel sheet before runinng
on May 5, 2010 at 1:52 am | Reply sally

hi every body ……i found another sol for exporting and importing to/from excel
i hope that is useful and it work

SQL Server Management Studio (SQL Server 2005)
1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
2. Right-click Linked Servers, and then click New linked server.
3. In the left pane, select the General page, and then follow these steps:
a. In the first text box, type any name for the linked server.
b. Select the Other data source option.
c. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
d. In the Product name box, type Excel for the name of the OLE DB data source.
e. In the Data source box, type the full path and file name of the Excel file.
f. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
g. Click OK to create the new linked server.

Import:-
CREATE PROCEDURE Import_Data
AS
BEGIN
Insert Customer
SELECT * FROM OPENQUERY(EXCELIMPPRT, ‘SELECT * FROM [Sheet1$]‘)
END

to Export:
Create PROCEDURE Export_Data
AS
BEGIN
INSERT INTO OPENROWSET
(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\\sales.xls;’,'SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key FROM [Sheet1$]‘)
SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key from Customer
END
on May 17, 2010 at 12:51 pm | Reply Kirti Darji

OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
‘Excel 12.0;HDR=NO;Database=@C:\Documents and Settings\raise20\My Documents\Preadmission\PreadmissionDetail.xlsx’,
‘SELECT F7,F8,F9 FROM [PreadmissionDetail$] where F7”’)
Give Error Like

[OLE/DB provider returned message: Failure creating file.]
Msg 7399, Level 16, State 1, Procedure Inser_PreAdmissionFromExcel, Line 16
OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ reported an error.

in SQl 2000 Database
on July 1, 2010 at 6:46 pm | Reply can

Hi Dave! Thanks for advice but i have a problem .İ tried many ways but i can’t solve it…

I’m using

Operating System Windows Server 2008 R2 Standart &
SQL SERVER 2005

I declared everything and i using this query;

SET @SQLconnect = ‘SELECT * INTO ##TMP FROM OPENROWSET(”Microsoft.Jet.OLEDB.4.0”, ”Excel 8.0;Database=’ + @XLSFileName + ”’, ”SELECT * FROM [Sheet1$]”)’
EXEC (@SQLconnect)

i take this ERROR:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

How can i solve it in 64 BIT OS ?
on July 8, 2010 at 2:16 pm | Reply krishna

i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me
on July 8, 2010 at 2:17 pm | Reply krishna

i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me
- on July 8, 2010 at 7:23 pm | Reply Madhivanan
  
  Refer method 5. It does what you want
  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
on July 9, 2010 at 3:38 am | Reply windsor

I’m able to insert the ‘ID’ column into Excel, but when I try to input the ID, productname and productdescription I get this error:

The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” supplied invalid metadata for column “ID”. The data type is not supported.

The ID datatype is INT. I don’t know why it wouldn’t be supported.

This is my insert statement:

insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=D:\Uploads\testing.xls;HDR=No;’,
‘SELECT ID,productname,productdescription
FROM [Sheet1$]‘)
select ID, productname,productdescription from tb_Products

Any thoughts on what I’m missing would be appreciated.

Thanks
on July 27, 2010 at 9:51 pm | Reply Deep

Guys ………………..!

i found the solution ……

create a new excel with some name along with columns than save as ……….Compatible with 97-2003 excel work book.

Thanks,
Deep
on September 6, 2010 at 12:31 pm | Reply hardik

what about .xlsx file ??

How to insert data from office 2010 to Sql server 2008 ?
on October 6, 2010 at 7:18 pm | Reply Abhishek

Thanks for this code .
it is working with .xls files , but i am working with .xlsx files.
I am getting the error
please paste the code for .xlsx file.

Thank you
Abhishek
- on August 23, 2011 at 6:54 pm | Reply madhivanan
  
  For MS Office versions 2010 onwards you need to use ACE provider
on October 19, 2010 at 1:16 pm | Reply Eric

Thank you all… Code is Great
on January 11, 2011 at 7:16 pm | Reply Sarat

when i run the below query in SQL Server 2005 and MSOffice2007

insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xlsx;’, ‘SELECT * FROM [Sheet1]‘) select * from member

i get following error,

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Member table, c:\contact.xlsx and Sheet1 exists. Iam running the query on a valid db.

Please help me regarding…
- on January 14, 2011 at 8:34 pm | Reply madhivanan
  
  Make sure the file is closed at the time of running this code
on January 26, 2011 at 6:02 pm | Reply Guru

I am new to SQL Server.I have few questions.Any help would be highly appreciated.

I have set of query and need to run as a schedule job.

1.First,I want to know how to redirect the output of the query in Excel format to particular location.

2.How to set a schedule task for that query.Need to be stored in the same folder.So i cant able to set a same file name for the result.
- on January 28, 2011 at 3:51 pm | Reply madhivanan
  
  1 Read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
  2 Read about job in sql server help file
on March 23, 2011 at 1:46 pm | Reply Ram

yes its working but i want that the excel should be overwrite instead of just appending records to it .

i mean every time i execute this query to insert records from sql table to excel it should fill with the 1st row . but its appending thats not desireable in my case.

PLS HELP . M LOOKING FOR IT FOR QUIET A DAYS.

THANKS
- on June 15, 2011 at 3:44 pm | Reply neelima
  
  Did you got any solution please share…..
on March 31, 2011 at 12:25 am | Reply talles

My query is correct,but when I change the file xls (values from the collumns).My query keep the last result.Why happend this?
Thanks for your attention!
- on April 1, 2011 at 11:22 am | Reply madhivanan
  
  Post the code that you have used
on April 12, 2011 at 9:34 pm | Reply Tiffany

I ran this script as a select * successfully with no errors, but the spreadsheet is missing data past the 11th column?
on April 25, 2011 at 12:15 pm | Reply Shrikant Shelke

USE [Volkswagen1];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 vLogin_UserID, VLogin_Password,vLogin_UserType,userCreationDate,lastLoginDate
FROM Tbl_Login
GO

This code is run on local server successfully.But when i use Tbl_Login table which is present in server database (another PC) not work, error occurred.
suggest the changes.
- on April 26, 2011 at 6:49 pm | Reply madhivanan
  
  You need to change the path of excel file
on April 26, 2011 at 5:54 am | Reply Mir

I ran the query mentioned above and got this error.
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I know it does not work on a 64 bit machine. Is there any work around to that.

Thanks,
on May 3, 2011 at 2:30 pm | Reply Anand M

what i can do for this error message :

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 did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

can any one know ple tell me …….
on May 3, 2011 at 4:33 pm | Reply Soma

Hi Pinal,
I got error while trying to read excel file from network path

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

here is my code

insert INTO Temp_TS_Contract_Document_Staging
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=\\computername\PDFAttachment\format_images1.xls’,'SELECT * FROM [Sheet1$]‘)

while its works fine with the local system
insert INTO Temp_TS_Contract_Document_Staging
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=C:\format_images1.xls’,'SELECT * FROM [Sheet1$]‘)
- on May 4, 2011 at 8:02 pm | Reply madhivanan
  
  It means that the server doesnot have JET provider. Also the file should be in server’s directory
on May 24, 2011 at 1:18 pm | Reply Robin

Hi

I am stuck up with a problem. If I try

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=E:\test.xls’, [Sheet1$])

the query executes properly.

But if i try

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’E:\test.mdb’;'Admin’;”, tmptable)

im getting the error

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Please advice me what should i do.
- on May 24, 2011 at 2:53 pm | Reply madhivanan
  
  Make sure that the file is not opened when you run the code
  - on May 25, 2011 at 9:35 am | Reply Robin
    
    File is not open
on June 15, 2011 at 12:56 pm | Reply neelima

great code..thanks
on June 21, 2011 at 12:44 am | Reply Amar

Hi,

Found error when access from excel data.the error is…

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

any one can healp on that…..
- on August 23, 2011 at 6:52 pm | Reply madhivanan
  
  Make sure the server has Microsoft.Jet.OLEDB.4.0 provider
on June 30, 2011 at 3:32 pm | Reply Vinay

Hi,

I am using the same first code but I am getting the same error as

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Everything I have done according to the instruction and even the file is not opened while executing the commands.

Please suggest the needful to be done.
- on June 30, 2011 at 7:05 pm | Reply madhivanan
  
  Make sure the server has jet provider registered
on July 29, 2011 at 1:53 am | Reply Balu

Hi All,

I need compare ID Column in existing SQL table and new Excel Sheet.

How to write Query
- on July 29, 2011 at 3:21 pm | Reply madhivanan
  
  Select t1.* from your_table as t1 inner join
  (
  select * from Openrowset….. — as shown in the post
  ) as t2 on t1.Id=t2.id
on July 29, 2011 at 3:49 pm | Reply balu

Select t1.account_no from newtest as t1 inner join
(
select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=C:\Documents and Settings\kyros\Desktop\Delwise sub base.xls;HDR=YES’,
‘SELECT * FROM [Sheet1$]‘)
) as t2 on t1.account_no=t2.account_no

Hi Thanks. It work fine.

i need to insert a new table in SQL database that matching records. How to right Query

Please Help me
- on July 29, 2011 at 5:01 pm | Reply madhivanan
  
  insert into new_table (col_list)
  Select t1.account_no from newtest as t1 inner join
  (
  select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0′,
  ‘Excel 8.0;Database=C:\Documents and Settings\kyros\Desktop\Delwise sub base.xls;HDR=YES’,
  ‘SELECT * FROM [Sheet1$]‘)
  ) as t2 on t1.account_no=t2.account_no
on July 29, 2011 at 3:51 pm | Reply balu

i need to insert on Excel data only. how to write the query
on July 29, 2011 at 4:46 pm | Reply Balu

Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

Ex: This is SQL Table Columns
———————————-

month1 month2 month3 month4
400 300 200 100

This is Excel Sheet Table
——————————–
Amount
800

i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

inserting on New SQL Tables.

Ex:

Amount month1 month2 month3 month4
0 200 0 0 0

next day do the same process

How to write the Query please help me.
on July 29, 2011 at 5:20 pm | Reply Balu

Hi Thanks. Its works fine. but the Account_no column show values

for this format. i need to store whole numbers. Ex:9072790008

9.07279e+008
9.07299e+008
9.07308e+008

Please Help me
- on August 2, 2011 at 6:44 pm | Reply madhivanan
  
  The excel cell should have been formatted as text before copying
on August 2, 2011 at 10:11 am | Reply Apostar on line

Great post. Thanks for the nice read
- on August 2, 2011 at 10:58 am | Reply Balu
  
  i need solution can you help me.
on August 2, 2011 at 6:54 pm | Reply Balu

Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

Ex: This is SQL Table Columns
———————————-

month1 month2 month3 month4
400 300 200 100

This is Excel Sheet Table
——————————–
Amount
800

i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

inserting on New SQL Tables.

Ex:

Amount month1 month2 month3 month4
0 200 0 0 0

next day do the same process

How to write the Query please help me.
on August 8, 2011 at 5:25 am | Reply Congenital heart defects

Great stuff man, come and visit my site
- on August 8, 2011 at 10:26 am | Reply Balu
  
  Can you send your site URL
on August 8, 2011 at 10:25 am | Reply Balu

can you send your site address
on August 23, 2011 at 11:06 am | Reply Saravana Msk

alter procedure [dbo].[SP_ExportExcelFromSql]
as
begin
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
‘SELECT * FROM [Sheet1$]‘) select * from usermas
end

After executing this i am facing the following 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, Procedure SP_ExportExcelFromSql, Line 5
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, Procedure SP_ExportExcelFromSql, Line 5
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]“.

Could anyone help plz……
- on August 23, 2011 at 11:24 am | Reply madhivanan
  
  If the EXCEL is in read-only mode it is not possible to add data to it
  - on August 23, 2011 at 5:14 pm | Reply Saravana Msk
    
    Hi Thanks for your response madhivanan,
    
    I checked but it has permission for Read/Write…
    
    Now im using my local database server….
    
    and my file path is my local system…
    
    whether this may give any problem?
    
    Thanks in Advance
    - on August 23, 2011 at 6:48 pm | Reply madhivanan
      
      Try this
      
      alter procedure [dbo].[SP_ExportExcelFromSql]
      as
      begin
      insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
      ‘Excel 8.0;IMEX=0;Database=F:\SARAVANA\Test.xls;’,
      ‘SELECT * FROM [Sheet1$]‘) select * from usermas
      end
      - on August 23, 2011 at 7:09 pm Saravana Msk
        
        Hi madhivanan,
        
        Thanks for your support
        
        I tried this one but it showing the same issue as :
        
        OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot update. Database or object is read-only
        
        But i saw the file by Right-Clicking and its properties
        
        and it is not a read only Mode
        
        moreover in the excel file(Test.xls) i added four column names in first line as
        and No records for that is it right?
        
        ———————————————————-
        Id User Name Password User Type
        ———————————————————-
        
        So Plz give some suggestions to fix this issue
        
        Thanks in Advance
on August 24, 2011 at 11:17 am | Reply Saravana Msk

ALTER procedure [dbo].[SP_ExportExcelFromSql]
as
begin
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
‘SELECT * FROM [Sheet1$]‘) select * from usermas
end

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, Procedure SP_ExportExcelFromSql, Line 4
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, Procedure SP_ExportExcelFromSql, Line 4
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]“.

Still i am facing this error

Can anyone help me to solve this problem

Thanks in Advance
on August 26, 2011 at 1:32 pm | Reply bhai

bhai 1st create excel and then try doin the same thing
on September 10, 2011 at 10:52 am | Reply subh

Hi,

I am getting problem at the time of uploading “The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered” please help
on October 7, 2011 at 1:54 pm | Reply Gauri

Hi ,
I want to export sql server 2005 table to excel 2003(windows 7).
i used this query:
INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\n\testing.xls;’, ‘SELECT * FROM [Sheet1$]‘)
SELECT * FROM dlr_master
All column names in both sql server table and excel file are same.
Excel file is not open.
Path is perfect.

Still I m getting the error,
Cannot Initialize Datasource Object Of OLEDB Provider “Microsoft.Jet.OLEDB.4.0″ for Linked server “(null)”

I feel its Windows 7 platform thats creating a conflict because this query runs well on Windows Xp.
Please suggest the right query and versions required

Thanks
- on October 14, 2011 at 7:35 pm | Reply madhivanan
  
  Make sure that the file is closed at the time of execution and check if the JET driver for EXCEL is installed in the Server
  - on October 15, 2011 at 10:31 am | Reply Gauri
    
    Hello there..
    Can u please guide me as to how to check whether the JET driver for excel is installed in the server?
    
    Thanks
    - on October 15, 2011 at 1:07 pm | Reply Gauri
      
      Hi,
      
      I checked it. i have 6.0 version for Excel driver on my Windows 7.
      Now can u suggest what i need to do?
      
      Thanks
    - on October 17, 2011 at 3:21 pm | Reply madhivanan
      
      You can know it via Control panel–>Admistrative tools–>Data sources (ODBC)
on October 14, 2011 at 8:48 pm | Reply Pablo

Great code, thanks a lot!!, it really works
on November 26, 2011 at 3:54 pm | Reply Shiv

Hello ALL,

I am getting this error, can any help to get rid this of

OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Regards,

Shiv
on December 2, 2011 at 5:36 pm | Reply ghulam haider

Excellent……. Excellent…….. Excellent……… 100% fit solution complete solution. Bundle of thanks. Your solutions are really have authority in SQL…
on February 10, 2012 at 10:21 pm | Reply Sinaman

beautiful solution, thank you. The codes are fine. But I have problem here. After execute the insert statement, the data always appear starting different row. How I can do if I want the data always start at A2 since my data is linked to charts. I need to update the weekly.
on April 4, 2012 at 7:30 pm | Reply rajendra

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.
on April 4, 2012 at 7:50 pm | Reply jat16may

problem:
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “TestLinkServer”.
on April 7, 2012 at 5:52 pm | Reply Motafa Elmasry

Hi Sir when i try to run yoour Script sql Server give me this Error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

Note : im not open the Ecexl File its Close When i run this script
on April 9, 2012 at 7:50 pm | Reply mallis

its very help full to mee
on May 10, 2012 at 12:08 pm | Reply balu

thnks
on May 10, 2012 at 12:09 pm | Reply ram

Print ‘Creating Proc’
GO
Create Proc GetMonthlyDownloadLogDetails
/* *****************************************************************************
** Name: GetMonthlyDownloadLogDetails
** Desc: To Get DownloadLogDetails Monthly Wise
** Auth: BalaKrishna Hari
** Date: 10/05/12
********************************************************************************/
AS
SET NOCOUNT ON;

Select convert(varchar,reqtime,103)
,Appid,channel,bill_rescode
,Unitprice,count(*) Requests
,SUM(Case When dwnstatus=’Y’
Then 1 Else 0 End) as Success
,SUM(Case When dwnstatus ‘Y’ Then 1 Else 0 End) as Failures
From cms_download_logs (nolock)
Where DATEPART(MM,reqtime) =DATEPART(MM,dateadd(MM,-1,getdate()))
And DATEPART(MM,reqtime) = DATEPART(YY,getdate())
And appid Not Like ‘%_FREE’
Group By Convert(varchar,reqtime,103)
,appid
,channel
,bill_rescode
,unitprice
Order By convert(varchar,reqtime,103),appid

Go

–Print ‘Creating Proc For BCP’

–GO

Declare @path varchar(100)
,@CMD varchar(1000)
SELECT @path = ‘C:\MonthlyDownloadLogDetails\MonthlyDownloadLogDetails_’ + CONVERT(VARCHAR,GETDATE() ,110)
SELECT @CMD = ‘bcp “GetMonthlyDownloadLogDetails” queryout ‘ + @path +’ -S.\sql2008r2 -T -c’
SELECT @CMD
EXEC XP_CMDSHELL @CMD
on June 19, 2012 at 12:11 pm | Reply Dinesh Vishe

If record are more than 65535 rows then csv can used.

EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

BULK
INSERT CSVTest
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO
on September 4, 2012 at 4:31 pm | Reply sonichintesh

Hi Gurus, when I have run the below query to export to excel ;
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\test.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

I have got below given error..pls help me to get it done…

“Msg 7308, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.”

Thanks..

Chintesh Soni
on October 31, 2012 at 7:02 am | Reply SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video « SQL Server Journey with SQL Authority

[...] SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet [...]
on November 6, 2012 at 11:28 am | Reply Ashish Bedmutha

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\test.xls;’,
‘SELECT * FROM [Sheet1$]‘)
SELECT * FirstName, LastName
FROM Person.Contact

It takes long time to execute.can u please tell me how to make it faster
on January 12, 2013 at 7:00 am | Reply SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

[...] Export Data From SQL Server 2005 to Microsoft Excel Datasheet [...]
on March 30, 2013 at 10:18 pm | Reply Mustafa Salman

Hi All
When I Execute above code got error

Msg 7308, Level 16, State 1, Line 2
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Any Suggest Please
on May 15, 2013 at 3:42 am | Reply neldimgomez

Hi pinal, how to do it in sql server 2008 with excel 2010?