Bulk Insert fails on table with primary key

[harmonic7]

When bulk inserting into a predefined table with a primary key, the insert fails and the following error is returned:

Invalid column type from bcp client for colid 1.

Column types used were NVARCHAR(255)

The whole thing worked fine if I didn't have a pre-existing table, or the pre-existing table didn't have a primary key.

[Steve-Mcl]

Did you include the primary key as a column in the list (should not be in the list if identity is specified)

Are you certain the values being bulk inserted are not in the table?

If it is not either of those, would you be kind enough to provide a sample table create statement and a demo flow & I will look into it?

Thanks for testing.

[harmonic7]

No problem,

The primary key is not an identity column, and the table is empty.

The table create statement is:

CREATE TABLE dbo.Temp (
    myKey NVARCHAR(255) PRIMARY KEY,
    name NVARCHAR(255)
)

The flow is:

[{"id":"f6f2187d.f17ca8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"abf66d8a.48c46","type":"MSSQL","z":"f6f2187d.f17ca8","mssqlCN":"425f76c7.13a328","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"dbo.Temp","modeOpt":"","modeOptType":"bulk","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"myKey","type":"NVarChar(255)","valueType":"num","value":"0"},{"output":false,"name":"name","type":"NVarChar(255)","valueType":"num","value":"0"}],"x":320,"y":60,"wires":[["6163cb34.235974"]]},{"id":"c0f9e7f3.6e46b","type":"inject","z":"f6f2187d.f17ca8","name":"","props":[{"p":"payload"},{"p":"rows","v":"[{\"myKey\":\"AAA_Test1\",\"name\":\"AAA_Test1\"},{\"myKey\":\"AAA_Test2\",\"name\":\"AAA_Test2\"}]","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":60,"wires":[["abf66d8a.48c46"]]},{"id":"6163cb34.235974","type":"debug","z":"f6f2187d.f17ca8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":60,"wires":[]},{"id":"425f76c7.13a328","type":"MSSQL-CN","tdsVersion":"7_4","name":"sqldata sdb","server":"sqldata","port":"1433","encyption":true,"trustServerCertificate":true,"database":"sdb","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

I actually tried it earlier with much more data to insert (10,000 records) - and aside from the primary key issue, things inserted well.

[Steve-Mcl]

Hi @harmonic7 I have now published a beta.3

https://www.npmjs.com/package/node-red-contrib-mssql-plus/v/0.7.0-beta.3

The error you were seeing was because the tedious driver couldnt infer from the parameter that the field was NOT nullable. Even though the bulk data you were sending did not contain nulls the table column MUST be set as not nullable. Who knew!

I have had to re-jig some code and add even more UI options (bah humbug)

anyhow, I would really appreciate you testing out beta.3?

Additional point, as you will remember, this beta version also has the v7 MSSQL (which updates tedious from v6.x --> v9.x) are you also seeing the improved speed? (reference issue #35 )

[Steve-Mcl]

I assume this issue is resolved in beta3?

Closing issue for now.

[harmonic7]

Hi @Steve-Mcl , sorry for not getting back to you sooner on this one.

I've checked now and can confirm that I no-longer receive the error relating to the primary key insert. I've also retested the scenario from #35 and the speed doesn't seem to be an issue - I successfully inserted 250,000 rows in about seven seconds using the stored proc with the TVP.