Error while running node-mssql query

Question

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 server.js code

var express = require('express');
var app = express();
app.get('/', function (req, res) {

var sql = require("mssql");

// config for your database
var config = {
    user: '..',
    password: '....',
    server: 'localhost\\SQLEXPRESS', 
    database: 'test_databasae' 
};

// connect to your database
sql.connect(config, function (err) {

    if (err)
         console.log(err);
     else
        console.log("Connection successful");


    // create Request object
    var request = new sql.Request();

    // query to the database and get the records
    /*request.query('select * from table1', function (err, recordset) {

        if (err) console.log(err)

        // send records as a response
        res.send(recordset);

    });*/
    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;,\'' + 
                    'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$])',function(err,recordset){
        if(err) console.log(err)

        console.log("success");
    }); 
});});var server = app.listen(5000, function () {
console.log('Server is running..');});`

and when i hit node server.js on command prompt I am getting following errors on command prompt:

Server is running..
Connection successful
{ [RequestError: Incorrect syntax near ')'.]
  name: 'RequestError',
  message: 'Incorrect syntax near \')\'.',
  code: 'EREQUEST',
  number: 102,
  lineNumber: 1,
  state: 1,
  class: 15,
  serverName: 'localhost\\SQLEXPRESS',
  procName: '',
  precedingErrors:
   [ { [RequestError: Incorrect syntax near the keyword 'SELECT'.]
       name: 'RequestError',
       message: 'Incorrect syntax near the keyword \'SELECT\'.',
       code: 'EREQUEST',
       number: 156,
       lineNumber: 1,
       state: 1,
       class: 15,
       serverName: 'localhost\\SQLEXPRESS',
       procName: '' } ] }
success

The same query is getting executed in SQL Server Management Studio , it is successfully inserting excel data to database.Excel sheet data looks like this:

SalesPersonID   TerritoryID
--------        -----------
275              2
276              4
277              3

Here is the plunker link

Answer 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