June 12, 2009 at 11:56 am
Hi guys, good day!
I have been looking around the web about an error that im getting when i try to obtain some info from an excel file to a table variable.
The error is:
Spanish (original) from sql
Msg 7303, Level 16, State 1, Line 1
No se puede inicializar el objeto de origen de datos del proveedor OLE DB "Microsoft.ACE.OLEDB.12.0" para el servidor vinculado "(null)".
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
My poor translation.
The data source object cannot be initialized from the provider OLE DB "Microsoft.ACE.OLEDB.12.0" for the linked server "(null)".
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
I have checked that nothing is using the excel files. But the WEIRDEST thing is, when i go to open the excel file, the recovery file window show me a lot of files to recover.
So i think that the execution of the OPENROWSET is letting the files open or creating copies or some strange operation.
¿Anyone knows if after calling an OPENROWSET statement u should do a CLOSE something statement?
I attached a simple excel 2007 file with 4 lines of data, 1 header, and 3 rows with data.
and
Here's the sample of the code that i use to extract the data.
declare @cadena1 VARCHAR(800),
@strArchivo varchar(500),
@strNomPlan varchar(50)
set @strArchivo = 'C:\PronVentas.xlsx'
set @strNomPlan = 'Sheet1'
declare @tbl table (ITEMNMBR char(31),CANT_PREVISTA numeric(9),ID INT IDENTITY(1,1))
set @cadena1 = 'Select * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
''Excel 8.0;Database='+@strArchivo+';HDR=YES'',
''SELECT * FROM ['+@strNomPlan+'$]'')'
insert into @tbl exec (@cadena1)
--from here i use the data for simple @PRINT stuff
I have been looking and the closest thing i've seen is that the OPENROWSET leaves ODBC connections open, but just in a no free acces forum that i cant see any responses, maybe one of u have passed through this before and have a good advice about what to do or check.
Any suggestion would be greatly appreciated.
Thanks in advance!
June 12, 2009 at 12:30 pm
First I had to download the driver. That's here.
Then, I had to change "Excel 8.0" to "Excel 12.0", as per the directions on that page.
Then I had to turn on ad hoc OPENROWSET (surface area configuration).
Then it worked.
Probably, all you need to do is change the 8 to a 12 and you'll be fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 1:03 pm
Sorry i forgot to mention those details. Just in case in the future somebody enter this thread. u need to run this code to enable ad.hoc distributed queries before using OPENROWSET, thanks for providing the link to download the drivers for Office 2007 formats with openrowset, i missed posting that too.
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
--then your procedure
i tried my procedure running remotely on the server and it worked, and then into a friend machine and worked too, but im still getting the same error That something is using the resources. Obviusly guess the installments of the AccesDatabaseEngine have some issues in my machine, even changin the Excel8.0 to 12.0 gives me the same error on my machinee. guess ill look more into the running processes or something but atleast i know the code is not the problem.
Thanks for the help Gsquared 😀
June 12, 2009 at 1:06 pm
Something that might be useful is the app at this link: http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
It can show you which process is using a particular file.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2009 at 1:14 pm
Excellent, exactly what i need , thanks :w00t:
June 15, 2009 at 7:26 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2010 at 11:27 am
a.] The advanced SQL Server configuration option 'Ad Hoc Distributed Queries' must be set to 1.
b.] Please remember to close the Excel sheet before each execution of the code.
c.] I personally find OPENROWSET more user-friendly.
d.] In some cases depending on your version of SQL Server, you may have to use the following as well to make the registry change:
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DisallowAdHocAccess', 0
GO
February 11, 2010 at 11:29 am
December 10, 2015 at 3:43 am
I used openrowset method to read the excel file, it is perfectly working, when keep the file in other network location, except one location, cal u please elaborate, what could be the reason
Regards,
Subir Das
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply