Error While Running Node-mssql Query

I am trying to run node-mssql query, if I run simple quires it is getting executed. But when I use OPENROWSET and Microsoft.ACE.OLEDB.12.0, it is showing some errors. Here is serv

Solution 1:

I see few syntax errors in your code:

Sample code for OPENROWSET is:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

1. In your code extra ' between the Excel and Database keywords ,\'Excel 12.0\';\'Database=D:\\sample ..., that need to correct

2. The SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$] need ' before and after.

So your working code will be:

request.query('INSERT INTO [mytable](SalesPersonID, TerritoryID)' + 
                ' SELECT SalesPersonID, TerritoryID FROM OPENROWSET('  + 
                '\'Microsoft.ACE.OLEDB.12.0\', \'Excel 12.0;Database=D:\\sample\\test\\data\\1540_OPENROWSET_Examples.xls;HDR=YES\', ' + 
                '\'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$]\')',function(err,recordset){
    if(err) console.log(err)

---

Update :

The OP received this configuration error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.

To fix the configuration error, the below script need to execute:

USE [master] 
GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO