Sep 292009
 

According to the DTCTest tool documentation, you create a DSN, and run the DTCTester.exe

When I ran it, I got the following error:

SQLSTATE=IM002,Native error=0,msg='[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified’

The tool is 32 bit, and access the ODBC for the 32 bit client, not the 64 bit client that is in the Administration Console. To access the 32 bit client, you have to open up: c:\windows\syswow64\odbcad32.exe

Sep 252009
 

While working for a client we had to use some custom  XSLT within BizTalk to complete some requirements that was outside the bounds of the BizTalk Mapper and orchestrations. Along with what we were already doing the client came to us and wanted a value assigned to an attribute based up on the most recent input file that had been dropped so essentially based upon the a max date from an array of dates. Come to find out that XPATH 1.0 and XSLT 1.0 can’t select records based upon a max date. Here is how I solved this problem.

 

I made some generic samples to help explain. Here is a sample input document.



<?xml version="1.0" encoding="utf-8"?>
<BatchedFile>
  <Record>
    <InformationElement Date="12/09/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="11/11/2008" ID="TryAgaine" />
  </Record>
  <Record>
    <InformationElement Date="01/08/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="03/09/2008" ID="TryAgain" />
  </Record>
  <!-- I Want to pick the following record ID because it is the most recent date- -->

  <Record>
    <InformationElement Date="01/09/2009" ID="YouPickedTheRightOne" />
  </Record>
  <Record>
    <InformationElement Date="07/09/2008" ID="TryAgain" />
  </Record>
  <Record>
    <InformationElement Date="12/09/2008" ID="TryAgain" />
  </Record>
</BatchedFile>

 

The trick is to try and compare the dates. XSLT can’t make an array exactly but you can use variables in a similar way and use the node-set() function to select the values within the variable.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl userCSharp" xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp" >
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="BatchedFile">
      <!-- First Create a variable that stores the Date Attribute along with its corresponding absolute value-->
      <xsl:variable name="MaxDateRecord">
        <xsl:for-each select ="./Record/InformationElement/@Date">
          <TotalDays>
            <xsl:attribute name ="RecordDate">
              <xsl:value-of select ="self::node()"/>
            </xsl:attribute>
            <!--Please see below for explanation of totalDays function-->
            <xsl:value-of select ="userCSharp:totalDays(self::node())"/>
          </TotalDays>
        </xsl:for-each>
      </xsl:variable>
      <!--I decided to create a variable that stored the most recent date, its easier to debug-->
      <xsl:variable name ="MostRecentDate">
        <!--The predicate after TotalDays is what picks the highest number value, which then corresponds to a date-->
        <xsl:value-of select ="msxsl:node-set($MaxDateRecord)/TotalDays[not(. &lt; preceding-sibling::TotalDays or . &lt; following-sibling::TotalDays)]/@RecordDate"/>
      </xsl:variable>
      <!-- Here is where the output is generated, -->
      <MaxDate>
        <xsl:attribute name="DidIPickTheRightOne">
          <xsl:value-of select ="//InformationElement[@Date=$MostRecentDate]/@ID"/>
        </xsl:attribute>
      </MaxDate>
    </xsl:template>
  <msxsl:script language="C#" implements-prefix="userCSharp">
    <!-- This function takes a date like 09/01/2008
    and returns the number days that exist between 01/01/1990
    and the current date selected. Any date would work as a reference
    date.-->
    <![CDATA[
 public static string totalDays(string date)
        {
            DateTime d1 = new DateTime();
            DateTime d2 = new DateTime(1990, 01, 01);
            TimeSpan span = new TimeSpan();
            DateTime.TryParse(date,out d1);
            span = d1 - d2;
            return span.TotalDays.ToString();

        }
]]>
  </msxsl:script>
</xsl:stylesheet>

Okay here is an explanation of what is going on.

1. I first create a variable (MaxDateRecord) that stores the date value of each record as well as the number of days between that date and Jan 1, 1990. This gives me a numeric value that XSLT can compare.

a. Here is what MaxDateRecord would look like essentially after it had ran through all the iterations of the Record element.

<MaxDateRecord>
<TotalDays RecordDate="12/09/2008">6917</TotalDays>
<TotalDays RecordDate="11/11/2008">6889</TotalDays>
<TotalDays RecordDate="01/08/2008">6581</TotalDays>
<TotalDays RecordDate="03/09/2008">6642</TotalDays>
<TotalDays RecordDate="01/09/2009">6948</TotalDays>
<TotalDays RecordDate="07/09/2008">6764</TotalDays>
<TotalDays RecordDate="12/09/2008">6917</TotalDays>
</MaxDateRecord>

2. I then compare all of the TotalDays and find the largest one and select out the RecordDate that matches.

a. The MostRecentDate variable outputs “01/09/2009” in Visual Studio.

3. After that its an easy process of selecting the Record element with the corresponding MostRecentDate or in this case the ID of that element.

<?xml version="1.0" encoding="utf-8"?>
<!-- This is what my output should be -->
<MaxDate DidIPickTheRightOne="YouPickedTheRightOne" />

I hope I have saved some of you some time.  Since BizTalk 2009 still uses the 1.0 specification of XSLT and XPath, I was somewhat disappointed to find out there was no direct way to get this done. I know there are better ways to handle the logic I wrote in XSLT so if you have suggestions or criticisms, post them.

I have to give some thanks to Jirka Kosek for the article “Understanding the node-set() Function” http://www.xml.com/pub/a/2003/07/16/nodeset.html

as well as lhaeger for the article “Again! The power of XPATH or the missing max() function” http://www.novell.com/communities/node/6431/again-power-xpath-or-missing-max-function

Sep 082009
 

Instead of scanning the Event Log and capturing when the Receive Locations or Send Ports go down, you can use the following triggers to send out an email when they change status:

Receive Location:

CREATE TRIGGER [dbo].[ReceiveLocationChangeNotification]
   ON [dbo].[adm_ReceiveLocation]
   AFTER UPDATE
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    DECLARE @oldstatus int
  DECLARE @newstatus int
  DECLARE @ReceiveLocationName nvarchar(256)
  DECLARE @status nvarchar(10)
  DECLARE @message nvarchar(300)
  --Was the status not changed
  IF NOT UPDATE([Disabled])
  BEGIN
  RETURN
  END
  --Otherwise send out email
  select @oldstatus=(select [Disabled] from Deleted)
  select @newstatus=(select [Disabled] from Inserted)
  select @ReceiveLocationName=(select [Name] from Inserted)
  SET @message=@ReceiveLocationName+' recieve location changed from '+ case
                     when @oldstatus=-1 then 'Disabled'
                    when @oldstatus=0 then 'Enabled'
                    END + ' to ' +
                    case
                    when @newstatus=-1 then 'Disabled'
                    when @newstatus=0 then 'Enabled'
                    END
                EXEC msdb.dbo.sp_send_dbmail @recipients='person@company.com',
                @subject = @message,
                @body = @message,
                @body_format = 'HTML' ;

--  print @message

END
GO

Send Port:

CREATE TRIGGER dbo.SendPortChangeNotification
   ON dbo.bts_sendport
   AFTER UPDATE
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    DECLARE @oldstatus int
  DECLARE @newstatus int
  DECLARE @PortName nvarchar(256)
  DECLARE @status nvarchar(10)
  DECLARE @message nvarchar(300)
  --Was the status not changed
  IF NOT UPDATE(nPortStatus)
  BEGIN
  RETURN
  END
  --Otherwise send out email
  select @oldstatus=(select nPortStatus from Deleted)
  select @newstatus=(select nPortStatus from Inserted)
  select @PortName=(select nvcName from Inserted)
  SET @message=@PortName+' changed from '+ case
                     when @oldstatus=1 then 'Unenlisted'
                    when @oldstatus=2 then 'Stopped'
                    when @oldstatus=3 then 'Started'
                    END + ' to ' +
                    case
                    when @newstatus=1 then 'Unenlisted'
                    when @newstatus=2 then 'Stopped'
                    when @newstatus=3 then 'Started'
                    END
                EXEC msdb.dbo.sp_send_dbmail @recipients='person@company.com',
                @subject = @message,
                @body = @message,
                @body_format = 'HTML' ;

    --print @message

END
GO

(Did I mention the disclaimer noted on the right side of this blog?)

Sep 052009
 

While promoting code from one 64 bit environment to another, ed I can across the following error while changing the configuration

Unable to get binding type for binding extension "sqlBinding".
Verify the binding extension is registered in machine.config."

Since I have come across this a couple of times, price I might as well put a reminder out for myself:

The SQL adapter is a WCF custom binding, which is registered under System.ServiceModel in the machine.config file. A 64-bit platform has two machine.config files, one used by the 32-bit applications and the other used by the 64-bit applications. So, when you install the 64-bit version of the BizTalk Adapter Pack, the setup wizard registers the bindings in the 64-bit version of the machine.config file. However, BizTalk Server Administration console runs as a 32-bit process and hence when you configure a port for the adapter, it checks for the bindings in the 32-bit version of the machine.config file and fails giving an error.

Resolution

Install both the 32-bit and 64-bit versions of the BizTalk Adapter Pack on a 64-bit WCF LOB Adapter SDK installation.