August 16, 2013 at 11:36 pm
Hi,
I am trying to import sample data into excel,using following code,but it gives me following error,
Create Table Test
(
a Varchar(2),
b Varchar(2)
)
Insert Into test
values ('ab','bc')
insert into
openrowset('microsoft.ace.oledb.12.0',
'excel 12.0; database= D:\test' ,
'SELECT * FROM [Sheet1$]'
)select * from test
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot initialize the data source object of OLE DB provider "microsoft.ace.oledb.12.0" for linked server "(null)".
August 17, 2013 at 6:29 am
First verify that you have the same bitness of both SQL Server. That is, SQL Server and the provider should either both be 64-bit or both be 32-bit.
Next verify that the service account for SQL Server has permission to access the Excel file, both the file itself and the the folder where it is located.
You may want to try the Import/Export wizard or an SSIS package instead of battling with OPENROWSET.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 11:28 am
Please ensure:
1. Install Office 2007 Data Connectivity Components (http://www.microsoft.com/download/en/confirmation.aspx?id=23734)
2. Change the extension of your excel file to the proper extension (Ex: .xlsx)
3. Enable "Ad Hoc Distributed Queries" on the server this is running on
4. Run: EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO
I was receiving the same errors as you, after enabling the "in process" for OLEDB, it works
Let me know how it goes for you!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply