Export excel from MS SQL Server 2019

The title says it all.
Command:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\ThienPham\Documents\export\export.xlsx;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM [DATA].[dbo].[ddt]

i excuted it on MS SQL Server 2019 software. it's good. But when i excute it on Node-red, it's not work.
message: "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"."
This is my flow
image
function code:

ena ="INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\ThienPham\Documents\export\export.xlsx;', 'SELECT * FROM [Sheet1$]') ";
ena = ena + "SELECT * FROM [DATA].[dbo].[ddt]";
msg.topic = ena;
return msg;

infor
OS: Window 10 pro x64
MS SQL 2019
SQL authentication
Node-RED version: v1.2.9
Node.js version: v15.13.0
Help me! . Thanks

What SQL user is setup on the MSSQL config node? Does it have the correct permissions.

Apart from that, i am unsure if the node-js SQL Server driver supports what you are attempting.

An alternative solution is to simply select the data and pass it to a CSV node then file out node (or use one of the contrib nodes that write to excel)

Thank you for your help. i found my issue. The problem is my function

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.