Inserting new records in SyteLine is not just limited to the Mongoose tool-set or SQL layer we can also use form script to add new records via the IDO layer.

To do this we need to define two variables the first of which is an update collection variable which will hold the records we want to insert whether this be one or many records with this variable being of type UpdateCollectionRequestData, and on initilisation of the new object variable we are also able to specify what IDO collection we want by passing in the IDO name as an argument.

In the below example we are using form script to insert a new family code and in our implementation we have called the new collection variable oUpdateCollection and have told it we want to update the family codes IDO which is called SLFamCodes.

Dim oUpdateCollection As New UpdateCollectionRequestData("SLFamCodes")

Secondly we also need to create a variable of type IDOUpdateItem which will represent a single record in our collection to which we are able to define the properties and values we want to insert and when initialising the object we are able to specify what action type we want the update item variable to be, in our example as we are inserting new records so we have set this to be UpdateAction.Insert.

Dim oUpdateItem As New IDOUpdateItem(UpdateAction.Insert)

Both the UpdateCollectionRequestData and IDOUpdateItem types are part of the Mongoose.IDO.Protocol1 namespace.

Next we can add the properties to the oUpdateItem variable that we want to include in our insert. We do this by calling the add function and passing in the property name, property value and lastly whether this property should be marked as modified which we will set to true as it will be a new record.

For our family code example we want to insert the new family code along with the family code description which have the property names FamilyCode and Description respectively.

Once we have added all the properties we need to the oUpdateItem variable we then need to add this object to the collection update variable oUpdateCollection.

oUpdateCollection.Items.Add(oUpdateItem)

If we needed to add more than one new record then we could repeat the oUpdateItem process by instantiating a new object of type IDOUpdateItem adding the required properties and property values and then lastly we would need to add the new update item to the update collection variable so that all the records could be processed and sent to the database in a batch rather than individually.

Now that we have the new family code records in our oUpdateCollection variable we can update the database, we do this by passing our collection to a shared update collection2 method which uses the current session context to make the IDO and then database call.

Me.IDOClient.UpdateCollection(oUpdateCollection)

If we have defined our IDO name, property names and values correctly the records will now be in the database, if any errors were encountered during the insert then the method call will return an error along with an error message, like you would get when doing an insert direct in SQL.

The very last thing we need to do is to clean up the variables we used by setting their references to nothing.

oUpdateItem = Nothing
oUpdateCollection = Nothing

Full code snippet to insert a new record into a SyteLine table via the IDO layer and form script.

The Mongoose.IDO.Protocol namespace is imported for you when using a SyteLine form as part of the implementation so does not need to be explicitly referenced.↩

Me.IDOClient.UpdateCollection is a shared method that is built into the Mongoose tool-set, all we need do is pass our collection variables into it and there is no instantiation required of the method itself.↩

In an earlier post we looked at using TSQL windowing functions to allow us to have a row number returned after execution of a select statement by using a windowing function in TSQL, that post can be found here if you haven’t yet seen it. We are also able to use a windowing function with the COUNT function within a select statement so that we can use count in SQL without needing a group by clause.

Take the below select statement as an example, we just want to select the product number and product name but we also want to return the total record count as an output column in the select, to do this we could use a GROUP BY and then turn the select statement into an aggregate query which gets a little messy the more columns you need in the select, or we could make a separate call after the query has executed to return the row count.

SELECT
ProductNumber
, Name
, COUNT(*) AS Count
FROM Production.Product

The above select statement will of course fail as we have not included a group by and the COUNT function is an aggregate function. But we can use an OVER clause to specify that the count function is a windowing rather than an aggregate function, to do this we change our query to look like the example below.

The OVER clause lets us execute the COUNT function without the need for a group by and in the above example it will return the count of all records returned in the query.

As its a windowing function though we are able to do some clever things like for example say we wanted to count the number of records that had the same first two characters we could add a PARTITION clause like we did in the row number example so that the record count gets reset each time the first two characters change, to do this we can alter our query as per below/

SELECT
ProductNumber
, Name
, COUNT(*) OVER(PARTITION BY LEFT(ProductNumber,2) ORDER BY ProductNumber) AS Count
FROM Production.Product

Sample output

ProductNumber

Name

Count

AR-5381

Adjustable Race

1

BA-8327

Bearing Ball

1

BE-2349

BB Ball Bearing

1

BE-2908

Headset Ball Bearings

2

As you can see from the above output the count function is acting the same as the row number function did in the previous post and it is more like a running count sub total. If we wanted to just return the total count then we can tell the partition clause the range of rows to take into consideration in order to generate the count, and at present in the above example this range is implied to be from the first record in our results to the current row which is why we see the output that we do.

To tell SQL to generate and return the count for all records in our results we can add a range clause such as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING which essentially means run the count function based on the partition over all records in the results. UNBOUNDED PRECEDING meaning the start and UNBOUNDED FOLLOWING meaning the last.

SELECT
ProductNumber
, Name
, COUNT(*) OVER(PARTITION BY LEFT(ProductNumber,2) ORDER BY ProductNumber
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Count
FROM Production.Product

Sample output

ProductNumber

Name

Count

AR-5381

Adjustable Race

1

BA-8327

Bearing Ball

1

BE-2349

BB Ball Bearing

2

BE-2908

Headset Ball Bearings

2

Now we have a true count so our first two records in the output have counts of 1 as they are the only records beginning with AR and BA but the last two records both have counts of 2 indicating that there are two records that beginning with the letters BE.

Occasionally its useful to return row numbers when executing a select statement in SQL so that each row of the returned result set has a unique id similar to how an identity column would work on a table and we are able to achieve this by using the ROW_NUMBER windowing function.

As an example below we have a simple select statement that returns product number and product name from the adventure works product table.

SELECT
ProductNumber
, Name
FROM production.product

Example output

ProductNumber

Name

AR-5381

Adjustable Race

BA-8327

Bearing Ball

BE-2349

BB Ball Bearing

BE-2908

Headset Ball Bearings

Now by adding the ROW_NUMBER windowing function into our select statement we are able to include a sequential numbered identifier in the result set.

SELECT
ProductNumber
, Name
, ROW_NUMBER() OVER(ORDER BY ProductNumber) AS RowNumber
FROM production.product

Example output

ProductNumber

Name

RowNumber

AR-5381

Adjustable Race

1

BA-8327

Bearing Ball

2

BE-2349

BB Ball Bearing

3

BE-2908

Headset Ball Bearings

4

In the above example we added the windowing function ROW_NUMBER() OVER(ORDER BY ProductNumber) to our select statement, the first part ROW_NUMBER() is the tsql function that is going to return the sequential number, the next part contains the OVER clause and this is windowing function part and without it the ROW_NUMBER function would not work for us within the select statement. The windowing part of the statement OVER(ORDER BY ProductNumber) in very simple temrs tells SQL server what records in the result set we want to apply the function to and in what order the function should be applied.

In the above example we are returning the result set and applying the ROW_NUMBER function to all the records returned so that the first record starts at number one and the next is n+1 etc… which for returning the row number is normally what you would want to do. But we are also able to use the PARTITION part of the over clause to tell the function to restart the number sequence based on certain criteria.

So for example if we wanted to restart the number sequence each time the first two characters of the product number changed we could alter the above select statement and add in a partition clause into the over section of our statement to do just that.

SELECT
ProductNumber
, Name
, ROW_NUMBER() OVER(PARTITION BY LEFT(ProductNumber,2) ORDER BY ProductNumber) AS RowNumber
FROM production.product

Now that we have added in a PARTITION clause and the LEFT() function the outcome is that the row number will be reset back to one each time the first two characters change and our result set would now look like the below.

ProductNumber

Name

RowNumber

AR-5381

Adjustable Race

1

BA-8327

Bearing Ball

1

BE-2349

BB Ball Bearing

1

BE-2908

Headset Ball Bearings

2

As you can see the first three records all have the row number 1 as they all have different characters at the start where as the fourth record has a row number of 2 as it is the second record in the ‘BE’ sequence.

We can move tempDB to a new location by running TSQL commands against the database to specify the new location and then after a restart of the SQL services the physical data files will be recreated at this new location.

As tempDB is created when SQL server starts up we next need to give the MSSQL service a restart so that tempDB is moved to our new location. To restart the service you will need to go to “SQL Server Configuration Manager” which is found in “All Programs\Microsoft SQL Server {version}\Configuration Tools\” as per the below screenshot.

Then within the SQL Server Configuration Manager right click the SQL Server service and then select restart.

The tempDB files will now be moved to the new location and you can check this by running a query against sys.master_files system function or by checking the new file location ofcourse.

Installing RabbitMQ Server on Windows is straightforward and should only take a few miniutes to complete the install and configure. In the below setup we will install RabbitMQ on a Windows 7 client that we will then further use for some example Visual Basic.net.

Firstly RabbitMQ has a dependency on Erlang so we need to download either the 32 or 64bit version from www.erlang.org/download first.

Once downloaded run through the Erlang setup and accept all the defaults. Next we need to download and install RabbitMQ for Windows from www.rabbitmq.com/download

When running the RabbitMQ setup you will get prompted to allow two firewall exceptions, select the network profile you want to allow the exception for and then click on allow.

If everything has gone successfully you should now see a RabbitMQ Server all programs menu option and also a RabbitMQ windows service.

Now that RabbitMQ is installed we can install the managment plugin which is done via the RabbitMQ command line. Navigate to all programs and then run the “RabbitMQ Command Prompt” as administrator, one the command prompt opens navigate to the sbin directory by running the following CD %PROGRAMFILES%\RabbitMQ Server\rabbitmq_server_2.7.1\sbin\

Next we need to install the management plugin by running the following command rabbitmq-plugins.bat enable rabbitmq_management

Then we need to uninstall and reinstall the RabbitMQ service for the plugin to become active, we do this by running the following three commands:

By far the simplest method to disable all table constraints on a SQL Server table is to use the ALTER TABLE method, for example if we had a table called MyOrderLines and we wished to disable all the constraints for the table we could run the below command against the table in TSQL.

ALTERTABLE MyOrderLines NOCHECKCONSTRAINTALL

The above line will disable all constraints and then to re-enable the constraints you can run the below command again via TSQL.

ALTERTABLE MyOrderLines CHECKCONSTRAINTALL

This will then enable the constrains on the table again so all new record changes will be checked against the constraint or constraints. However when you re-enable a constraint it won’t automatically check the integrity of the existing data in the table, so one option we have available to us is to tell SQL Server when it re-enables the constraint to go and validate the integrity of the existing data against the constraint rules, we can do this by using the below command which tells SQL to check the check constraint.

ALTERTABLE MyOrderLines CHECKCHECKCONSTRAINTALL

If you only wanted to enable or disable a single or a named constraint then you can change the ALL to be the constraint name.