January 20, 2008 at 4:12 am
Can someone please help me.
I executed these Commands
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
--- Error message
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
---I then executed this query
--Insert Data into an EXCEL spreadsheet
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database = D:\CreditCard\CampaignData.xls;',
'SELECT * FROM [Sheet1$]')
SELECT
Campaign,
ContactNumber,
[Name],
IDNumber,
NewRepeatCat,
OriginalLoanOfficer,
CreationDate,
OrganogramDescription,
ClientNumber,
LoanID,
AccountNumber,
CardNumber,
FinalLoanOfficer,
FinalBranch,
Status,
CardStatus,
StraightLimit
Capital,
StartDate
FROM #CC1
ORDER BY CreationDate
I I got the error message below. How do I fix this? Is it also possible to delete data in the spreadsheet before
inserting.
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
January 20, 2008 at 8:05 pm
I may need to run sp_addlinkedserver before uring your query command.
January 21, 2008 at 2:56 am
May be the requested OLE DB provider is not installed in your system. Run component checker ( MDAC) and download the required version from the site. Call up MS Support for the above they will check and download it for you. It may be a free service to guide you to get the correct version of Mdac.
"More Green More Oxygen !! Plant a tree today"
January 22, 2008 at 10:08 am
raym,
I second the recommendation to verify your MDAC components.
Troubleshooting Guide:
Component Checker: Diagnose problems and reconfigure MDAC installations
http://support.microsoft.com/kb/307255/
You can download the MDAC Component Checker software tool here:
MDAC Utility: Component Checker
The most recent MDAC version(s) can be found at:
Microsoft Data Access Components (MDAC) 2.8
and
Microsoft Data Access Components (MDAC) 2.8 SP1
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 24, 2008 at 10:45 am
Actually, you may want to try creating a linked server and see if you get the same error. I scripted a linked server and got the message "The OLE DB provider "MSDAORA" has not been registered." only because the linked server had a missing or wrong configuration. So you might want to do a little testing first. I always found it easier to use a linked server instead of OPENROWSET--but that's just me 🙂
February 22, 2009 at 8:58 pm
If you are running 64 bit SQL Server 2005 it may not be possible.
I found the the MDAC linked server for Excel only works in 32 bit.
I use SSIS to import Excel Data into 64 bit SQL 2005.
March 20, 2009 at 11:35 am
has anyone have success with the latest 64-bit OleDB driver. I have installed the 64-bit Oledb driver but still not able to import Excel data into a sql db.
December 30, 2009 at 2:03 pm
I did not mess with the 64 bit MDAC... the version on server 2008 was higher than the listed MDAC patch so I believe the problem lay elsewhere.
I checked a couple of other items. First, that it was possible that the registration needed a kick start so I simply added a dummy Access database to the linked servers and seeing it was there in full, I found another issue that resolved my problem.
In the project file, Configuration Properties, Debugging, there is an option to set 64 bit debugging to true or false. Once it was set to false, I stopped receiving the error. I'm not sure the answer was just the one step, so I include the other step (Jump Start above) as a reference.
Jamie
February 20, 2010 at 9:49 pm
Please explain in more detail about the fix you found "In the project file, Configuration Properties, Debugging, there is an option to set 64 bit debugging to true or false."
I am also receiving the error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine." when trying to run custom software on my machine. System is Windows Vista Home Premium Service Pack 2 64-bit Operating System.
I dropped msjetoledb40.dll directly into the System 32 folder but that did not work.
February 21, 2010 at 7:04 am
In the project file, Configuration Properties, Debugging, there is an option to set 64 bit debugging to true or false. Once it was set to false, I stopped receiving the error.
If the question was directed elsewhere, my apologies - otherwise, this is all I have.
Jamie
August 10, 2010 at 5:00 am
try installing 'Microsoft Access Database Engine 2010 Redistributable' from http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
and change your code to use Microsoft.ACE.OLEDB.12.0 instead of Microsoft.JET.OLEDB
December 7, 2010 at 11:19 am
minto.antony (8/10/2010)
try installing 'Microsoft Access Database Engine 2010 Redistributable' from http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=enand change your code to use Microsoft.ACE.OLEDB.12.0 instead of Microsoft.JET.OLEDB
I agree with "minto.antony", this is your best bet.
See
http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!848.entry
for the "bug in documentation" and for a few example connection strings.
January 18, 2012 at 12:50 am
This happens when you are trying to connect to source like Excel from 64 bit SQL Server, it works well had it been 32 bit.
As a workaround, you could use the separate tool which comes with SQL Server 64 bit designed to run with 32 bit features. Its called "Import and Export Data (32-bit)". You would find it under the installation directory in Start > All Programs > Microsoft SQL Server 2008 for Windows machines.
Try it out and you should be able to import/export data without any problem. I was able to do and I hope it helps.
Thanks,
Syamjith
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply