Link to home
Start Free TrialLog in
Avatar of skull52
skull52Flag for United States of America

asked on

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" 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

Avatar of Mark Wills
Mark Wills
Flag of Australia image

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 ?
Avatar of skull52

ASKER

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
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.
Avatar of skull52

ASKER

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.
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.
Avatar of skull52

ASKER

cpkilekofp,
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?
Avatar of skull52

ASKER

Server 2K3 R2 , SQL Server 2008 R2, Office 2007
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...
Are you running in 64-bit or 32-bit?
Avatar of skull52

ASKER

32bit
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skull52

ASKER

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