Executes SQL queries against a Microsoft SQL database and returns results over a series of pages. This Node requires Microsoft SQL Server 2012 and later.
126.96.36.199 Initial Release (preview)
188.8.131.52 Official Release
184.108.40.206 Fixed a bug on XmlQuery
220.127.116.11 Added support for custom connection string fragments
Type: Connection Input
The name or IP address of the server hosting the Microsoft SQL Server instance. Specify a DropPoint if you need to access a SQL instance that is not Internet-exposed.
The name of the database to connect to.
The SQL username to be used for the connection. Not required when UseWinAuth is True.
The SQL password to be used for the connection. Not required when UseWinAuth is True.
When True, indicates that the security context of the caller should be used to authenticate. In Flowgear, UseWinAuth can only be used when the Connection is routed through a DropPoint. In such instances, the security context is the service account used for the DropPoint Windows Service.
Provides an endpoint and credentials for the SQL instance to be queried
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.
Type: Multiline Text Input
The SQL query text. This must be a single select statement.
Type: Boolean Input
When True, indicates that the query provided in the Query property is an XML Query (ie. a query containing the FOR XML statement - refer to http://msdn.microsoft.com/en-us/library/ms178107.aspx for more information on XML queries).
This sets the number of records returned at a time by the node. The Page output will fire each time there are results returned, and the Finished output will fire when there are no more results to return.
Type: Xml Output
The ResultXml Property
This return the number of rows returned by SQL. Note that for Xml Query, this is not accurate because the xml string gets split into rows in the result, which is consolidated in the node.
Use this Connector to execute large SQL queries against a Microsoft SQL Server instance.
This node make use of the OFFSET - FETCH functionality of Microsoft SQL Server 2012 and higher.
For additional information on querying Microsoft SQL databases, refer to Microsoft SQL Query
SQL Parameters via Custom Properties
Flowgear Custom Properties can be added to this Node to make SQL parameters available in the
Query Property. Use of a Custom Property is shown in the linked example workflow (see Examples below).
In XML Query mode, the Connector will automatically build a single XML document from the multiple result-set rows that may be returned.
Other Nodes for SQL databases
If you are updating values in a table based on keys, consider using SQL Table Update instead of crafting update statements.
If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.
See https://flowgear.me/s/a1DUk2P for an example.
See https://flowgear.me/s/r6m3csq for an example on how to implement pagination in versions of Microsoft SQL Server 2012