BizTalk 2013 r2 CU1 update
Hi All,
BizTalk 2013 r2 CU1 update is available. Please install in your DIT , SIT environments and check if it is breaking any code and do the regression testing.
https://support.microsoft.com/en-us/kb/3055198
below are fixes that were covered.
BizTalk Server management tools and business tools
BizTalk Server B2B support
BizTalk Server Accelerators
BizTalk Server Adapters
BizTalk Server Design Tools
BizTalk Server Message Runtime, Pipelines, and Tracking
Regards,
Chaitanya
Query to get BizTalk Role Link Details
Hi All,
You can get the Role link details using Sql Query instead of going to admin console. Go to BiztalkMgmtDB Database and run the below query. it’s simple and easy
USE BizTalkMgmtDb
SELECT
app.nvcName,
role.nvcname [RoleLink],
party.nvcname [PartyName],
assembly.nvcfullname [Assembly],
sendport.nvcname [SendPort]
FROM bts_sendport sendport
INNER JOIN bts_party_sendport partysendport ON partysendport.nsendportid = sendport.nid
INNER JOIN bts_enlistedparty_operation_mapping operationmapping ON operationmapping.npartysendportid = partysendport.nid
INNER JOIN bts_enlistedparty_port_mapping portmapping ON portmapping.nid = operationmapping.nportmappingid
INNER JOIN bts_enlistedparty enlistedparty ON portmapping.nenlistedpartyid = enlistedparty.nid
INNER JOIN bts_party party ON enlistedparty.npartyid = party.nid
INNER JOIN bts_role role ON role.nid = enlistedparty.nroleid
INNER JOIN bts_rolelink_type rolelinktype ON rolelinktype.nid = role.nrolelinktypeid
INNER JOIN bts_assembly assembly ON assembly.nid = rolelinktype.nassemblyid
INNER JOIN bts_application app on app.nID=assembly.nApplicationID
Regards,
Chaitanya
Case Statement on Dynamically Generated Pivot Columns
Hi All,
I got into a situation where I have to apply case statement on Dynamically generated columns. Below is my requirement and how I how I achieved that.
I have created below result set with pivoting. Actually Server1,server2,server3 … are Rows and I am making them as columns. I am generating the servers information as columns dynamically
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | NULL | NOT EXIST |
| Assembly2 | NOT EXIST | NULL | NOT EXIST |
| Assembly3 | NOT EXIST | NULL | NOT EXIST |
I want the result like this.
| AssemblyName | Server 1 | Server 2 | Server 3 |
| Assembly1 | NOT EXIST | EXIST | NOT EXIST |
| Assembly2 | NOT EXIST | EXIST | NOT EXIST |
| Assembly3 | NOT EXIST | EXIST | NOT EXIST |
Below is the solution for this.
–Below is the Dynamic SQL Pivot Script.
DECLARE @servers nvarchar(max)
SELECT @servers =
STUFF(
(
select distinct ‘,[‘ + S.ServerName + ‘]’
from tempassemblies S
for xml path(”)
),
1,1,”)
DECLARE @SQL nvarchar(max)
SELECT @SQL = N’
select
* into Temp_Assemblies
from (
SELECT p.AssemblyName, p.ServerName,p.CurrentStatus
FROM dbo.tempassemblies p
) Data
PIVOT (
max(CurrentStatus )
FOR [ServerName]
IN (
‘ + @servers + ‘
)
) PivotTable
‘
exec sp_executesql @SQL
–update NULL values to ‘Custom Values’
DECLARE @dynamicsql nvarchar(MAX)
SELECT @dynamicsql= COALESCE(@dynamicsql + ‘ ‘, ”) +
‘UPDATE Temp_Assemblies SET ‘ + quotename(name)+ ‘ = ”EXIST” WHERE ‘ + quotename(name) + ‘ IS NULL ‘
FROM sys.columns WHERE object_id = object_id(‘Temp_Assemblies’)
EXECUTE (@dynamicsql)
Regards,
Chaitanya
Internal Server 500 Error when accessing the BizTalk Service( ./Btshttpreceive.dll)
If you are getting internal server error when you partner is connecting to your receive location ending with BSHTTPReceive.dll.
Check your host that was used for this receive location, if that host is of 32 bit, enable the App Pool for that receive location service to 32 bit mode.
If that host is of 64 bit mode, enable the App Pool to run in 64 bit mode.
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
Host Instances in Hung State and not able to Restart
Hi All,
Today we faced the issues with the Host instances as these are in hung state, all the messages are piled up and are unable to process.
If host instances are in hung mode, first try to restart the host instances, if it is not happening, check the msgbox blockings.
If blockings are there, check who is causing those blockings?
Sp_who2 active for checking the blockings in BizTalkMsgBoxDb
We have issue like Sql agent job is failing at our end
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb Job is failing at our end which needs to run at every one minute.
select count(*) from BizTalkMsgBoxDb..Spool (nolock)
that’s why we have huge number of messages piled up in the above table Of message box
Issue is above job is holding the lock on the message box tables and host instances are requesting the lock on those same tables and it is causing the blocking.
We have killed the blocking and job became successful and issue got fixed.
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
Retrieving the COM class factory for component with CLSID {254B4003-2AA7-4C82-BB2E-18BA7F22DCD2} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
When we are trying to process the 997 Message over As2 Protocol to the one of partner, we got the below issue.
Retrieving the COM class factory for component with CLSID {254B4003-2AA7-4C82-BB2E-18BA7F22DCD2} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
After troubleshooting , we got to know the root cause.
We are sending the AS2 Message in a send port that was running under the 64 Bit host instance.
Reason : AS2 can only work on 32 bit host. It’s one of the known issues in BizTalk due to its ExplorerOM Dependencies
Resolution:
Delete the host instances of that host and change the host setting to 32 bit mode and then recreate the new host instances.
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
Unable to cast object of type ‘Microsoft.BizTalk.Streaming.VirtualStream’ to type ‘System.IO.MemoryStream’.
During our BizTalk migration, we have encountered below error in the BizTalk administration console. This was the generic Error BizTalk Will throw when you are having any issues while receiving the message from Partner
Reason: Unable to cast object of type ‘Microsoft.BizTalk.Streaming.VirtualStream’ to type ‘System.IO.MemoryStream’.
Upton now we have below cases (Types of issues found for this error).
Case 1:
We thought it would be certificate error and we are checking partner certificate and our certificate.
But when we checked the event viewer, we are seeing the below error and interestingly we are not seeing this Error in our old server when the partner is sending the same file again and again
So we compared the EDI settings of same party in Old server with new server and we identified the issue
Duplicate check functionality is enabled in new server and it was disabled in old server for interchange settings
New Server Interchange Settings
Old Server Interchange Settings
So disabled the settings and issue got fixed.
Case 2:
We have below error in the event viewer for the error message (Unable to cast object of type ‘Microsoft.BizTalk.Streaming.VirtualStream’ to type ‘System.IO.MemoryStream’.) in BizTalk Administration console.
The AS2 Decoder failed processing when validating the MIC value returned in the MDN. Details of the MDN message are
as follows: AS2-From:"Vendor1" AS2-To:"MyCompany"
MessageID:"<1875777344.1081412822113762.JavaMail.root@cmb2bi59>" OriginalMessageID:"5fe1e2b4-2400-4536-9202-
749f8ce2a795"
Resolution:
when we are sending the As2 Message with one certificate and we are receiving the MDN from different certificate, we will get this error
Case 3:
Reporting-UA: Integration Server
Original-Recipient: rfc822; Vendor
Final-Recipient: rfc822; Vendor
Original-Message-ID: 8d6a7516-3ca4-49e0-b4c2-cfcbcbe41293
Disposition: automatic-action/MDN-sent-automatically; processed/error: insufficient-message-security
Resolution:
we will get the below error when we received the MDN from Partner with a certificate which was not there in the
other people if that certificate is not configured at party level
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
Partner Received MDN with Expired Status
Hi All ,
When we received AS2 Message from partner, we will send the MDN to them.
As it is asynchronous model we will have dedicated send port for sending MDN to them with the filters having the AS2 ID of the partner, so that whenever we got the AS2 Message from Partner, we will extract As2 Message header properties and create the MDN message and store it in the Message Box, and our MDN send port subscription which is already in message box and BizTalk make this correlation and get the MDN message and send this MDN message to Partner.
But we are sending the MDNs to them when they sent the As2 Messages but it is having expired status at partner.
I have seen this issue happened at partner level due to below issues.
1. We are sending the MDN to them with the different certificate other than the certificate which was configured at partner end.
2. We are having the some string attached to AS2ID in the send port filter , ideally it should not go to partner, but BizTalk is sending the MDN to them , but for partner it was coming as expired.
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
Different AS2 ID Partner Profiles have Different Certificates for Single Party in BizTalk 2013
Hi All,
We got into a situation where we have 2 AS2 Profiles for one of the partner and they are sending the Message with signing and encrypted properties with the certificate
Signing will be done by partner with their private key certificate.
Encryption will be done by partner using the Our Public Key certificate.
We have a situation where our party have 2 AS2 Profiles and one will be doing EDI Transactions and other will doing XML Transaction.
In the old server , we are having the different parties for different profiles.
So those parties have their respective profile signing certificate configured. So it cannot be a issue.
But in 2013, we have merged 2 profiles under one party and for a party you can configure only one certificate at a time.
If partner is sending the message with one certificate then Party should configured with that signing certificate.
Resolution:
Remove the certificate from the party, so that BizTalk will check in the Certificate store for signing .
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
MDN missing in BizTalk 2013
Hi All,
When we tried to send any AS2 Message to the partner, they will send the MDN message for the As2 Message and this is the expected flow in B2b Solutions.
Somehow our BizTalk orchestration is not picking that message and orchestration instance is not closing.
After troubleshooting , we found the issue at AS2 Agreement in the BizTalk Party profile and the Process inbound MDN into MessageBox for routing/delivery options is not selected
We selected this property and recycled the App pool related to the partner URL and the issue got fixed.
Regards,
Visit site: http://www.sqlblogging.com
Send an Email: sqlblogging
SQL BLOGGING 