truncate tables with a suffix

While working with D365 F&O I often have to delete test data from several tables with a prefix or suffix. The following SQL script helps a lot to generate the truncate commands for these tables. For example to empty all staging tables from a BYOD.

DECLARE @suffix NVARCHAR(50) = 'staging'; 

SELECT TABLE_SCHEMA, TABLE_NAME, 'truncate table ' + TABLE_NAME as SQLCommand

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE '%' + @suffix;

LCS Shared asset library - different content per geo location

 I just tried to download a software deployablte package from the shared asset library. 

Unfortunately it was not available. That was quite confusing at first. But than I realized that I'm using lcs in geo location Europe lately: https://eu.lcs.dynamics.com/V2/SharedAssetLibrary

The asset library for Europe contains just (67) software deployable packages:

Switching geo location to US solved the problem. There I do see more assets (136). 

Has anyone ever noticed this. I'm using the same tenant.

I tried other geo locations and all do have different amount of software deployable packages.

@Microsoft: Please keep them in sync 

Microsoft Teams 2.0 ;)

Finally Microsoft Teams is supporting multi tenant activity in the windows app. 

What’s New in Microsoft Teams at Enterprise Connect 2023 - Microsoft Community Hub

Download large db backups from LCS asset library

If you have to download a large database backup from LCS asset library this might be a real pain. It is slow and unstable if you just download it with your browser.

 You should always use azcopy: Copy or move data to Azure Storage by using AzCopy v10 | Microsoft Learn

In LCS just click on the "Generate SAS link" this will copy the download link into the clipboard. Open a command prompt and run

azcopy "SASlink" t:\yourLocalFolder

D365 VHD for 10.0.24 is finally available

 For all of you looking for a new D365 VHD Microsoft has just released the VHD with version 10.0.24

Call Dynamics AX2012 AIF Webservice from Postman

Lately I was forced to call a Dynamics AX2012 webservice from Postman. The service is deployed as an HTTP service inbound port in AX on the IIS instance.

First of all I had to authenticate against AX to get around "IIS 8.5 Detailed Error - 401.1 - Unauthorized" - there are some posts in the WWW suggesting to use Fiddler as a supporting tool for the authentication. In my case I was able to use NTLM authentication from postman:

Even though this is still beta it is working quite well for me. 

After that I did some tests with GET and POST. In the header it is important to set

Content-Type = text/xml; charset=UTF-8
SOAPAction = http://tempuri.org/AXService/ProcessWhatever

In the POST scenario I was facing "400 Bad request". This is quite chalanging cause it just means that there is something wrong within the Body. In my case I finally wrote a working XMLwhich looks something like this:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<h:CallContext xmlns:h="http://schemas.microsoft.com/dynamics/2010/01/datacontracts"
               xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<h:Company>ABC</h:Company>
<h:Language i:nil="true"/>
<h:LogonAsUser i:nil="true"/>
<h:MessageId i:nil="true"/>
<h:PartitionKey i:nil="true"/>
<h:PropertyBag i:nil="true" 
                            xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/> </h:CallContext>
</s:Header>
<s:Body>
<ABCServiceRequest xmlns="http://tempuri.org">
<data>123</data>
</ABCServiceRequest >
</s:Body>
</s:Envelope>

The last error I saw was "500 internal server error" - Unexpected character encountered while parsing value. This was due to a wrong value within the body of the XML.

After all that was solved I now can try the AX interface through postman and I'm happy to see "200 Ok" and the response from the service

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <ABCServiceRequest xmlns="http://tempuri.org">
            <response>processed :) </response>
        </ABCServiceRequest >
    </s:Body>
</s:Envelope>

AX2012 Aos start error: Server terminated unexpectedly with 110 exitcode.

Yes AX2012, back to the past :) 

I just tried to start an old AOS with a new empty database. 

First thing that popped up was the error code 110. So what to do? 

Checking the new database I noticed that the two stored procedures CREATESERVERSESSIONS and CREATUSERSESSIONS were missing. 

The solution for this error code was just to create them. 

USE [AX2012DB] 

GO 

 /****** Object: StoredProcedure [dbo].[CREATESERVERSESSIONS] Script Date: 12/24/2031 11:11:11 AM ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

CREATE procedure [dbo].[CREATESERVERSESSIONS] @aosId nvarchar(50), @version int, @instanceName nvarchar(50), @recid bigint, @maxservers int, @status int, @loadbalance int, @workload int, @serverid int OUTPUT as declare @first as nvarchar(50) declare @max_val as int begin select top 1 @first = SERVERID from SYSSERVERSESSIONS WITH (UPDLOCK, READPAST) where STATUS = 0 and AOSID = @aosId and INSTANCE_NAME = @instanceName if (select count(SERVERID) from SYSSERVERSESSIONS where SERVERID IN (@first)) > 0 begin update SYSSERVERSESSIONS set AOSID = @aosId, VERSION = @version, INSTANCE_NAME = @instanceName, LOGINDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), LASTUPDATEDATETIME = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), STATUS = @status, WORKLOAD = @workload, AOSACCOUNT = SUSER_SNAME() where SERVERID IN (@first) and ( ((select count(SERVERID) from SYSSERVERSESSIONS where STATUS = 1 and LOADBALANCE = 0) < @maxservers) OR LOADBALANCE != 0 ) if @@ROWCOUNT = 0 select @serverid = 0 else select @serverid = @first end else begin if (select count(SERVERID) from SYSSERVERSESSIONS WITH (UPDLOCK) where STATUS = 1 and LOADBALANCE = 0) >= @maxservers select @serverid = 0 else begin if (select count(SERVERID) from SYSSERVERSESSIONS) = 0 select @max_val = 1 else select @max_val = max(SERVERID)+1 from SYSSERVERSESSIONS insert into SYSSERVERSESSIONS(SERVERID, AOSID, INSTANCE_NAME, VERSION, LOGINDATETIME, LASTUPDATEDATETIME, STATUS, RECID, LOADBALANCE, WORKLOAD, AOSACCOUNT) values(@max_val, @aosId, @instanceName, @version, dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), dateadd(ms, -datepart(ms,getutcdate()), getutcdate()), @status, @recid, @loadbalance, @workload, SUSER_SNAME()) select @serverid = @max_val end end end 

GO 

CREATE procedure [dbo].[CREATEUSERSESSIONS] @clientType int, @sessionType int, @serverid int, @versionid int, @userid nvarchar(8), @lanExt nvarchar(10), @manExt nvarchar(10), @computerName nvarchar(80), @sid nvarchar(124), @recid bigint, @startId int, @maxusers int, @licenseType int, @masterId int, @maxClientId int, @dataPartition nvarchar(8), @sessionid int OUTPUT, @loginDateTime datetime OUTPUT as declare @return_val as int declare @first as int declare @max_val as int declare @counter as int begin select @sessionid = -1 select @max_val = -1 select @counter = 0 select @loginDateTime = dateadd(ms, -datepart(ms,getutcdate()), getutcdate()) if(not exists(select * from SYSSERVERSESSIONS WITH (NOLOCK) where SERVERID = @serverid AND Status = 1)) begin select @sessionid = -2 return end select @first = min(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK,READPAST) where STATUS = 0 and SESSIONID > @maxClientId and SESSIONID <> @masterId if (select count(*) from SYSCLIENTSESSIONS where SESSIONID IN (@first)) > 0 begin if (@licenseType = 0) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType, CLIENTCOMPUTER = @computerName, DATAPARTITION = @dataPartition where SESSIONID IN (@first) end else if (@licenseType = 1) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType, CLIENTCOMPUTER = @computerName, DATAPARTITION = @dataPartition where SESSIONID IN (@first) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers) end else if (@licenseType = 2) begin update SYSCLIENTSESSIONS set STATUS = 1, VERSION = @versionid, SERVERID = @serverid, USERID = @userid, LOGINDATETIME = @loginDateTime, SID = @sid, USERLANGUAGE = @lanExt, HELPLANGUAGE = @manExt, CLIENTTYPE = @clientType, SESSIONTYPE = @sessionType, CLIENTCOMPUTER = @computerName, DATAPARTITION = @dataPartition where SESSIONID IN (@first) and ( (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) > 0 or (select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) < @maxusers ) end if @@ROWCOUNT = 0 select @sessionid = 0 else select @sessionid = @first end else begin if (@licenseType = 1) begin if (select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers select @sessionid = 0 end else if (@licenseType = 2) begin if ( ((select count(distinct USERID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and ((STATUS = 1) or (STATUS = 2))) >= @maxusers) and ((select count(SESSIONID) from SYSCLIENTSESSIONS where CLIENTTYPE = @clientType and (USERID = @userid) and ((STATUS = 1) or (STATUS = 2))) = 0) ) select @sessionid = 0 end if (@sessionid = -1) or (@licenseType = 0) begin while (@sessionid = -1 and @counter < 5) begin set @counter = @counter + 1 if (select count(SESSIONID) from SYSCLIENTSESSIONS WITH (UPDLOCK) where STATUS = 0 or STATUS = 1 or STATUS = 2 or STATUS = 3) = 0 select @max_val = @startId else select @max_val = max(SESSIONID)+1 from SYSCLIENTSESSIONS WITH (UPDLOCK) if (@max_val > 65535) select @sessionid = -3 else begin insert into SYSCLIENTSESSIONS(SESSIONID, SERVERID, VERSION, LOGINDATETIME, USERID, SID, USERLANGUAGE, HELPLANGUAGE, CLIENTTYPE, SESSIONTYPE, RECID, CLIENTCOMPUTER, STATUS, DATAPARTITION) values(@max_val, @serverid, @versionid, @loginDateTime, @userid, @sid, @lanExt, @manExt, @clientType, @sessionType, @recid, @computerName, 1, @dataPartition) if @@ROWCOUNT = 0 begin select @sessionid = -1 end else select @sessionid = @max_val end end end end end 

GO