Error while inserting the data to SQL Server from BizTalk WCF-SQL Send Port

Good day all,

When BizTalk is inserting the data into the custom database that you have we might encounter a lot of issues.
The below error is one among many other common issues that we get in non-Production servers.

Error:
System.Data.SqlClient.SqlException (0x80131904): Could not allocate space for object 

'YourTableName'.'YourPrimaryKeyName' in database 'YourDatabaseName' because the 'PRIMARY' filegroup is full. 

Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the 

filegroup, or setting autogrowth on for existing files in the filegroup.

Server stack trace: 
   at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.Channels

Cause:

SQL Server databases have .mdf (PRIMARY data file) and .ldf(log data files). On each database, we can set the Initial Size(in MB) and Autogrowth/ Max size for data and log files on the database.

As the data gets inserted into the database the files grow accordingly. 

Solution:

When the SQL Server database data files are full we encounter the above issue. We need to fix the storage issues on the DB Server.

When SFTP send port is Configured with port number different from the port number that SFTP site is configured on.

Hi All,

Hope you are all doing well.

Below is my observation when SFTP send port is configured to run on a different port than Destination SFTP site configured port.

WinSCP.SessionRemoteException: Network error: Connection to "FTPServerIP or FTPDNSName" timed out. ---> WinSCP.SessionRemoteException: The server rejected SFTP connection, but it listens for FTP connections.

Did you want to use FTP protocol instead of SFTP? Prefer using encryption.
   --- End of inner exception stack trace ---

Server stack trace: 
   at WinSCP.SessionLogReader.Read(LogReadFlags flags)
   at WinSCP.ElementLogReader.Read(LogReadFlags flags)
   at WinSCP.SessionElementLogReader.Read(LogReadFlags flags)
   at WinSCP.CustomLogReader.TryWaitForNonEmptyElement(String localName, LogReadFlags flags)
   at WinSCP.CustomLogReader.WaitForGroupAndCreateLogReader()
   at WinSCP.Session.Open(SessionOptions sessionOptions)
   at Microsoft.BizTalk.Adapter.Sftp.SftpConnection.OpenUnderlyingConnection(SftpConnectionProperties connectionProperties)
   at Microsoft.BizTalk.Adapters.CommonHelpers.Connection`3.ConnectionPool`3.GetConnection(T1 connectionProperties, TimeSpan timeout)
   at Microsoft.BizTalk.Adapters.CommonHelpers.Connection`3.GetConnection(T connectionProperties, TimeSpan timeout)
   at Microsoft.BizTalk.Adapter.Sftp.SftpOutputChannel.Send(Message message, TimeSpan timeOut)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]: 
   at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndSend(IAsyncResult result)

Exception rethrown at [1]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.Channels.IOutputChannel.EndSend(IAsyncResult result)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendCallback(IAsyncResult result)

BizTalk FF Pipeline: The remaining stream has unrecognizable data.

Hi All,

Hope everyone is enjoying the festive season.

Another small article on the error that I have got when I am handling the Flat file message on the receive side.

Error:-
The remaining stream has unrecognizable data and HRESULT:c0c01464 in BizTalk on the
Log Name:      Application
Source:        BizTalk Server
Date:          2019-12-17 10:34:33 AM
Event ID:      5753
Task Category: BizTalk Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      YourBizTalkServerName
Description:
A message received by adapter "BiztalkMessagingEngine" on receive location "YourReceiveLocationName" with URI "YourLocationAddress" is suspended.
 Error details: There was a failure executing the receive pipeline: "YourPipelineFullyQualifiedName, YourProjectName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=27a168965285aebe" Source: "Flat file disassembler" Receive Port: "YourReceivePortName" URI: "YourLocationAddress" Reason: The remaining stream has unrecognizable data. 
 MessageId:  {BC21C975-006C-418F-97A5-EAAE7472C0E8}
 InstanceID: {0DA3DA9D-1234-4D0A-B623-68159BF68DDA}
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="BizTalk Server" />
    <EventID Qualifiers="49344">5753</EventID>
    <Level>2</Level>
    <Task>1</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2019-12-17T08:34:33.000000000Z" />
    <EventRecordID>199652</EventRecordID>
    <Channel>Application</Channel>
    <Computer>YourBizTalkServerName</Computer>
    <Security />
  </System>
  <EventData>
    <Data>{BC21C975-006C-418F-97A5-EAAE7472C0E8}</Data>
    <Data>{0DA3DA9D-1234-4D0A-B623-68159BF68DDA}</Data>
    <Data>BiztalkMessagingEngine</Data>
    <Data>Your location address</Data>
    <Data>YourReceiveLocationName</Data>
    <Data>There was a failure executing the receive pipeline: "YourPipelineFullyQualifiedName, SYourProjectName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=27a168965285aebe" Source: "Flat file disassembler" Receive Port: "YourReceivePortName" URI: "YourLocationAddress" Reason: The remaining stream has unrecognizable data.  </Data>
  </EventData>
</Event>

Cause:- In my case, I have a repeating record which is separated with the LF(Line Feed) character, For some reason, the source system has generated the file with an extra LF character after the first record (first line) which is where the problem is. On the Schema, after first LF, the pipeline is expecting a new record. In my case instead of the new data record, there is another LF which is causing the pipeline to fail with the above-mentioned error.

Solution:- Souce System to correct the behaviour

Execute the FFDASM to create the scenario:- Below is the error when I ran FFDASM.
ffdasm.exe yourTestInputFile.FileExtention -bs yourSchema.xsd

HRESULT:        c0c01464

BizTalk: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

Hi BizTalk Buddies,

Hope you are all doing great. Time for another Article.

As we all know that the BizTalk host can either be 32-bit or 64-bit and our host instances run on those hosts. 

As we might also know, 
Running the FTP adapter, POP3 adapter, and MIME Decoder on 64-bit host instances is not supported. We can only run them on 32-bit host instances.

Errors in the event viewer:-
There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: "sendPortName" URI: "mssql://xxxxServerName//xxxxDBName?" Reason: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)


A message sent to adapter "WCF-SQL" on send port "sendPortName" with URI "mssql://xxxxServerName//xxxxDBName?" is suspended. 
 Error details: There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: "xxxYourSendPortName" URI: "mssql://ltvmanchikanti//Insurance_Collections?" Reason: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)  
 MessageId:  {934486D4-5F64-4EED-BBE8-E68A5F5DDBA8}
 InstanceID: {C78702BB-4DFE-412B-98F9-AAD347660ACC}


There was a failure executing the response(receive) pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Send Port: "xxxYourSendPortName" URI: "mssql://xxxxServerName//xxxxDBName?" Reason: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

Reason:- The reason why we are getting the above errors is that our send adapter hander on the port where we get an error is running on a 32-bit host as opposed to 64-bit.

NB: Normally this happens when we use default host(BizTalkServerApplication which might be a 32-bit host) that gets created when BizTalk is installed.

Solution Steps:- 

• We must create the host with 32-bit only checkbox un-checked as shown below.

• Create the send/receive handler on the Adapter(in my Case WCF-SQL but the principle is same for other adapters that run on the 64-bit host) that we have experienceing the issue on.

• Select the adapter handler that is created in the above step as shown below.

BizTalk WCF-SQL Polling and de-batching on Receive Port(De-batching SQL Data)

Hi BizTalk Buddies,

Hope you are all doing great. Every one of us come across the scenario where we need to poll from the database. When we are polling, we can either can poll the single record at a time and process it or poll multiple records at a time and de-batch using Envelope schema on the receive port and Process the de-batched records individually. Here is a simple article that explains how to poll from the DB and de-batch on the receive port and subscribe to the Debatched Messages.

Below is the Employee Schema(Employee.xsd) that contains the Single Employee.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employee" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://BTS_SQL_Debatch.Employee" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employee">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Name" type="xs:string" />
        <xs:element name="ID" type="xs:string" />
        <xs:element name="Salary" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
Below is the Employees Schema(Employees.xsd) that contains multiple employees that are polled from the database. Employee schema is imported to this Schema. Employees Schema has marked as an Envelope and the Body XPath is selected as shown below.
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BTS_SQL_Debatch.Employees" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:ns0="http://BTS_SQL_Debatch.Employee" targetNamespace="http://BTS_SQL_Debatch.Employees" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:import schemaLocation=".\Employee.xsd" namespace="http://BTS_SQL_Debatch.Employee" />
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo is_envelope="yes" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
      <b:references>
        <b:reference targetNamespace="http://BTS_SQL_Debatch.Employee" />
      </b:references>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="Employees">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo body_xpath="/*[local-name()='Employees' and namespace-uri()='http://BTS_SQL_Debatch.Employees']" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="ns0:Employee" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Deploy the solution.

Once the solution is been deployed, We need to create One-way WCF-SQL receive Port as shown below.
Port Name:- rcv_Employees
Receive Location Name:- rcv_WCF-SQL_Employees
Type:- WCF-SQL
Receive Pipeline:- XMLReceive

In the Configure section please do the below settings.
Address(URI):- mssql://YourServerName//YourDatabaseName?InboundId=getEmployees
XmlStoredProcedureRootNodeName:- Employees
XmlStoredProcedureRootNodeNamespace:- http://BTS_SQL_Debatch.Employees
InboundOperationType:- "XmlPolling"
PolledDataAvailableStatement:-  SELECT COUNT(1) FROM [dbo].[Employee] WHERE IsPolled IS NULL AND DatetimeBizTalkPolled IS NULL
PollingStatement:-   exec [dbo].[getEmployees]

Rest of the properties you can leave then to the default values.

Save the above configuration. 

Now Create a FILE Sendport that subscribes on BTS.MessageType="http://BTS_SQL_Debatch.Employee#Employee"

Below are the scripts to create the table and the stored proc.

--Table
CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[ID] [int] NULL,
[Salary] [decimal](18, 2) NULL,
[PollingId] [uniqueidentifier] NULL,
[IsPolled] [bit] NULL,
[DatetimeBizTalkPolled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Venu', 123, CAST(10.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Ravi', 124, CAST(34.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Raghu', 126, CAST(19.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO
INSERT [dbo].[Employee] ([Name], [ID], [Salary], [PollingId], [IsPolled], [DatetimeBizTalkPolled]) VALUES (N'Rakesh', 129, CAST(63.00 AS Decimal(18, 2)), NULL, NULL, NULL)
GO

Oracle Client Installation on BizTalk Server 2016

Hello BizTalk Buddies,

It has been a while that since I have written an article. 

Recently, I have had a requirement to upgrade BizTalk from 2010 to 2016. My client is using Oracle Database Adapter extensively in BizTalk Server.

There are good Articles that explain the installation and configuration or ODAC for BizTalk 2016 but I feel that they are not detail enough so it's my attempt to clear the gaps in understanding.

Before you can download the ODAC (Oracle Data Access Components), create an Oracle online account from the below URL.

https://login.oracle.com/mysso/signon.jsp

Once you have created an Oracle account, log in to oracle online and you can navigate to the below URL to download the 32 bit Version of the ODAC.

https://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

Accept the License Agreement as shown below.

On the same page navigate down till you spot the 12.1.0.1.0 version of ODAC as shown below.

Logon to the BizTalk Server using either an Administrator's account or the service account that the host instances run on.

Open the command line as an administrator, and navigate to the folder location where you have extracted the downloaded zip file.

In my case, I have downloaded and extracted the file to the below location.

D:\Software\ODAC121010Xcopy_32bit

In the command prompt run the following command if you would like to install to C:\ Drive on the server. Otherwise, change the drive accordingly and run the below command.

D:\Software\Oracle Client\ODAC121010Xcopy_32bit>install.bat all C:\Oracle myhome

Note:- Some checks after the above installation is completed.

1). Double-check that, the Oracle home path is set up properly in System Environment variables.

2). If you are using TNS name aliases, then double-check your file exists in the respective location. In my case, it is in C:\Oracle\network\admin\tnsnames.ora.