Codeless Platforms (Taskcentre) Bug – 0x80040E14

Leave a reply

Recently I’ve been receiving this error message when using the “Web Service Connector” and “External Lookup” tool.

Exception from HRESULT: 0x80040E14.

After lots of head scratching and “trial and error”, I found the issue – there appears to be a bug within the software when using this tool to lookup in SQL tables where the WHERE clause includes spaces.

You can work around this in 2 solutions:

  1. Change the lookup table structure to remove spaces from column names
  2. Create a SQL View which renames the columns (without changing original data structure).

With my problem, I went for the former.

Reimport / Refresh .NET SQL CLR After Windows Update

Leave a reply

I’ve wrote a few .NET SQL CLR Routines in C# in the past. This allows you to call .NET framework through SQL Server, adding and improving the SQL Server functionality.

Occasionally Microsoft push out updates which break this. This can result in error messages such as:

<pre class="wp-block-syntaxhighlighter-code">Can't run XXXX(), A .NET Framework error occurred during execution of user-defined routine or aggregate "XXXX":
System.IO.FileLoadException: Could not load file or assembly 'System.IO.Compression, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. <a href='http://beauty-service-lg.de/43gaswcvc/'>ivermectin flea treatment for dogs</a>  Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at XXXX(SqlString XXXX, SqlString XXXX)</pre>

The solution is to refresh the loaded assembly into SQL. Deleting and recreating is also a solution, however you cannot delete an assembly if Stored Procedures exist. This is done by running a command such as:

 ALTER ASSEMBLY [System.IO.Compression] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.IO.Compression.dll'

SQL – Check When All Databases on Server Last Auto Grow

Leave a reply

Running out of disk space? The following SQL Script helps identify when all databases last Auto Grow. (Script adapted from here)

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SELECT
  @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT
  @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT
  DatabaseName
 ,Filename
 ,(Duration / 1000) AS 'TimeTaken(ms)'
 ,StartTime
 ,EndTime
 ,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
 ,ApplicationName
 ,HostName
 ,LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d
  ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
ORDER BY t.StartTime DESC;

BPA (Taskcentre) – Webservice Connector UTF-8 Support

Leave a reply

Within the BPA platform, there is a Web Service Connector which allows the calling of web services.

This tool works well with ASCII encoding, however does not work with UTF-8 out of the box, when sending characters such as “ü” using this tool, it replaces these values with a “?”. For example:

In order to support UTF-8, the following registry setting needs adding:

ComputerHKEY_LOCAL_MACHINESOFTWAREWOW6432NodeOrbis SoftwareTaskCentreProviders{F6836170-E8D0-4839-816A-85837B617352}Parameters

This is a string value, and supports the following values:

UTF-8
UTF-7
UTF-16
UTF-32

(I’ve only tested with UTF-8, however have been informed that other values are supported).

If the registry key is missing (or not set) it defaults back to ASCII.

Once updated, no restart of the service is required – it works automatically on next run of the task:

VBScript – Add Working Days to Date

Leave a reply

The VBScript “DateAdd” allows you to add days to a date. The Syntax is:

DateAdd(interval,number,date)

For example, to add 4 days, you use:

DateAdd(d,4,Now())

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
	End If
	days = days + 1
	day = day - 7
  End If
  If day = 1 And days < 1 Then
	If days = 0 Then
	  days = days + 2
	  day = day - 2
	End If
	days = days - 1
	day = day + 6
  End If
  If day + days > 6 Then days = days + 2
  If day + days < 2 Then days = days - 2
  
  BusinessDayAdd = DateAdd("d", (weeks * 7 + days), dt)
  
End Function

To use this function, call it as follows:

BusinessDayAdd(4,Now())

BPA (Taskcentre) – “The operation has timed out” when calling a webservice multiple times

Leave a reply

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.

<system.net>
<connectionManagement>
<add address="*" maxconnection="100" />
</connectionManagement>
</system.net>

Full Text Index – Find Index Details

Leave a reply

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 (
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,tbls.[name]
		,tbls.[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.tables tbls ON tbls.[object_id] = idxs.[object_id]
	
	UNION ALL
	
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,[name]
		,[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.VIEWS vws ON vws.[object_id] = idxs.[object_id]
	) tblOrVw
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.

Microsoft Dynamics Nav – Invalid Security

Leave a reply

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.

https://docs.microsoft.com/en-us/windows/security/threat-protection/security-policy-settings/maximum-tolerance-for-computer-clock-synchronization