SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 OpenRowSet Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goss
Starting Member

USA
24 Posts

Posted - 08/20/2014 :  08:07:27  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/20/2014 :  09:47:14  Show Profile  Reply with Quote
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!

Edited by - sz1 on 08/20/2014 10:07:06
Go to Top of Page

goss
Starting Member

USA
24 Posts

Posted - 08/21/2014 :  14:33:59  Show Profile  Reply with Quote
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

Sweden
30276 Posts

Posted - 08/21/2014 :  16:36:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
24 Posts

Posted - 08/21/2014 :  21:28:56  Show Profile  Reply with Quote
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

Sweden
30276 Posts

Posted - 08/22/2014 :  01:59:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000