Problem to get data from Excel 2007

Question

Problem to get data from Excel 2007

shohelr2003

Hall of Fame

Points: 3535
More actions
February 23, 2014 at 10:54 pm

#282784

I use SQL Server 2008 R2 (x32) and Office 2007. I installed AccessDatabaseEngine(x32). Then I execute the following statement.
select * from openrowset(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=D:\Book1.xlsx;HDR=YES',
'Select * from [Sheet1$]'
)
And received the following error.
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".
Please help to get out of this stack.
Thanks

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

shohelr2003 (2/23/2014)
I use SQL Server 2008 R2 (x32) and Office 2007. I installed AccessDatabaseEngine(x32). Then I execute the following statement.
select * from openrowset(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=D:\Book1.xlsx;HDR=YES',
'Select * from [Sheet1$]'
)
And received the following error.
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".
Please help to get out of this stack.
Thanks

First thought is - Where is the spreadsheet you are trying to open ? With the command you are using, it will need to be on drive D: on the machine that you are running SQL Server on (not your workstation)

Second thought - Have you installed the drivers for Excel on the server ?

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 2

@happycat59,

Thanks for your thought.

First thought: The file is on drive D: on that machine.

Second thought: Yes, I have installed the driver named "AccessDatabaseEngine".

At last I could successfully execute the statement and it retrieved data from excel sheet. But there is a matter of surprising that again it throws an error. Sometimes it retrieves data from excel sheet and sometimes not.

I am not getting any clue.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

I created a user "shohel" with Administrative rights in windows and I ran SQL Server Services by this user. I use Windows 7 Ultimate. The problem is sometimes I get result and sometimes it throws an error message. I am not getting any clue. I have tried all of the combinations as follows.

exec sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

exec sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

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

I executed it in the command prompt as Administrator

icacls C:\Users\shohel\AppData\Local\Temp /grant shohel: (R,W)

icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant shohel: (R,W)

icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant shohel: (R,W)

[p] Even I tried the following.

1. Open SQL Server Configuration Manager.

2. Select the SQL Server Services folder in the left pane.

3. Right-click the SQL Server (MSSQLSERVER) service in the right pane.

4. Click Properties.

5. Click the Advanced tab in the properties dialog that pops up.

6. Add “-g512;” to the front of the value for parameter “Startup Parameters”.

7. Click OK.

[/p]