Thursday, March 29, 2018
Dynamics AX upgrades can be painful, time-consuming and resource draining. Add an SQL version upgrade on top and that is when things can go bananas in a blink of an eye.
As a Dynamics AX Upgrade Factory and ISV Development Center, we find rare and unusual issues when working on the numerous upgrade and development projects we undertake each year. In this series, Dynamics AX Obscurities, we share our experiences, insights and workarounds for these unknowns in hopes to save you time, headaches and costs in your future Dynamics AX upgrade and development projects.
Obscure SQL Server Integration Issue:
After moving to a newer SQL Server version, your Microsoft Dynamics AX integration related to SSIS packages fails to execute the SQL stored procedure from the AX code with the error:
[Microsoft][SQL Server Native Client 10.0][SQL Server]The path for 'ISServerExec.exe' cannot be found. The operation will now exit.
One might think the problem lies within Dynamics AX or the SQL Server setup – however, don’t fall prey to this misleading prompt. The version number shown in the error message (10.0) points to the version of SQL client, which is not related to the issue we are looking at.
To resolve this you must look into the registry key – is it correct? If the error persists and you don’t have multiple versions of SSIS installed in the machine, what do you do?
Obscure SQL Server Integration Solution:
Assuming you have gone through SSIS upgrade wizard and you are still wielding this error (even when executing SSIS related stored procedures directly from SQL), there is one teeny step left to finalize the procedure – updating SSISDB assembly. You have moved your SSISDB to the new server and upgraded SSISDB, however, an SSIS Server assembly references an old .dll file.
Updating path to the .dll file isn’t as straightforward as one might assume – you cannot modify existing assembly from the UI. You cannot create a new assembly pointing to the correct file as well. But worry not – we’ve got you covered!
Open your SQL Management Studio and navigate to SSISDB. Expand the Programmability node, expand the Assemblies node and right-click an assembly you need to update (ISSERVER in this example).
Figure 1: SQL Management Studio – Assemblies
Click New Assembly. Now you want to change Permission set and set the Path to assembly.
Figure 2: SQL Management Studio – Permission Set – Unrestricted
Microsoft.SqlServer.IntegrationServices.Server.dll file is an assembly you are looking for. To find it, go to your SQL installation directory and navigate to Program Files (x86)\Microsoft SQL Server\130\DTS\Binn, locate the forementioned .dll file and click Open.
Figure 3: SQL Management Studio – Path to Assembly
Note: Directory of Program Files (x86)\Microsoft SQL Server\130\DTS\Binn file will differ for different SQL versions – make sure you navigate to correct folder, depending on the target version of your Dynamics AX related SQL installation.
If correctly set, a new assembly will be ready to be created. However, if an assembly reference to ISServer dll already exists – you cannot create a duplicate.
Figure 4: SQL Management Studio – New Assembly
Instead of clicking OK, click the Script button on the top of New Assembly window. This will create an SQL statement for you – the same statement that would be executed if you clicked that tempting OK button.
After you have clicked the Script button, the SQL database engine query editor window will open. Now you can close the New Assembly window – you will not need it any further.
Figure 5: SQL Database Engine Query Editor
Creating a new assembly from code won’t work as well because of the aforementioned duplicate issue, therefore we need to change the statement to modify the existing assembly record.
There are three things we need to change in the statement:
- Change the SQL statement so it modifies an existing assembly instead of creating a new one:
- Replace the CREATE keyword with the ALTER
- Replace assembly name to resemble the assembly we need to modify:
- [ISSERVER] in this example.
- Remove an extra line:
- Remove AUTHORIZATION [dbo]
This is how the final SQL statement should look:
Figure 6: SQL Database Engine Query Editor – Modified Assembly Record
Note: It is likely that your assembly name and PERMISSION_SET parameters might be different. Make sure you change them in accordance with original assembly parameters. See your assembly’s Properties window to ensure all the parameters entered in SQL statement are correct.
Now that the statement is created, execute it. It will take a couple of seconds with no additional actions to follow afterwards.
Before diving into testing a freshly applied solution, be sure to restart your Dynamics AX client. Now the error should be resolved.
Stay tuned for more tips!
If you run into a Dynamics AX Obscurity you’d like us to investigate, email our team at: services@1ClickFactory.com.