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

Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
skull52

8/22/2022 - Mon
Mark Wills

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 ?
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
Mark Wills

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
Christopher Kile

Reinstall the Microsoft Access Database Engine 2010 Redistributable on the affected server and see if you still have the issue.
Christopher Kile

you can get that Redistributable here.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
skull52

ASKER
cpkilekofp,
Did that sill an issue.
Christopher Kile

What is the operating system of the server on which this is failing?  what other things are installed on this server?
skull52

ASKER
Server 2K3 R2 , SQL Server 2008 R2, Office 2007
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Wills

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 Kile

Are you running in 64-bit or 32-bit?
skull52

ASKER
32bit
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
skull52

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