OPENROWSET issue

Question

OPENROWSET issue

Greg Snidow

SSCoach

Points: 16108
More actions
November 2, 2012 at 11:42 am

#269661

Greetings all. I'm not able to get OPENROWSET to work using ACE. The below does not work...
SELECT
*
FROM OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=Yes;IMEX=1;database=c:\imports\test.xlsx',
'SELECT * FROM [Sheet1$]'
)
However, I am able to use Jet. The below does work...
SELECT
*
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;database=c:\imports\test.xls',
'SELECT * FROM [Sheet1$]'
)
I read that the ACE driver creates a temporary DSN in the admin\temp folder, and one reason it will fail is that either the user or SQL Server does not have sufficient priveledges to that folder. That sounds reasonable, but wouldn't Jet also do the same thing? On another post here, one poster indicated that running as admin solved the problem, and that ACE was not initializing when running as normal user. Before I try to explain what is going on to our IT folks, does anyone know of a good resource that would explain all the components of using OPENROWSET with SQL Server 2012 on a 32 bit windows machine? Also, I am assuming ACE is installed, since it appears in the list of available providers. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Okay, now this is really wierd. I found a solution at http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm#Grant rigths to TEMP directory. I ran...

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

and I thought it did not work at first. I put this in the editor, with *no* spaces after it...

SELECT

*

FROM OPENROWSET

(

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;HDR=Yes;IMEX=1;database=c:\imports\test.xlsx',

'SELECT * FROM [Sheet1$]'

)

And I get...

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 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Now, if I add at least one blank space after the last ')' it works! Can anyone else check and see if this happens to you?

Thanks.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

What do you get if it's followed by a semi-colon instead of a space?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Jeff Moden (11/2/2012)
What do you get if it's followed by a semi-colon instead of a space?

Jeff, this is the darndest thing. I was off Monday and Tuesday, so was all excited to come in today to test, and it worked today both with and without the trailing space. It also works with semi-colon, which is to be expected, so I have no idea what is happening. I messed with so much stuff on Friday, that I'm thinking maybe I did something that required a re-start to take effect. One thing I noticed though, is it took an inordinate amount of time to run the first time, like 5 seconds for a spreadsheet with only two cells populated. Since I can no longer access anything on my new Windows 7 machine locked down tightly by Viewfinity, I'm wondering at what point SQL Server deletes the temporary DSN created by OPENROWSET. If it keeps it until the computer shuts down, that might help explain why the statement runs almost instantaneously after the first run, since it may not have to create the DSN again. Just a guess though, because I can't access the admin\temp folder.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.