OpenRowSet Error - SQL Server Forums

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!

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources