Content Comparison

...

• Connect to MSSQL
• Disconnect from MSSQL
• Add User Access to Database
• Backup Database
• Delete Row(s) from Table
• Insert Row(s) into Table
• Remove User Access from Database
• Restore Database
• Run Query
• Select Row(s) from Table

Image Added

Connect to MSSQL

This activity connects to a Microsoft SQL Host. The Output of this activity should be used as input for all Connection parameters used for activities placed under Microsoft SQL category.

...

• Authentication (combo box control) (type: string): the authentication method. For SQL Authentication, the Username and Password parameters are mandatory. For Windows authentication they are optional.
• Database (text box editor type) (type: string): the database name to connect to.
• Impersonation Full Username (text box editor type) (type: string): the domain and username required if impersonation is used for the Windows authentication. If empty, default credentials are used. E.g. 'fmrpdomain\demouserusername'
• Impersonation Password (password editor type) (type: string): the username password if impersonation is used for Windows Authentication. If left empty default credentials are used.
• Impersonation Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the sql server. (This parameter is used only when the user wants to send an encripted password from outside the workflow, for example Flowster Portal). In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain empty.
  
• Instance (text box editor type) (type: string): the instance name to connect to. If there is an SQL server with no instance, this field should be empty. Examples:
  
  • localhost\sqlexpress - server with instance
  • localhost - server with no instance
• Password (password editor type) (type: string): the password for the provided username. In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain emptyusername provided in Username parameter for SQL Authentication.
• Password Binding (text box editor type) (type: string):  bind Bind to a variable containing the encrypted password for the sql serverusername provided in Username parameter for SQL Authentication. (This parameter is used only when the user wants to send an encripted encrypted password from outside the workflow, for example Flowster Portal). In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain empty.
• Port (text box editor type) (type: int32): the port number for the SQL Server.
• Server Name (text box editor type) (type: string): the name or IP of the SQL Server to connect to. If it is a local SQL server, localhost is also accepted as a value.
• Username (text box editor type) (type: string): the  the username of the SQL Server Administrator. In case of Windows Authentication, if the user that connects to the database is the logged in one, the field can remain emptySQL username for SQL Authentication. E.g 'demouser' which represents the Login name of type SQL Server authentication.

The Read Only Output variable are the possible output values that the activity will provide:

...

The Backup Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

• Backup Path (text box editor type) (type: string): the full path where the backup file will be created.Media Name 
• Check Folder Exists (text box editor type) (type: stringcombobox): the new media a media name. For better results, use the same name as the database's. As an example, the Restore activity will work properly on a 2014 SQL Server when using the same name as the database's for the media set.select True if the folder where the backup will be created should be checked for existence, otherwise select False. By default the True value will be selected. The False option can be selected for situations where the user doesn't have rights for checking the folder, but it can create the backup.
• Media Name (text box editor type) (type: string):  the name of the backup set. Names can have a maximum of 128 characters. (E.g. 'Full Backup of Database'). In the example the name of the backup is the same the new media a media name. For better results, use the same name as the database's. As an example, the Restore activity will work properly on a 2014 SQL Server when using the same name as the database's for the media set.
• Timeout Name (text box editor type) (type: Int32string): the time in seconds  the name of the backup set. Names can have a maximum of 128 characters. (E.g. 'Full Backup of Database'). In the example the name of the backup is the same as the database's.
• Timeout (text box editor) (type: Int32): the time in seconds to wait for the backup operation to execute. The default value in 30 seconds.

...

The Restore Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

• Backup File Path (text box editor type) (type: string): the path of the backup file or select it with the File Browser. The .bak file path can be manually written or selected by clicking the  button. In this example the backup file has the same name as the database's.Timeout
• Check File Exists (text box editor type) (type: Int32combobox): the time in seconds to wait for the backup operation to execute. The default value in 30 seconds.
  

Run query

This activity runs a query on a SQL Server database.

Activity Parameters

...

• select True if the backup file should be checked for existence, otherwise select False. By default the True value will be selected. The False option can be selected for situations where the user doesn't have rights for checking the folder, but it can create the restore.
• Timeout (text box editor) (type: Int32): the time in seconds to wait for the backup operation to execute. The default value in 30 seconds.
  

Run query

This activity runs a query on a SQL Server database.

Activity Parameters

The Run Query activity parameters can be provided by manual inserted values or via IN arguments or variables:

...

• Query (text box editor type) (type: string): the SQL query string. For example: "SELECT Name, Company FROM [FlowsterMSSQLActivities].[dbo].[Names] where ID>1 and ID<4 order by name ASC"
  Image RemovedScript (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:
  Image Removed

The Read Only Output variable are the possible output values that the activity will provide:

• Output (type: collection<collection<string>>): outputs a collection of returned rows.To use the elements of the collection, use ForEachFactory activity.
• Output Columns (type: int32): outputs the number of columns returned.
• Output Rows (type: int32): outputs the number of rows returned.

...

• ASC"
  
  Image Added

NOTE: if query returns a column of Date type, the format of the date returned as a string will be taken from the execution server.

• Script (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:
  
  Image Added

The Read Only Output variable are the possible output values that the activity will provide:

• Output (type: collection<collection<string>>): outputs a collection of returned rows.To use the elements of the collection, use ForEachFactory activity.
• Output Columns (type: int32): outputs the number of columns returned.
• Output Rows (type: int32): outputs the number of rows returned.

NOTE: if user-defined messages are desired to be returned and displayed by the Run Query activity, the command(s) can be written by following the example below:

DECLARE @firstMessage char(20);
DECLARE @secondMessage char(20);
SET @firstMessage='The selected category exists in the database';
SET @secondStatus = 'The selected category does not exist in the database';

IF (EXISTS (SELECT name FROM Category where name='Approval Tasks'))
SELECT @firstMessage;
ELSE
SELECT @secondMessage;

...