Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 OpenRowSet Error

Author  Topic 

goss
Starting Member

29 Posts

Posted - 2014-08-20 : 08:07:27
Hi All,

Using Developer Edition SQL Server 2012 on /local.

I ran this query to configure to allow ad hoc queries:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE


The Target Table definitely exists in the database. The source file definitely exists in the source path.

Still, I receive this error when I run the query:
quote:
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)".


What else can I try?

thx
w

query:

SELECT * INTO tblTest FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\NFL Teams.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')


Kind regards,
-w

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-20 : 09:47:14
Is the sheet closed? do you have permission to the share, do you have headers in the sheet, are you local admin, you can also check the registry settings and change:

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


For more info:

http://www.sqlservercentral.com/blogs/sqlandme/2013/04/15/sql-server-import-data-from-excel-using-t-sql/

You can use SSIS for this task.

Alternatively, you could bulk insert but you would need the table with the correct columns ready:

BULK INSERT #YourTable
FROM 'C:\Data\NFL Teams.xlsx'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO


We are the creators of our own reality!
Go to Top of Page

goss
Starting Member

29 Posts

Posted - 2014-08-21 : 14:33:59
Thanks sz1,

Is the sheet closed? Yes
do you have permission to the share, Yes
do you have headers in the sheetYes,
are you local adminYes,

I ran this query:

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 tried running the OPENROWSET(). I still get the error message:
quote:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".




Any other thoughts?

thx
w

Kind regards,
-w
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-21 : 16:36:34
Is "C:\Data" on your local computer?
Remember that all paths are relative to the database server.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

goss
Starting Member

29 Posts

Posted - 2014-08-21 : 21:28:56
Thanks SwePeso,

Yes, everything is on my laptop.
Developer Edition SQL Server 2012

Kind regards,
-w
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-22 : 01:59:01
Try putting double quotes around the path

SELECT * INTO tblTest FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database="C:\Data\NFL Teams.xlsx";HDR=YES',
'SELECT * FROM [Sheet1$]')


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -