Hi

The other day, I suddenly found myself in a situation, where I needed to use the "Find message" functionality and HAT to find a message, and I needed to filter on a promoted property.

My problems started appearing, when there were no promoted properties to select in the Message properties filter view. I had chosen my schema, but still, the dropdowns for promoted properties were empty.

So I checked that tracking of both message bodies and promoted properties were enabled in the receive port. Yes, they were... so I started wondering what might be the cause of this. And then I though: Oh yeah, you need to enable tracking of these individually in HAT. But in HAT I couldn't find anywhere to enable this. That stunned me for a couple of minutes. Turns out, that was in BizTalk 2004 :-S

Whoops :-)

And the I remembered that I needed to do two things in order to get the tracking of promoted properties to work:

The first is to enable the tracking in the receive port:

And the second thing is to enable tracking of each specific promoted property - and now I remembered it isn't in HAT anymore, it is in BizTalk Server Administration for each schema. So open BizTalk Server Administration. Go to the application that has the schema, for which you need to track promoted properties. Go to the schemas collection of this application, double click on the appropriate schema, go to the tracking pane, and here you must select the properties to track.

I googled this a lot, but had great difficulty finding any information... that's the reason for this post.

I hope others will find it useful in the future.

Comments are welcome

--
eliasen

Hi

I have just written a blog post about using the SQL Adapter Wizard and during my tests for this post, I found the cause of an error that I saw someone in the newsgroups ask about. So I will just post the answer here.

I got these errors when compiling my project, in which I have used the SQL Adapter Wizard twice to create a schema for me:

Error 1 illegal name-hiding: 'Orchestration_1' hides 'SQLAdapter.Orchestration_1' on line 101 C:\Projects\BTS 2006\BlogEntries\SQLAdapter\BizTalk Orchestration_1.odx 102 23 

Error 2 symbol 'Orchestration_1' is already defined; the first definition is in file C:\Projects\BTS 2006\BlogEntries\SQLAdapter\BizTalk Orchestration.odx on line 101 C:\Projects\BTS 2006\BlogEntries\SQLAdapter\BizTalk Orchestration_1.odx 102 23

It turns out, that when you use the SQL Adapter Wizard to automatically create schemas for you, then an orchestration is also created for you. Yes, I knew that, you say? Well, so did I :-) What I didn't know was that the Wizard is clever enough to give the two orchestrations different filenames, but stupid enough to give them both the .NET type name "Orchestration_1". So when compiling the project, you have two orchestrations with the same fully qualified typename, which off course will fail.

See this screenshot:

As you can see at the lower right vorner of the picture, the "BizTalk Orchestration_1.odx" has "Orchestration_1" as its typename. Well, "BizTalk Orchestration.odx" has the same.

"So I just change one of them, no problem", you say? Well, there is another problem... If you look at the port types and multi-part message types in each orchestration, they are also given the same names. So changing the typename of one of the orchestrations removes the compilation error, but it will just give you the next set of errors, as more things are given the same name, and therefor more illegal name-hiding is taking place. Se the post here about what you can do to rename things.

I hope this helps someone.

--
eliasen

Hi

When using the SQL Adapter wizard to generate schemas for updategrams, queries or calling stored procedures, the wizard will create an orhestration and a schema for you in your project. The orchestration is named BizTalk Orchestration.odx (or if this allready exists, then it will add _1, _2 and so on) and the xsd will be called SQLService_<chosen_rootnode_in_wizard>.xsd

These names are of course not useful in most scenarios, since most projects have naming conventions that must be followed. Moreover, the orchestration is often just annoying, since the database read/update usually must happen in an allready existing orchestration.

What you should do is this:

1. Rename the xsd to follow your naming conventions
2. Rename the typename of the schema to match the filename
3. Open the automatically created orchestration, and:
• Update the parts of the two created multi-part messages to point to the new typename of the schema
• Rename the port type that is created to something meaningful
• Rename the multi-part message types that are created to be more meaningful.
• The last two steps are very important, if you plan to use the SQL Adapter more than once in the same orchestration, since otherwise you will get type names in both generated orchestrations that are the same. And this isn't allowed, off course.
4. Cut and paste the port type and multi-part message types from BizTalk Orchestration.odx to the orchestration where you actually need them.
5. Delete the BizTalk Orchestration.odx file, since it is no longer needed.

So basically, the automatically created names of things when using the SQL Adapter Wizard are useless. And also, the created orchestration is useless after moving the automatically created types away from it.

I hope this is useful for someone at some point.

--
eliasen

Hi

I had post some time ago, which explained how to promote fields from a schema generated by the SQL Adapter. This post contains some information that could be useful for people searching the net for information about changing the records in the generated schema into elements. A search for those words wouldn't have my post in the top 1000 hits, though :-)

So I thought just to have this simple post with a title that will hopefully match what people search for, and let them find the information.

That's it :-)

--
eliasen

Hi

Sometimes I run into this error, when deploying a BizTalk project from VS.NET: "An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I take a quick look at it, bang my head in the desk for doing it again, and then I perform the following steps:

1. Rightclick on the project and choose Properties
2. Go to "Configuration Properties" => "Deployment"
3. Change the "Server" property to be the servername of the SQL Server you are deploying to
4. Save changes
5. Deploy again

This happens if you receive a copy of someone elses project - since the servername is listed in the projectname.btproj.user file that is a part of the project.

Hope someone else gets quickly past this hurdle now it has been blogged.

--
eliasen

Hi

I have just had a client who was upgrading from the BizTalk Accelerator for SWIFT 2.3SP1 to the 2006 Message Pack, and it didn't go as expected.

Basically, after upgrading, using the migration guide found at http://www.microsoft.com/downloads/details.aspx?FamilyID=5f0a2355-b17f-479c-9b14-f784d5da9a95&DisplayLang=en, it didn't work. A warning appeared in the eventlog when trying an MT541 document:

An exeption occured inside the rule engine instance executing policy "MT541_Master_Policy"... Now, the obvious errors were checked for - the policy WAS deployed and undeploying it gave a whole new error. So something must go wrong at runtime, evaluating the policy. The policy only consisted of a single rule, but this rule has plenty of actions. I tried removing the actions one at the time, in new versions of the policy, and finally found out that it wouldn't work until I had removed ALL the actions.

So, basically, something must be very wrong.

The solution ended up being doing some assembly binding.

The migration guide states that:

"You must configure the assembly bindings on all computers in your environment using .NET Framework 1.1. You do not need to configure the assembly bindings for computers using .NET Framework 2.0. "

BUT, it is wrong! When using BizTalk 2006, which, as we all know, is running under .NET 2.0, you STILL need to do the assembly binding. You just need to do it using the .NET 2.0 Configuration instead of the .NET 1.1 Configuration, as written in the migration guide.

After that, the error disappeared, but another one turned up:

Now I was really mad. This error looks just like the first one, it is just another policy that is going wrong. Anyway, this turned out to be a known bug... sort of: http://support.microsoft.com/default.aspx?scid=kb;EN-US;928259 - the KB article states that it is for the Accelerator for SWIFT 2.1 - but actually it is for the 2006 MEssage Pack and applies to BOTH 2.1 AND 2.3.

So I was really in for a difficult job here - having to go against the migration guide form Microsoft and installing a hotfix that wasn't for this version of the accelerator.

I had actually given up, and I started a support incident with Microsoft, so all these steps were done as suggested by the support guy. The support incident hasn't cost me anything, as you might guess :-)

I hope someone else can benefit from this at some point.

--
eliasen

Hi

I was trying to help someone in the BizTalk newsgroups, creating a flat file schema, and I ran into this very annoying error message: "Unable to match the data in the input stream.". I got the error message after having created the schema and trying to validate an instance, using VS.NET 2005.

I searched and searched...

• Someone needed to set the "parser_optimization" to "complexity" - I had allready done that.
• Someone else needed to set the "lookahead_depth to 0 - I had also tried that.
• Someone needed to set the input type to "Native" instead of XML. Had that been my problem, I wouldn't have blogged about it - I'd be too embarassed :-)

No, the problem was that I had created parts of the schema using the flat file wizard. It had set one of my records to have "minOccurs" = 18 and "maxOccurs" = 18 as well. Since my test instance didn't have that many records in it, I got this error message. Changing the two 18's to the default 1 solved my problem.

I hope Microsoft considers this and perhaps in future releases find a better error description :-)

In order to test it, you can just download the working schema: Exolit_FlatFileSchema.xsd (91.59 KB) and the test instance: Instance.txt (.72 KB) - it will validate. But try to change the minOccurs and maxOccures of the "Invoices"-record to "18" - and revalidate.

Anyway... Hope this helps someone in the future.

--
eliasen

Hi

Should you ever run into this warning in the eventlog, when using the SQL Adapter for BizTalk:

your problem is that the SQL Adapter is receiving a result set instead of XML.

There are two possibilities:

1. As we all know, when using the SQL Adapter to call a stored procedure, you must write "for xml auto, xmldata" (or some variant - I like the "elements" way of things) in your Stored Procedure in order to let the schema generator generate a schema for you. After the schema has been generated, you need to remove the ", xmldata" or else schema data will be returned every time the SP is called instead of real data. Anyway, sometimes people remove too much from the SP, ie. they remove the entire "for xml auto, xmldata" instead of just ", xmldata".
2. Sometimes, someone forgets to write "for xml auto" after a select statement in the receive location.

Both will cause the above mentioned error.

Hope this has helped someone.

--
eliasen

Hi

I have just played quickly with receiving flat files.

Often, people create a receive pipeline for each flat file messagetype that must be received. This is done because the flat file disassembler needs to know exactly which document schema is to be used to parse the incoming message, as opposed to the XML disassembler, which will find the schema at runtime. So instead of having several flat file disassemblers in one pipeline, often, many pipelines are created.

BUT, I'd just like to point out that with BizTalk 2006, you can change properties of the pipeline components on a per instance level. So double click on your receive location, click the ellipsis on the pipeline and go to the "DocumentSpecName" property of the flat file disassembler. Why they haven't called it "Document Schema" as it is called in the pipeline designer, I don't know!

Anyway, here you can specify which document schema this pipeline instance should use. You must specify the fully qualified .NET typename of the schema, however. And it is a textbox, not a drop down, as in the designer. You can get the .NET typename by clicking on the .xsd in solution explorer, and taking it from the "Fually Qualified Name" property.

Hope this was helpful

--
eliasen

Hi

When generating a schema based on the SQL Adapter, there are some options for manipulating the XML.

I always use the "for xml auto, elements" version, because I'm an "element-kind-of-guy". Others use the "for xml auto" for several reasons:

1. It is the default
2. Attributes create smaller XML
3. They like attributes

None the less, I'm an "element-kind-of-guy" and I do what I want! :-)

Anyway, the schemas that are the result of this leave my schema with loads of records instead of elements, meaning that I can not promote the result to distinguished fields.

There is a way of changing this, though... allthough I really don't consider it to be a nice solution :-)

As an example, I have the following table in a database:

And I have this stored procedure to get the values:

CREATE PROCEDURE [dbo].[Get_eliasen]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT * from eliasen for xml auto, elements, xmldata
END

Basically, it returns all rows from the table. The schema generated from this stored procedure will look like this:

The "seqno", "field1", "field" and "field3" are records and not elements. If I want to change them to be elements instead, I need to open the XSD in a text editor and do this manually:

As an example, I will change the record "seqno" into an elements.

The original XSD looks like this:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://eliasen.dk/SQLAdaterRecordsTest" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <msbtssql:sqlScript value="exec [Get_eliasen]" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="SQLAdaterRecordsTestRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="eliasen" xmlns:q1="http://eliasen.dk/SQLAdaterRecordsTest" type="q1:eliasenType" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name="eliasenType">
    <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="seqno" xmlns:q2="http://eliasen.dk/SQLAdaterRecordsTest" type="q2:seqnoType" />
      <xs:element name="field1" xmlns:q3="http://eliasen.dk/SQLAdaterRecordsTest" type="q3:field1Type" />
      <xs:element name="field2" xmlns:q4="http://eliasen.dk/SQLAdaterRecordsTest" type="q4:field2Type" />
      <xs:element name="field3" xmlns:q5="http://eliasen.dk/SQLAdaterRecordsTest" type="q5:field3Type" />
    </xs:choice>
  </xs:complexType>
  <xs:complexType name="seqnoType">
    <xs:simpleContent>
      <xs:extension base="xs:long" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field1Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field2Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field3Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
</xs:schema>

And I will change it into this:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://eliasen.dk/SQLAdaterRecordsTest" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <msbtssql:sqlScript value="exec [Get_eliasen]" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="SQLAdaterRecordsTestRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="eliasen" xmlns:q1="http://eliasen.dk/SQLAdaterRecordsTest" type="q1:eliasenType" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:complexType name="eliasenType">
    <xs:choice minOccurs="0" maxOccurs="unbounded">
      <xs:element name="seqno" type="xs:long" />
      <xs:element name="field1" xmlns:q3="http://eliasen.dk/SQLAdaterRecordsTest" type="q3:field1Type" />
      <xs:element name="field2" xmlns:q4="http://eliasen.dk/SQLAdaterRecordsTest" type="q4:field2Type" />
      <xs:element name="field3" xmlns:q5="http://eliasen.dk/SQLAdaterRecordsTest" type="q5:field3Type" />
    </xs:choice>
  </xs:complexType>
  <xs:complexType name="field1Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field2Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
  <xs:complexType name="field3Type">
    <xs:simpleContent>
      <xs:extension base="xs:string" />
    </xs:simpleContent>
  </xs:complexType>
</xs:schema>

So what have I done?

1. I Found the definition of the record "seqno". It was based on the type "q2:seqnoType".
2. I found where the "seqnoType" type was declared, and noted that it extended "xs:long".
3. I changed the type of "seqno" from "q2:seqnoType" to be "xs:long".
4. I removed the q2 namespace declaration from the seqno element, as it was no longer needed.
5. I removed the "seqnoType" type declaration, as it was no longer needed.

That's it. Save the XSD and reload it in your schema editor - you will get this:

But it sure would be nice to have the option of just changing it in the properties of an elements in the schema editor...

Hope this helps someone.

EDIT on the 4'th of march 2007: Remember also that the record just below the root node must be set as "maxOccurs=1" instead of unbounded. Otherwise, you can not promote the fields below the record, obviously.

--
eliasen