Sunday, June 24, 2012

Changing variables during runtime (BizTalk 2010)

For one of our customers I had to come up with a solution for the changing variables and different environment parameters.
For the changing send/receive location we use Deployment Framework for BizTalk, but there are some parameters that cannot be changed during runtime.

One of the proposals was too place the environment variables in the different config-files used by BizTalk. Yes BizTalk uses different config-files, we all know the config-files for 32-bit and 64-bit host instances. But not everyone knows that de isolated hosts and WCF use different ones.
This means that you have to maintain at least four files per server and we all know how quickly people forget things. This and the fact that altering config-files isn’t a best practice, we opted for the use of a database. The data can be changed at runtime and we can make an identical database with different data per environment that all the servers for that environment can access.

The following screenshot describes the table I’m using for this explanation. 

 

By including the application name as a column, we can reuse the same table, stored procedures and the generated schemas by the WCF-SQL adapter. This also eliminates the possibility of identical parameter names by different applications.  Depending on the expected records, you can index the table. We put an index on the ApplicationName together with ParameterName.
For retrieving the data, we're using the WCF-SQL adapter that calls one of two stored procedures. There are 2 stored procedures:
1. usp_GetParameterValueByName: it takes the application and parameter name as an input value and returns the value of that parameter

USE [EbtsServices]
GO
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParameterValueByName]
      @applicationName nvarchar(10),
      @parameterName nvarchar(10)
AS
BEGIN     
      SET NOCOUNT ON;
      SELECT ParameterValue
      FROM ApplicationParameters
      WHERE ApplicationName = @applicationName
      AND ParameterName = @parameterName;
END

     2. usp_GetParametersByApplicationName: it takes the application name as an input value and returns all the parameters associated with the application

USE [EbtsServices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetParametersByApplicationName]
      @applicationName nvarchar(10)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ParameterName , ParameterValue
      FROM ApplicationParameters
      WHERE ApplicationName = @applicationName;
END

Don’t forget to grant the correct BizTalk groups execute (with grant) permissions on the stored procedures.  Using WCF-SQL to generate the schemas results in the following three schemas: 
- dbo.xsd  
The first thing to do is to promote the ApplicationName en ParameterName element in the dbo.xsd so that we can alter them inside an orchestration.
This is a response message when using usp_GetParametersByApplication : 
 
 
As you can see, the response consists out of - depending on the number of parameters in the table - multiple StoredProcedureResultSet0 records, which have a ParameterName and ParameterValue element.  
Because BizTalk doesn’t let you promote elements that can occur multiple times, we had to use XPath expression to extract the value.

On the internet there are a lot of blogs that suggest that you count the number of repeated records and then loop x time to extract the parameter value by using the index in the XPath.  This approach has some disadvantages:
               -  you need to add three extra shapes to you orchestration (one expression to determine the count, one loop and one expression insight the loop the extract the values)
               -  It has an impact on performance because it needs to loop over all the StoredProcedureResultSet0, check if the parameter name is the one you expect and then extract the value, even if you don’t use them all.

So I searched for another solution. Just like with an collection it is possible to replace the index by a key value, in this case the parameter name. First I tried to figure out the correct xpath by trail and error. Eventually I went looking for an interesting tool. The lucky winner is Altova’s xml spy. 
I opened the response message and started to experiment until I found the correct xpath expression:
string(/*[local-name()='usp_GetAllParametersByApplicationNameResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'] /*[local-name()='ParameterName' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName' and .=’{ParameterName}’] /../*[local-name()='ParameterValue' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'])"))

Now I just replace {ParameterName} with the parameter name (as written in the database) where I want to get the corresponding parameter value form. And then I had to place the expression inside string(), to get the value as a System.String object.
Still this wasn’t good enough for BizTalk, I got an object of type Microsoft.XLANGs.Core.Part+ArrayBasedXmlNodeList, not exactly what I needed. The solution is to use System.Convert.ToString.
The full command to retrieve the parameter value in an orchestration,  parameterValue is of type System.String. 
parameterValue = System.Convert.ToString(xpath(parametersResponse.Body , "string(/*[local-name()='usp_GetAllParametersByApplicationNameResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'] /*[local-name()='ParameterName' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName' and .= ’{ParameterName}’] /../*[local-name()='ParameterValue' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetAllParametersByApplicationName'])"));

When using usp_GetParameterValueByName the xpath expression is slightly different:
 /*[local-name()='usp_GetParameterValueByNameResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/sp_GetParameterValueByName']/*[local-name()='ParameterValue'])"))

It is still necessary to put the expression inside of string() and do a conversion to System.String.



author: Martijn

Tuesday, June 19, 2012

Common error when connecting to IBM Websphere MQ from ASP.NET


Problem

When connecting to an IBM WebSphere MQ queue using the WebSPhere MQ classes for .NET from within an ASP.NET application, you might run into the following error:

General Exception: The type initializer for 'IBM.WMQ.MQQueueManager' threw an exception.


When investigating the error a bit further we find the following error in the Windows EventViewer:

An unhandled exception occurred and the process was terminated.
Application ID: /LM/W3SVC/1/ROOT/HealthCheckMQSeriesService
Process ID: 4972
Exception: System.Reflection.TargetInvocationException
Message: Exception has been thrown by the target of an invocation.
StackTrace:    at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at IBM.WMQ.CommonServices.CreateCommonServices()
   at IBM.WMQ.CommonServices.TraceEntry(String objectId, UInt32 component, UInt32 method, Object[] parameters)
   at IBM.WMQ.MQQueueManager.Dispose(Boolean disposing)
   at IBM.WMQ.MQQueueManager.Finalize()
InnerException: System.DllNotFoundException
Message: Unable to load DLL 'amqxcs2.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
StackTrace:    at IBM.WMQ.MQCommonServices.xcsInitialize(UInt16 scope, UInt16 attributes, Byte[] charName, Byte[] charPrefix, xcsHPOOL& hPool)
   at IBM.WMQ.MQCommonServices.Initialize()




Solution

The solution is very simple.
In IIS go to the advanced settings of  application pool your application is running under.
Be sure that the “Load User Profile” property is set to true.


Run your application again and you should notice that the error disappeared.

Author: Christophe Verschuere