Tuesday, June 30, 2009

How to check xml parameter’s node value is null or empty in MS Sql

Suppose you are receving XML datatype of message in your stored procedure and you need to extract value from
that xml parameter. Before extracting data you need to check the node is exist or not. Means is that node is empty or not.
You can do it by using the .exist() in MS Sql.

Let me explain using an example.

Suppose we have a @XmlDoc and it contains the ‘’ data.
Here in example we are checking is there any Student contains Maths subject. As you can see in our Xml, it is there so it will return 1. Like the
same way we can check the particular node is exist in xml or not. So to check the value, we can use this way.

DECLARE @XmlDoc XML

SET @XmlDoc = ‘
DECLARE @docHandle int

IF (@XmlDoc.exist(‘/Student/Subject/Maths’)=1)
PRINT ‘Maths Exist’
ELSE
PRINT ‘Maths Not Exist’

IF (@XmlDoc.exist(‘/Student/Subject/Science’)=1)
PRINT ‘Scienct Exist’
ELSE
PRINT ‘Science Not Exist’

Saturday, June 27, 2009

XML parsing: line 1, character 38, unable to switch the encoding

When you will create an Xml file with the Encoding type UTF-8, they it will give you this error:
“XML parsing: line 1, character 38, unable to switch the encoding”

To resolve this error you need to set the Encoding type to UNICODE i.e. UTF-16. Test it using this encoding type UNICODE / UTF-16, it will resolve your error.

Regards,

Vijay Modi

Monday, June 22, 2009

Biztalk : Illegal attempt to update the value of part ‘part’ in XLANG/s message ‘testMsg’ after the message construction was complete.

When you are receiving a message and will go to update it in the expression shape, you will receive this type of error. To resolve this you need to construct the new message of the same type and needs to update the value of it in the construct shape using Message Assignment shape.

Regards,
Vijay Modi

Thursday, June 18, 2009

MS SQl + XML parsing error: Reference to undeclared namespace prefix

When a namespace is declared in your xml document / String and you will use the OpenXml in your stored Procedure / Sql Query, you will get this type of error. To remove this error you need to define the namespace while you write the EXECUTE statement in you query/stored procedure.

Suppose you have written the following statement:

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDoc

So you need to change it with the following one:

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDoc, ‘

Where ns0 is the namespace of your xmlDocument, which is defined in the first node of your xml document. Suppose my xml document is as follows:


ID_0
Name_0

Address_0

City_0

Let me know if you have any query:)

Regards,

Vijay Modi

Sunday, June 14, 2009

"Service Unavailable" error message

When you are going to browse your web service or WCF Service, you can get this error “Serviec Unavailable”.

This error may occur if the password of Application pool assigned to Web Service or WCF Service is not correct. So to resolve this one you need to change it. You can change it using the IIS Services.

To change the password of application pool, see the properties of the application pool using IIS Services. and change the password. Restart the IIS using IISReset in Start=>Run.

Test your Web Service or WCF Service, it will work.

Regards,
Vijay Modi

Wednesday, June 10, 2009

BizTalk: Dynamically create schema nodes using XSLT

First let us discussed about the problem:I have a source schema which is in the following format:Now I want my destination schema in the following format:Note: Check I want the Ord1 and OrdName1 in OrderInfo as well as Ord2 and OrdName2 in OrderInfo too. So I need the value of above in one record (node) of Destination schema. So for to do the above functionality I have used the BizTalk map. What I have done is I have created a BizTalk map and assign Source.xsd in SourceSchema and Destination.xsd in Destination schema of this map.You can see the snap of my Mapping as follows:Here you can find that I have added just one script Functoids in the mapping of source and destination schema. I have written the following code (XSLT Code) to map both schemas.==========================================================================
<xsl:for-each select="//Order">

<xsl:element name="OrderInfo">

<xsl:element name="OrderID"><xsl:value-of select="Ord1" /></xsl:element>

<xsl:element name="OrderName"><xsl:value-of select="OrdName1" /></xsl:element>

</xsl:element>

</xsl:for-each>


<xsl:for-each select="//Order">

<xsl:element name="OrderInfo">

<xsl:element name="OrderID"><xsl:value-of select="Ord2" /></xsl:element>

<xsl:element name="OrderName"><xsl:value-of select="OrdName2" /></xsl:element>

</xsl:element>

</xsl:for-each>

==========================================================================So Test the map and check the generated destination file.Enjoy BizTalking:)

Thursday, June 4, 2009

Biztalk : Could not load file or assembly ‘Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d

Error: Biztalk : Could not load file or assembly ‘Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified

Solution:



This error will come when you will call .net assembly from BizTalk orchestration using Expression shape or Message Assignment shape. The error telling you that the .Net assembly could not loaded means the .Net assembly is not exist in Globle Assembly Cache(GAC). So you have to put the particular assembly in GAC.

You can keep the assembly in GAC using the GACUtil command. Suppose you have the “Microsoft.Practices.EnterpriseLibrary.Logging” file in “C:\program Files\Microsoft Enterprise Library 3.1 – May 2007\Bin” folder. Open the microsoft Visual Studio Command Prompt. Then change the directory to this path(“C:\program Files\Microsoft Enterprise Library 3.1 – May 2007\Bin” ) and write the following command to put the assembly in GAC:GACUtil i/ Microsoft.Practices.EnterpriseLibrary.Logging.dllIts will install the Microsoft.Practices.EnterpriseLibrary.Logging.dll in GAC.Now restart your BizTalk Service Host and stop and start your BizTalk application. Then test your orchestration. It will resolve your problem.Cheers:)

Regards,
Vijay Modi

Biztalk : Log entry using Microsoft Practice Enterprise Library in .Net assembly / Call .Net assembly using Biztalk Orchestration Expression Shape……

Its one of the important task in Biztalk to do the Log entry whenever you want in the orchestration. Suppose you have got an error and you need to log it before the Message going in suspended mode. One of the best solution we can use to loggin is the Microsoft Practise Enterprise library. So how will we do it? If you will implement it the first time you will face many problems like how to call .net assembly from an orchestration, configuration etc… So I thought to write an article on this full functionality. I am trying to write here as easy as possible from my side. Please let me know your comments if you have found any mistake in this article.

Important: You need to install the Microsoft Practise Enterprise Library 3.1 in your machine. You can download it from the following link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=4c557c63-708f-4280-8f0c-637481c31718&displaylang=en



First you need to create a .Net class library project using VS .net 2005. Like I am creating a project named ‘TestEntLibLogging’.
Create a class named ‘EntLibraryErrorHandler.cs’ in you project.
Make it public and serializable.
Please create a method named ‘LogErrorbyEntLibrary(Exception ex)’ in the above class.

Now you need to add reference Microsoft.Practices.EnterpriseLibrary.Logging.dll in you project. To add reference in your project just right click on your project and select ‘Add Reference’. And select the ‘Microsoft.Practices.EnterpriseLibrary.Logging.dll’ from list of assembly.

Now add the following library in you class named ‘EntLibraryErrorHandler.cs’:
using Microsoft.Practices.EnterpriseLibrary.Logging;

Now in you method ‘LogErrorbyEntLibrary(Exception ex)’, write the following code to log entry:

======================================================
//Create an object of LogEntry
LogEntry objEntry = new LogEntry();

//Assign properties to LogEntry object.
objEntry.Message = ex.ToString();
objEntry.Priority = 1;
objEntry.TimeStamp = DateTime.Now;
objEntry.Categories.Add(“Exception”);

//Do the Log entry.
Logger.Write(objEntry);
========================================================

This code will work perfectly. Now we are going to implement the configuration of the config file using ‘Enterprise Library Configuration’ tool. You can find this too in the Start > Program Files > Microsoft patterns & practices > Enterprise Library 3.1 – May 2007 > Enterprise Library Configuration.

Now open you config file in this by selecting the File Menu > Open.

Now one of the question should come in your mind is which configuration file you need to configure here. Because you have not added any configuration file in you class library project. So the answer is:

You need to configure the ‘BTSNTSvc.exe.config’. You can find this file in your ‘C:\Program Files\Microsoft BizTalk Server 2006′ folder. i.e. where you have install your BizTalk.

So open this configuration file in your Enterprise Library Configuration wizard. Now right click on your config file and add new Logging Application Block by clicking on ‘New > Logging Application Block’.

There select the ‘Tract Listeners’. Right click on it and select the New > Rolling File Trace Listener.

You can select Flat File Trace Listener. Here I am creating Rolling File Trace Listener to creating the File separately for each day.

There see the properties of new added ‘Rolling Flat File Trace Listener’. Select the Formatter and select the ‘Text Formatter’ from the list.
You can update the Template properties inside the Formatter property whatever you want. To chaange the looging file’s path you have to change the property of the FileName. If you want to put the File in C:/Log/rolling.log, then you have to write it in the FileName property.

Now we have created a Rolling File Trace Listener. Now we need to assign it to ‘Formatted EventLog TraceListener’, which is in the ‘Special Sources > Logging Errors & Warnings node’. Select the ‘Formatted EventLog TraceListener’ and in the property window select the Reference Trace Listener to ‘Rolling Flat File Trace Listener’ from the dropdown. You can change the header and footer whatever you want in your logging file.

Now you can find two properties which are very important here. One is the RollFileExistsBehavior and another is RollInterval. The default value of RollFileExistsBehavior has set to OverWrite, which means it will overwrite the existing file. This we need to update to increament. For this just select the dropdown and select increament. This will create a new file based on the interval set there.

The second one is the RollInterval, which is default set to None, means it will not create new file. It will just overwrite the existing file. This we need to set depends on the requirements. I am setting it to Day, means it will daily create a new file for log entry.

Now another property ‘RollSizeKB’, This is one important property if we want to restrict to write in file more than particular size. Like I want to create the logger file not more than 5 kb size. So I will set this property. But for now I don’t require here. But if you want you can set it there.

Now you need to set the ‘Rolling Flat File Trace Listener’ in the ‘Category Sources > General > Formatted EventLog TraceListener’. Just select this node and select the Referenced TraceListener to ‘Rolling Flat File Trace Listener’ from the dropdown.

So your configuration of the config file has been completed now.

Now our remaining part is how to call the .Net assembly (that we have created the .net class library project) from Biztalk orchestration:

I am here write the following code to call the .net assembly from an Expression shape. You need to add the reference in your biztalk project. You have to right click on your biztalk project and select the Add reference. In the add reference wizard, select the Projects tab. There you will find our Class Library project. Select this project and click ok button. So now we have added the Class Library Project’s reference in our BizTalk project. Now you can access all accessible methods in your expression shape.

Write the following sentence in you catch block of the orchestration. Here I expect that you know how to use the catch exception in orchestration. Add an expression shape and write the following sentence to call the method ‘EntLibraryErrorHandler(Exception ex)’.
======================================================
TestEntLibLogging.EntLibraryErrorHandler.LogErrorbyEntLibrary(ex);
//Where ex is the Exception Object Name
======================================================

Note: Now when you run your orchestration you will face a runtime error that the EntLibraryErrorHandler not found. Its reason is that we have not add the Class Library project in GAC. To add the above file in GAC write the following command on Visual Studio Command Prompt:
======================================================
GACUTIL /i EntLibraryErrorHandler.dll
======================================================

Now test your orchestration.
I am first time writing this type of big article on my blog. So there can be some problem. And for time purpose I have not write the steps. But I think you have the initial knowledge of the Biztalk and you can understand it. Let me know if you will have any confusion or query with this article. You can add your comments here.

Tuesday, June 2, 2009

BizTalk: Dynamically create schema nodes using XSLT

First let us discussed about the problem:I have a source schema which is in the following format:Now I want my destination schema in the following format:Note: Check I want the Ord1 and OrdName1 in OrderInfo as well as Ord2 and OrdName2 in OrderInfo too. So I need the value of above in one record (node) of Destination schema. So for to do the above functionality I have used the BizTalk map. What I have done is I have created a BizTalk map and assign Source.xsd in SourceSchema and Destination.xsd in Destination schema of this map.You can see the snap of my Mapping as follows:Here you can find that I have added just one script Functoids in the mapping of source and destination schema. I have written the following code (XSLT Code) to map both schemas.==========================================================================
<xsl:for-each select="//Order">

<xsl:element name="OrderInfo">

<xsl:element name="OrderID"><xsl:value-of select="Ord1" /></xsl:element>

<xsl:element name="OrderName"><xsl:value-of select="OrdName1" /></xsl:element>

</xsl:element>

</xsl:for-each>


<xsl:for-each select="//Order">

<xsl:element name="OrderInfo">

<xsl:element name="OrderID"><xsl:value-of select="Ord2" /></xsl:element>

<xsl:element name="OrderName"><xsl:value-of select="OrdName2" /></xsl:element>

</xsl:element>

</xsl:for-each>

==========================================================================So Test the map and check the generated destination file.Enjoy BizTalking:)