Importing Excel File into SQL Server fails

skull52
skull52 used Ask the Experts™
on
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" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
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 NULL

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
OK, first thing to try is to copy \\AMCHAR_GP1\NotSold\NOSALE1YR.xlsx to the local C:\ drive where your SQL Server Instance is running.

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 ?
skull52IT director

Author

Commented:
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" 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.ACE.OLEDB.12.0" for linked server "(null)".

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
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

skull52IT director

Author

Commented:
Mark,
I removed the the UNC path from the script and it is now D:\NotSold\NOSALE1YR.xlsx. I did get it to work one after a reboot of the server but subsequent attempts failed. I works on 2 different servers one running Server 2008 64bit and one running Server 2003 R2 32bit. This is driving me nuts. Also the file was not open and it has write access.
Christopher KileSenior Software Analyst

Commented:
Reinstall the Microsoft Access Database Engine 2010 Redistributable on the affected server and see if you still have the issue.
Christopher KileSenior Software Analyst

Commented:
you can get that Redistributable here.
skull52IT director

Author

Commented:
cpkilekofp,
Did that sill an issue.
Christopher KileSenior Software Analyst

Commented:
What is the operating system of the server on which this is failing?  what other things are installed on this server?
skull52IT director

Author

Commented:
Server 2K3 R2 , SQL Server 2008 R2, Office 2007
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Just as a left field "rule out possibilities" in SQL Server do :

ALTER DATABASE <your_db_name_goes_here> SET TRUSTWORTHY ON

Open in new window


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...
Christopher KileSenior Software Analyst

Commented:
Are you running in 64-bit or 32-bit?
skull52IT director

Author

Commented:
32bit
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
When ACE14 was first released, it was being incorrectly referred as ACE12 in the connection string. So, I wonder if reinstalling ACE12 would be a good idea.

And the 2007 is available from http://www.microsoft.com/en-us/download/details.aspx?id=23734 (or use the 2010 from the above, but, I only ever match to the office version)

The other thing to try is saving your spreadsheet as an excel 5 workbook .xls and try using the JET engine... If that works we know it is the driver, if not, then it probably points to environment...

Also, if possible, check the SQL Server that does work and look at the drivers via SSMS and the user characteristics.
skull52IT director

Author

Commented:
Thanks for all your help I think Marks solution will work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial