MS SQL

learn how to connect engini to ms sql using engini’s ms sql activities, you can create, get and update records to manage and define databases getting started with ms sql prerequisites a ms sql account add a connection to ms sql in engini the ms sql connector uses basic authentication to authenticate with sql server enter your engini account at https //app engini io https //app engini io navigate to connections page by clicking on the connections on the left sidebar or by clicking here https //app engini io/connections click on the "new integration" option located at the top bar choose ms sql option from the available applications enter the following details in the “new integration” form and press save connection name enter a unique and descriptive name for this connection this name will help you identify and manage the connection in your engini account “ms sql” by default server address specify the address or hostname of your ms sql server this is the location where your database is hosted database name provide the name of the specific database within the ms sql server that you want to connect to username enter the username associated with your ms sql database this username should have the necessary permissions to access and interact with the database password enter the corresponding password for the provided username make sure the password is accurate to establish a secure connection time zone select the time zone of your sql server environment this ensures that all date and time values (timestamps, schedules, logs) are handled correctly for all the options, click here https //help engini ai/timezones in functions communication channel choose the appropriate connection type based on your setup cloud if you are connecting to a database hosted in a cloud environment, select “cloud” opa if your database is on premises and you are using an on premises agent (opa) for the connection, select “opa” in this case, an additional field will appear on prem agent choose the specific on premises agent that you want to use for this connection if you have multiple agents configured save settings after clicking “save”, a window will prompt you to select database objects from available tables/views/stored procedures you can select the databases you want to use by marking the corresponding checkboxes of the tables/view/stored procedures you intend to utilize subsequently, you can perform various activities within your workflow and access the data stored within these selected tables/views/stored procedures actions create record creates a new record in a microsoft sql server table by specifying the target table and providing the field values to insert table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table for the new record you create add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields get records get records from a specified sql table/view table/view click on the empty field and the tooltip will pop up showing all the tables/views you can use all available tables/views will be accessible and you can select the specific tables/views of the records you want to get top n you can set the number of rows you want to retrieve from the table or view this parameter limits the result set to the specified number of records, with the default being 100 if you set top n = 1, a single record will be returned instead of an array if no record is found, the action will fail, allowing you to implement an if condition within the workflow this can be useful for processes where the existence of a record determines the next steps in the workflow offset if you want to skip a certain number of rows before retrieving data, you can set the offset value the default is 0, meaning no rows are skipped add filter click add filter to define conditions that narrow down the data list and return only the relevant items to learn more about using filters, click here https //help engini ai/workflow editor#add filters add sort click add sort to organize the data list by a selected field in ascending or descending order to learn more about sorting data, click here https //help engini ai/workflow editor#add sorts update record updates an existing record in a microsoft sql server table by specifying the target table and providing the field values to update table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table of the record you want to update add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields update records updates multiple records in a microsoft sql server table by specifying the target table, applying filters to identify the records, and providing the field values to update table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table of the records you want to update add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields add filter click add filter to define conditions that narrow down the data list and return only the relevant items to learn more about using filters, click here https //help engini ai/workflow editor#add filters delete records deletes records from a microsoft sql server table by specifying the target table and applying filters to identify the records to remove table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table of the record(s) you want to delete add filter click add filter to define conditions that narrow down the data list and return only the relevant items to learn more about using filters, click here https //help engini ai/workflow editor#add filters create batch of records this activity iterates over a selected data list and for each record in the data list create records for a designated sql table corresponding to the specified data list data list click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose choose a data list to iterate on table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table for the new records you create add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields get records batch this activity iterates over a selected data list and for each record in the data list retrieves records from a designated sql table corresponding to the specified data list data list click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose choose a data list to iterate on table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table of the record(s) you want to get top n you can set the number of rows you want to retrieve from the table or view this parameter limits the result set to the specified number of records, with the default being 100 if you set top n = 1, a single record will be returned instead of an array if no record is found, the action will fail, allowing you to implement an if condition within the workflow this can be useful for processes where the existence of a record determines the next steps in the workflow add filter click add filter to define conditions that narrow down the data list and return only the relevant items to learn more about using filters, click here https //help engini ai/workflow editor#add filters add sort click add sort to organize the data list by a selected field in ascending or descending order to learn more about sorting data, click here https //help engini ai/workflow editor#add sorts update batch of records this activity iterates over a selected data list and for each record in the data list updates records from a designated sql table corresponding to the specified data list data list click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose choose a data list to iterate on table click on the empty field and the tooltip will pop up showing all the tables you can use all available tables will be accessible and you can select the specific table of the record(s) you want to update add filter click add filter to define conditions that narrow down the data list and return only the relevant items to learn more about using filters, click here https //help engini ai/workflow editor#add filters add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields execute customized sql the “execute customized sql” activity is an activity that allows you to interact with a ms sql system by running custom sql queries or commands within your workflow sql in the sql text field, you can write and enter your own sql queries, which are structured commands that define what you want to do with the data in the ms sql system these queries can include operations like data retrieval, modification, or database management execute procedure the “execute procedure” activity is an activity that allows you to interact with a ms sql system by running stored procedures within your workflow procedure name you need to specify the name of a stored procedure that exists within your ms sql database a stored procedure is a pre defined, reusable set of sql commands that are stored in the database and can be executed on demand add field click add field to add and populate additional fields for the selected activity to learn more about managing activity data, click here https //help engini ai/workflow editor#add fields