Problems reading Excel SQL Server 2012 file

Navigation
3

The error returned by the statement is:

  

Msg 7303, Level 16, State 1 sql server 2012 Microsoft.ACE.OLEDB.12.0 

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
                ,'Excel 12.0 Xml;Database=\path\archivo_excel.xls;HDR=YES'
                ,'SELECT * FROM [Sheet1$]')

I did what they indicated in this blog: link

And the problem persists, from what I have seen this page is not the only one with this topic, there are several that indicate even the same solution.

Does anyone have a solution?

    
asked by Javier Medina 14.10.2016 в 20:30
source

2 answers

1

Depends on so you need to read the excel, because if you want to load it in SQL Server you could use the Management Studio to upload it to a table and already there you make the queries that you occupy.

Import data from an Excel Worksheet to SQL Server 2008 R2

Or you could even use SSIS packages to import an excel to SQL Server

Using SSIS packages to import MS Excel data into a database

    
answered by 14.10.2016 в 22:39
0

This answer I placed a few minutes ago in another thread, but in the end it is the same problem.

  • Make sure you have the connection components or drivers.
  • You must assign the SQL Server service a domain user with sufficient privileges to use the network resource you are trying to connect to.
  • Execute the query I show later.
  • Do not forget to add the login of the user you created for the service SQL Server with the role of sysadmin .

    • This is the query that I mentioned in point # 3: 

    use master 
go 
sp_configure 'show advanced options', 1 
reconfigure 
go 
sp_configure 'Ad Hoc Distributed Queries', 1 
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
        
    answered by 20.10.2016 в 15:27
    Send token Jwt from php Create pdf report with ireport in netbeans with JSON as datasource