Error "Incorrect syntax near ')'." in Bulk Insert when Columns are from msg.paramQuery

I get this error when passing Columns parameter from specific msg.xx

This is the configuration:

this is the content of the msg.complete

{"paramQuery": [{"output":false, "name":"idEsecuzioneAttivita","type":"int"}, {"output":false,"name":"idUtente","type":"int"}, {"output":false,"name":"Utente","type":"nvarchar"}, {"output":false,"name":"Data","type":"date"}, {"output":false,"name":"OraInizio","type":"nvarchar"}, {"output":false,"name":"OraFine","type":"nvarchar"}, {"output":false,"name":"Minuti","type":"int"}, {"output":false,"name":"CodiceProgetto","type":"nvarchar"}, {"output":false,"name":"idCliente","type":"int"}, {"output":false,"name":"RagioneSociale","type":"nvarchar"}, {"output":false,"name":"idProgetto","type":"int"}, {"output":false,"name":"Progetto","type":"nvarchar"}, {"output":false,"name":"idAttivita","type":"int"}, {"output":false,"name":"Attivita","type":"nvarchar"}, {"output":false,"name":"idTipoAttivita","type":"int"}, {"output":false,"name":"TipoAttivita","type":"nvarchar"}, {"output":false,"name":"Numero","type":"float"}, {"output":false,"name":"TariffaAttivita","type":"float"}, {"output":false,"name":"idLuogoEsecuzioneAttivita","type":"int"}, {"output":false,"name":"LuogoEsecuzioneAttivita","type":"nvarchar"}, {"output":false,"name":"CostoAttivita","type":"float"}, {"output":false,"name":"CostoOrario","type":"float"}, {"output":false,"name":"Note","type":"nvarchar"}, {"output":false,"name":"idSettore","type":"int"}, {"output":false,"name":"Settore","type":"nvarchar"}, {"output":false,"name":"idAreaBusiness","type":"int"}, {"output":false,"name":"AreaBusiness","type":"nvarchar"}, {"output":false,"name":"idTipoProgetto","type":"int"}, {"output":false,"name":"TipoProgetto","type":"nvarchar"}, {"output":false,"name":"processato","type":"tinyint"}], "formattedData":[ ["1779763","14280","Pablo Picasso","23/02/2021","09:00","13:00","240","0110018","43682","XXX S.P.A.","60367","XXX AM","80925","XXXXX AM","17803","Sviluppo","","150,00","3727","Ufficio","","","x","","","668","XXX","","",0] ],"_msgid":"71a8d293.c9817c"}

Could you please help me if it is an issue or what i'm doing wrong?

Hi,

your setup and params looks ok but your data might be an issue. I am not setup to test this but I can see a few parameters of type int or float but your data is not numeric...

Try setting these data values to null or undefined (assuming the table permits nulls - otherwise set them to zero)

Question: is execuioni_attivita2 a table or view? could you share a CREATE script for the table?

Hi @Steve-Mcl i tried today to change the formatted data and cast the integer and float to the correct dataelement and set null or undefined. Same result, same error on the bulk insert node.

The table create script is:

CREATE TABLE [dbo].[esecuzioni_attivita2]( [idEsecuzioneAttivita] [int] NOT NULL, [idUtente] [int] NULL, [Utente] [nvarchar](255) NULL, [Data] [date] NULL, [OraInizio] [nvarchar](5) NULL, [OraFine] [nvarchar](5) NULL, [Minuti] [int] NULL, [CodiceProgetto] [nvarchar](255) NULL, [idCliente] [int] NULL, [RagioneSociale] [nvarchar](255) NULL, [idProgetto] [int] NULL, [Progetto] [nvarchar](255) NULL, [idAttivita] [int] NULL, [Attivita] [nvarchar](255) NULL, [idTipoAttivita] [int] NULL, [TipoAttivita] [nvarchar](255) NULL, [Numero] [float] NULL, [TariffaAttivita] [float] NULL, [idLuogoEsecuzioneAttivita] [int] NULL, [LuogoEsecuzioneAttivita] [nvarchar](255) NULL, [CostoAttivita] [float] NULL, [CostoOrario] [float] NULL, [Note] [nvarchar](max) NULL, [idSettore] [int] NULL, [Settore] [nvarchar](255) NULL, [idAreaBusiness] [int] NULL, [AreaBusiness] [nvarchar](255) NULL, [idTipoProgetto] [int] NULL, [TipoProgetto] [nvarchar](255) NULL, [processato] [tinyint] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

@fabioperencin

could you knock up a minimal flow that demonstrates this by using and inject (to provide the sample data) and your MSSQL node.

hi @Steve-Mcl ,

I prepared the minimal flow.

thanks for help

Hi, i have published an update

could you install node-red-contrib-mssql-plus@0.7.0-beta.4

cd into your node-red folder then run npm install node-red-contrib-mssql-plus@0.7.0-beta.4

and use this flow for testing ...

[{"id":"ccdf4e4f.5393","type":"function","z":"7734b3d9.8bcfcc","name":"generate random data","func":"\nfunction randomInt(min, max) { // min and max included \n    return Math.floor(Math.random() * (max - min + 1) + min);\n}\nlet lastIndex = context.get(\"lastIndex\") || 0;\nmsg.values = [];\nconst count = 1;\nfor (let index = 0; index < 100; index++) {\n    msg.values.push({\n        \"idEsecuzioneAttivita\": lastIndex + index,//int\n        \"idUtente\": (lastIndex + 10000) + index,//int\n        \"Utente\": \"Mario Rossi\",//nvarchar\n        \"Data\": new Date(\"2021-02-23\"),//date\n        \"OraInizio\": \"09:00\",//nvarchar\n        \"OraFine\": \"13:00\",//nvarchar\n        \"Minuti\": 240,//int\n        \"CodiceProgetto\": \"0110018\",//nvarchar\n        \"idCliente\": randomInt(40000, 50000),//int\n        \"RagioneSociale\": \"XXXX S.P.A.\",//nvarchar\n        \"idProgetto\": randomInt(60000,70000),//int\n        \"Progetto\": \"XXX AM\",//nvarchar\n        \"idAttivita\": randomInt(80000, 90000),//int\n        \"Attivita\": \"XXX AM\",//nvarchar\n        \"idTipoAttivita\": randomInt(10000, 20000),//int\n        \"TipoAttivita\": \"Sviluppo\",//nvarchar\n        \"Numero\": undefined,//float\n        \"TariffaAttivita\": 250.00,//float\n        \"idLuogoEsecuzioneAttivita\": randomInt(3000, 4000),//int\n        \"LuogoEsecuzioneAttivita\": \"Ufficio\",//nvarchar\n        \"CostoAttivita\": undefined,//float\n        \"CostoOrario\": undefined,//float\n        \"Note\": \"text noter\",//nvarchar\n        \"idSettore\": undefined,//int\n        \"Settore\": \"\",//nvarchar\n        \"idAreaBusiness\": randomInt(668, 688),//int\n        \"AreaBusiness\": \"XXX\",//nvarchar\n        \"idTipoProgetto\": undefined,//int\n        \"TipoProgetto\": \"\",//nvarchar\n        \"processato\": undefined//tinyint\n    });\n}\ncontext.set(\"lastIndex\", lastIndex + count); \n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":160,"wires":[["5ad6bbd8.35def4","44dfb665.d9b868"]]},{"id":"bfa201af.b6b3d","type":"debug","z":"7734b3d9.8bcfcc","name":"MSSQL Bulk Ins","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":160,"wires":[]},{"id":"ebcb82c.a30da8","type":"inject","z":"7734b3d9.8bcfcc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":100,"wires":[["17a7b980.1084c7"]]},{"id":"5ad6bbd8.35def4","type":"debug","z":"7734b3d9.8bcfcc","name":"JSONDebug","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":220,"wires":[]},{"id":"8fc26d5d.f4888","type":"MSSQL","z":"7734b3d9.8bcfcc","mssqlCN":"8d9d212a.cea03","name":"select *","outField":"payload","returnType":"0","throwErrors":1,"query":"select * from esecuzioni_attivita2","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","rows":"values","rowsType":"msg","params":[],"x":460,"y":280,"wires":[["7ca6b9e0.209e08"]]},{"id":"6c043006.6edd5","type":"inject","z":"7734b3d9.8bcfcc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":220,"y":280,"wires":[["8fc26d5d.f4888"]]},{"id":"7ca6b9e0.209e08","type":"debug","z":"7734b3d9.8bcfcc","name":"MSSQL Select","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":280,"wires":[]},{"id":"44dfb665.d9b868","type":"MSSQL","z":"7734b3d9.8bcfcc","mssqlCN":"","name":"bulk insert","outField":"payload","returnType":"0","throwErrors":1,"query":"esecuzioni_attivita2","modeOpt":"","modeOptType":"bulk","queryOpt":"","queryOptType":"editor","paramsOpt":"columns","paramsOptType":"msg","rows":"values","rowsType":"msg","params":[],"x":470,"y":160,"wires":[["bfa201af.b6b3d"]]},{"id":"17a7b980.1084c7","type":"function","z":"7734b3d9.8bcfcc","name":"Setup columns","func":"msg.columns = [\n    { output: false, \"name\": \"idEsecuzioneAttivita\", \"type\": \"int\", options: { nullable: false } },\n    { output: false, \"name\": \"idUtente\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"Utente\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"Data\", \"type\": \"date\", options: { nullable: true } },\n    { output: false, \"name\": \"OraInizio\", \"type\": \"nvarchar(5)\", options: { nullable: true } },\n    { output: false, \"name\": \"OraFine\", \"type\": \"nvarchar(5)\", options: { nullable: true } },\n    { output: false, \"name\": \"Minuti\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"CodiceProgetto\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idCliente\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"RagioneSociale\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idProgetto\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"Progetto\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idAttivita\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"Attivita\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idTipoAttivita\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"TipoAttivita\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"Numero\", \"type\": \"float\", options: { nullable: true } },\n    { output: false, \"name\": \"TariffaAttivita\", \"type\": \"float\", options: { nullable: true } },\n    { output: false, \"name\": \"idLuogoEsecuzioneAttivita\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"LuogoEsecuzioneAttivita\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"CostoAttivita\", \"type\": \"float\", options: { nullable: true } },\n    { output: false, \"name\": \"CostoOrario\", \"type\": \"float\", options: { nullable: true } },\n    { output: false, \"name\": \"Note\", \"type\": \"nvarchar(max)\", options: { nullable: true } },\n    { output: false, \"name\": \"idSettore\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"Settore\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idAreaBusiness\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"AreaBusiness\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"idTipoProgetto\", \"type\": \"int\", options: { nullable: true } },\n    { output: false, \"name\": \"TipoProgetto\", \"type\": \"nvarchar(255)\", options: { nullable: true } },\n    { output: false, \"name\": \"processato\", \"type\": \"tinyint\", options: { nullable: true } }\n];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":100,"wires":[["ccdf4e4f.5393"]]},{"id":"8d9d212a.cea03","type":"MSSQL-CN","tdsVersion":"7_4","name":"My SQL Server connection","server":"192.168.1.38","port":"1433","encyption":false,"trustServerCertificate":true,"database":"testdb","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

@fabioperencin Did this work for you? Please let me know.

@Steve-Mcl Hi, sorry for the delay. I tried just now with your flow and the new beta version and it's works. Now I had to check with my original flow.
Thanks

bestlong / node-red-contrib-mssql-plus

Error "Incorrect syntax near ')'." in Bulk Insert when Columns are from msg.paramQuery #45