The VBScript “DateAdd” allows you to add days to a date. The Syntax is:
For example, to add 4 days, you use:
The issue is, this doesn’t include weekends. However I fell across this solution thanks to Sholsinger:
Function BusinessDayAdd(delta, dt)
dim weeks, days, day
weeks = Fix(delta/5)
days = delta Mod 5
day = DatePart("w",dt)
If (day = 7) And days > -1 Then
If days = 0 Then
days = days - 2
day = day + 2
days = days + 1
day = day - 7
If day = 1 And days < 1 Then
If days = 0 Then
days = days + 2
day = day - 2
days = days - 1
day = day + 6
If day + days > 6 Then days = days + 2
If day + days < 2 Then days = days - 2
BusinessDayAdd = DateAdd("d", (weeks * 7 + days), dt)
An issue I’ve had a few times in the past with regards BPA (formerly “Taskcentre) is the following error message in a specific scenario. The error is caused when a webservice is called multiple times. Usually the first 2 calls are called correctly, however when called a 3rd time, the error message is thrown.
Oddly, when debugging the issue, if you set BPA to use Fiddler to debug the issue, then the error does not happen.
The error message thrown is:
The operation has timed out .
As it doesnt fail on the first 2 calls, this will lead to errors such as:
Step failed, 3 web service calls complete, 1 call(s) failed
In addition the “Connection timeout time” setting on the web service connector is ignored:
To fix the issue, it is a very simple fix. Simply edit the “iwtskrun.exe.config” file, adding the following just before the closing </configuration> tag.
Recently I’ve had an issue with the primary key on a Full Text Index. This SQL query was helpful in highlighting the primary key which was being used:
SELECT tblOrVw.[name] AS TableOrViewName
,tblOrVw.[type_desc] AS TypeDesc
,tblOrVw.[stoplist_id] AS StopListID
,c.name AS FTCatalogName
,cl.name AS ColumnName
,i.name AS UniqueIdxName
FROM sys.fulltext_indexes idxs
INNER JOIN sys.tables tbls ON tbls.[object_id] = idxs.[object_id]
FROM sys.fulltext_indexes idxs
INNER JOIN sys.VIEWS vws ON vws.[object_id] = idxs.[object_id]
INNER JOIN sys.fulltext_indexes fi ON tblOrVw.[object_id] = fi.[object_id]
INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = tblOrVw.[object_id]
INNER JOIN sys.columns cl ON ic.column_id = cl.column_id
AND ic.[object_id] = cl.[object_id]
INNER JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN sys.indexes i ON fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id];
Code initially from here, but has been tweaked slightly.
Received the following error message with a Microsoft Dynamics Nav Client:
The client could not establish a connection to the Microsoft Dynamics NAV Server.
FaultCode = 'Invalid Security'
Reason = 'An error occurred when verifying the security for the message.'
The fix is a simple one. Make sure the date / time is correct both on the client, plus also the server.
Microsoft allow a time difference of up to 5 minutes between client/server. Over 5 minutes is typically not allowed as this can allow “replay attacks” (where credentials and authentication is replayed again at a later date).
A simple explanation of a “replay attack” is available here.
The setting can be changed in Group Policy, but I’d recommend keeping this to the 5 minutes by default.
Within BPA it is possible to add a custom message body. These are not in the documentation, but are very useful. These functions override the standard operation and allow you to modify the document before it is sent by POST.
(In the screenshot below, the word “TEST” appears before the XML generated as part of the input step.)
Operations need to be wrapped within Curly brackets.
A few examples:
The XML created from the input data.
The JSON created from the input data
Call the function “FunctionCall” passing in the XML Input Data.
The parameter “ParameterName”.
Multiple steps can be wrapped together too, for example:
The following handy script will show the size of tables, sorted by size.
SELECT schema_name(tab.schema_id) + '.' + tab.name AS [table]
,cast(sum(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS used_mb
,cast(sum(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS allocated_mb
FROM sys.tables tab
INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id
INNER JOIN sys.partitions part ON ind.object_id = part.object_id
AND ind.index_id = part.index_id
INNER JOIN sys.allocation_units spc ON part.partition_id = spc.container_id
GROUP BY schema_name(tab.schema_id) + '.' + tab.name
ORDER BY sum(spc.used_pages) DESC
Recently I’ve had an issue where some text was presented in a table, and I needed to normalise the text.
Would need to become:
With thanks to Justin Cooney this was possible using the SQL below:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION NormalizeCase (@InputString VARCHAR(8000))
DECLARE @OutputString VARCHAR(500)
DECLARE @Location INT
SET @Location = 1
-- Pre-set to a character string if the input string exists and has a length. otherwise the out string remains a NULL
IF DATALENGTH(@InputString) &amp;amp;amp;gt; 0
SET @OutputString = ''
WHILE @Location &amp;amp;amp;lt;= DATALENGTH(@InputString)
DECLARE @CheckCharacter VARCHAR(1)
DECLARE @PrevCheckCharacter VARCHAR(1)
DECLARE @OutCharacter VARCHAR(1)
-- Set the current character to lower case in case a capitalization condition is not met.
SELECT @CheckCharacter = LOWER(SUBSTRING(@InputString, @Location, 1))
SELECT @PrevCheckCharacter = SUBSTRING(@InputString, @Location - 1, 1)
-- Set the current letter to uppercase if the preceeding letter is a non-letter character
-- and the current character is a letter
IF @PrevCheckCharacter NOT LIKE '[a-z]'
AND @CheckCharacter LIKE '[a-z]'
SELECT @OutCharacter = UPPER(@CheckCharacter)
SELECT @OutCharacter = @CheckCharacter
SET @OutputString = @OutputString + @OutCharacter
SET @Location = @Location + 1
The code can be used as follows:
SELECT dbo.NormalizeCase('THIS IS a reaLLY GooD example')