"SQL Server" Server Error "DELETE" and "INSERT" from Excel

Navigation
0

I have created a procedure that first deletes the records from my table and then imports them from excel. 

DELETE tabla WHERE columna='dato'
INSERT INTO tabla
  SELEC * 
  FROM OPENDATASOURCE
    ('Microsoft.ACE.OLEDB.12.0',
        'Data Source=\compartido\carpeta\archivo_excel.xlsm;Extended Properties=Excel 12.0'
    )...[tbHoja$]

When executed, it deletes the records. But when trying to connect to Excel to recover the data I get this error message:

  

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.

     

Can not initialize the data source object of OLE DB provider   "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I tried to create a procedure where only the import is executed and it works, but what I need is that when calling a single procedure it is deleted and imported.

Since to solve this I had to restart the service of the server and until now it takes time, besides the ideal is that it is automatic and that the executions are not so manual.

    
asked by Andrea S. B. 20.10.2016 в 01:32
source

1 answer

0

  • 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 following:

    • Query: 

    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
  • Do not forget to add the login of the user you created for the SQL Server service with the sysadmin role
    • answered by 20.10.2016 в 15:11
    Problems with weka in python Collation problem in BD and PHP