Importing Excel File into SQL Server fails
I am trying to import an excel file into SQL Server 2008R2, the excel file is on the server I am importing from I have checked the file and it looks fine but I am getting the following error;
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [CODE$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [CODE$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NOSALE1YR]') AND type in (N'U'))
DROP TABLE [dbo].[NOSALE1YR]
GO
SELECT * --INTO NOSALE1YR
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=\\AMCHAR_GP1\NotSold\NOSALE1YR.xlsx',
'SELECT * FROM [CODE$]');
INSERT INTO SSGPR100
(SSGPROMO, ITEMNMBR, CUSTNMBR, STRTDATE, ENDDATE, SSG_Customer_Price, MRKDNAMT, PRICELVL, UOFM)
SELECT n.SSGPROMO,
n.ITEMNMBR,
n.CUSTNMBR,
n.STRTDATE,
n.ENDDATE,
n.SSG_Customer_Price,
n.MRKDNAMT,
n.PRICELVL,
n.UOFM
FROM NOSALE1YR n
left join SSGPR100 s ON n.SSGPROMO = s.SSGPROMO
WHERE s.SSGPROMO IS NULLASKER
Well the permissions issue is resolved, but now I am getting
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"
I have installed the 2007 Access Database Engine, I am using Office 2007, the server is Server 2003 32Bit, I have run the
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Script but still I get the error.... Help
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"
I have installed the 2007 Access Database Engine, I am using Office 2007, the server is Server 2003 32Bit, I have run the
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Script but still I get the error.... Help
OK, that can happen because it is already open, or, dont have write access.
It is a problem message because it is so dang generic, but typically associated with not having exclusive access.
Can you please confirm that SQL Server had exclusive access ?
You might still like to try the local C:\ drive as well - when I get into problems, I first try to eliminate as many other factors and having it a local file often helps isolate different problems (because of that 7399 message) and Just for a test at this stage.
It is a problem message because it is so dang generic, but typically associated with not having exclusive access.
Can you please confirm that SQL Server had exclusive access ?
You might still like to try the local C:\ drive as well - when I get into problems, I first try to eliminate as many other factors and having it a local file often helps isolate different problems (because of that 7399 message) and Just for a test at this stage.
ASKER
Mark,
I removed the the UNC path from the script and it is now D:\NotSold\NOSALE1YR.xlsx.
I removed the the UNC path from the script and it is now D:\NotSold\NOSALE1YR.xlsx.
Reinstall the Microsoft Access Database Engine 2010 Redistributable on the affected server and see if you still have the issue.
you can get that Redistributable here.
ASKER
cpkilekofp,
Did that sill an issue.
Did that sill an issue.
What is the operating system of the server on which this is failing? what other things are installed on this server?
ASKER
Server 2K3 R2 , SQL Server 2008 R2, Office 2007
Just as a left field "rule out possibilities" in SQL Server do :
Bit strange that it worked once, but fails often....
Any chance we can see the excel file so we can see if we can import and rule out the excel file itself ? Or, create a simple/easy spreadsheet and try that - if it fails - post it so we can play...
ALTER DATABASE <your_db_name_goes_here> SET TRUSTWORTHY ONBit strange that it worked once, but fails often....
Any chance we can see the excel file so we can see if we can import and rule out the excel file itself ? Or, create a simple/easy spreadsheet and try that - if it fails - post it so we can play...
Are you running in 64-bit or 32-bit?
ASKER
32bit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your help I think Marks solution will work.
Reason being, the account used to start the SQl Server Service is the windows account that must have full access to that path.
And if your windows account used to start SQL Server does have full access, might actually need to map the path (but not so sure about that, used to be a UNC problem) in the win login for that windows account
If you can import from C:\ then it is likely to be the account. If you cannot, then it might be the file - the sheet names are case sensitive, and you should not have it open yourself.
If still a problem, is it possible to post that spreadsheet ?