Microsoft SQL Table Update

Provides an efficient way for executing bulk insert/update on a database without having to hand-write INSERT or UPDATE statements.

This Node may be affected by Windows Regional Settings.

Revision History

1.0.0.0 Initial Release
3.0.0.6 Added support for assigning null values to simple types, minor updates and bugfixes
3.0.0.12 Added ability to insert into a table with identity fields. Use IdentityInsert custom property.
3.0.0.13 Added the ability to update tables and columns that contain spaces.
3.0.0.14 Added support for custom connection string fragments
3.0.0.16 Added support for specifying a schema

Properties

Connection

Type: Connection Input

Server
Type: String

Database
Type: String

UserId
Type: String

Password
Type: Password

UseWinAuth
Type: Boolean

ConnectionStringFragments
Type: String

Provide a list of extra connection string fragments that are not part of the standard connection properties. The fragments must be in the format key=value and different fragments should be listed underneath each other.

TableXml

Type: Xml Input
An XML representation of the data to be updated or inserted (see Remarks).

KeyFields

Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml property. It is not necessary to supply key fields if AllowUpdate is false.

AllowInsert

Type: Boolean Input
Indicates whether inserts are allowed into the target table.

AllowUpdate

Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).

RowsAffected

Type: Int32 Output
Returns the number of rows affected by the insert/update operation.

Remarks

The XML supplied should contain the following paths root\tablename\columnname. The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.

Here's an example of a payload that will add or insert two rows into a table called inventory :

<root>
	<inventory>
		<code>123</code>
		<description>first inventory item</description>
	</inventory>
	<inventory>
		<code>456</code>
		<description>second inventory item</description>
	</inventory>
</root>

When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.

For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.

If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.

When a column is an identity column, and specific values needs to be inserted, a custom field 'IdentityInsert' with value 'true' could be added to the node.

For tables and columns with spaces, the xml tag name will have to be escaped to make it valid xml. A space should be escaped as _0x0020_

A full list of characters can be found here: Unicode UCS-2 Code Chart (columbia.edu)

Troubleshooting

The most common error that occurs is "Failed to convert parameter value from a String to a Double" (or other numeric type). This occurs due to the Regional Settings in place on the machine on which the DropPoint is running. For example, if the decimal separator is set to a comma, only numbers containing a comma decimal separator will be parsed correctly.

To resolve these problems, first determine exactly which field or fields are not being converted correctly by progressively reducing your XML payload until it contains only the problem fields. Next, review the regional settings at the DropPoint.

If it is not possible to change Regional Settings on the machine, the numeric fields need to be formatted in a way that conforms to the Regional Settings.

Examples

See Sample Workflow for an example.