If you’re hand-coding ADF files and doing a lot of code/import/test cycles, use the version number to make your life easier. 

I hate to admit it, but until this week, I was always deleting the ADF and re-importing it.  This would break my business data columns and make me re-wire them.  All unnecessary.

File this under "it’s obvious once you see it".

Example:

<LobSystem
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd"
  Type="WebService"
  Version="1.2.0.0" Name="xyzzyDocumentReview"
  xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

Change that version and re-import and the existing business data column uses the updated version automatically with no additional configuration required.

</end>

 Subscribe to my blog.

In my on-going attempts at providing a more useful lookup column using BDC, I hit a wall with the BDC picker.  If you haven’t see it, the BDC picker is similar to a people picker except that it works with columns of type "business data".

You access the picker by clicking on the open book icon of a business data column as shown:

The above image shows a business data column called "Master Document Id".  That column is connected, via BDC, to a web service.  The web service returns two columns of information: Document ID and Title.  The business purpose here is to provide a "this document is based on" function.  Users select a "master" document and when they save, an event receiver copies meta data fields from the referenced master.

By default, the BDC picker looks like this when I search for a document whose ID = "38":

That’s helpful, but not good enough.  People don’t think in terms of IDs, they think in terms of titles and/or other meta data.  The picker allows you to search on other columns (e.g. Title) but won’t show the actual list of titles it found, just their DocId’s as shown here:

(The screen shot isn’t so great because I didn’t pick a search that returns any valid results, but you can see that if it had found some results, it would only have shown DocId’s, not titles).

I searched high and low for the answer to this and failed.  My colleague, the venerable Jonathan Bradshaw, had faced and solved this issue.  When I reached out to him for help, he pointed me in the right direction.

Configure the picker to show multiple columns via the "ShowInPicker" property in the ADF:

         <Property Name="ShowInPicker" Type="System.Boolean">true</Property>

In more detail:

  <!-- Title -->
  <TypeDescriptor TypeName="System.String" Name="Title" >
    <LocalizedDisplayNames>
      <LocalizedDisplayName LCID="1033">Title</LocalizedDisplayName>
    </LocalizedDisplayNames>
    <Properties>
      <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
      <Property Name="ShowInPicker" Type="System.Boolean">true</Property>
    </Properties>
  </TypeDescriptor>

Setting this property does introduce a minor problem.  As soon as you set it once, you need to set it for every column you want to show.  In my case, BDC picker showed DocId by default.  However, once I added "ShowInPicker" to Title, DocId no longer displayed.  I solved that by explicitly setting the ShowInPicker property for Doc ID.

Here is the result:

(I’ll explain the odd-looking "168 – CamlSchema.xsd" construction in a future blog post.  In short, it’s a concatenated string that allows for a slightly better user experience).

Of course, having written this blog entry, I just did a search for "ShowInPicker" and found numerous hits, including this one: http://msdn2.microsoft.com/en-us/library/ms583986.aspx.  It explains the meaning of that property along with some other good BDC stuff.

</end>

 Subscribe to my blog!

I was once again crafting BDC ADF files by hand (so that I can build up my "get off my lawn!" cred) and hit this lovely error:

"Application definition import failed.  The following error occurred:"

As you can see, there’s an error, but … it’s not going to tell me what it is.

In my case, the issue turned out that I had started off with a functional ADF for a different project that connected to a database and executed a SQL query against a view.  In this new project, I am calling a method on a web service.  I had stripped out the DB specific stuff and added my web service stuff, but failed to update the <LobSystem>’s Type attribute.  I switched it to "WebService" and I happily moved on to newer and more exciting import errors, which were handled in due course.

Here is the wrong LobSystem:

  <LobSystem
    xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
    xsi:schemaLocation=http://schemas.microsoft.com/office/2006/03/BusinessDataCatalogBDCMetadata.xsd
    Type="Database"
    Version="1.0.0.0" Name="xyzzy"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

This is correct:

  <LobSystem
    xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
    xsi:schemaLocation=http://schemas.microsoft.com/office/2006/03/BusinessDataCatalogBDCMetadata.xsd
    Type="WebService"
    Version="1.0.0.0" Name="xyzzy"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

</end>

UPDATE: This MSDN posting has some interesting observations from JXJ based on his, mainly negative, experiences going down this path: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2623565&SiteID=1

We have a business scenario where we need to link two documents libraries via a common "document ID" field.

We often use a lookup column to implements links like this.  There are several drawbacks to lookup columns, three of which are:

1. Only one column from the lookup lookup library can be linked. 
2. Performance: The source library could contain hundreds of entries.  That’s too many entries in the lookup.
3. Search: There is no integrated search.  I don’t mean in the MOSS sense of search, but there’s no way to search / filter on multiple columns from the source document library and locate the link you want.

Backed by BDC, we can use a "business data" column type and it provides a superior search and even allows multiple columns of data to appear in list views. 

I’ve had some preliminary success with this approach and plan to write more about it.

If you’ve worked this angle before and have any comments, please share!

</end>

I’ve been working with BDC today, coding ADF files by hand and generating myself some errors.  One such error:

Application definition import failed. The following error occurred: Could not load Type described by TypeDescriptor’s TypeName. Parameter name: typeName Error was encountered at or just before Line: ’35’ and Position: ’20’.

MOSS displayed this error when I tried to import the ADF XML file.

I researched the Internets and found that I was referencing the LOB instance name (from the <LobSystemInstance>) in my <TypeDescriptor> node when I should have referenced the LOB name itself (from <LobSystem>).

Wrong:

<TypeDescriptor TypeName="Conchango.KeyValue, LOB Instance Name" Name="KeyValue">

Correct:

<TypeDescriptor TypeName="Conchango.KeyValue, LOB Name" Name="KeyValue">

Hope this one saves someone an hour or two of time.

</end>

 Subscribe to my blog!

I caused a BDC error this week that manifested itself on the user interface and in the 12 hive log at runtime.

First, this appeared in the user interface:

Could not find fields to insert all the Identifier Values to correctly execute a SpecificFinder MethodInstance with Name … Ensure input Parameters have TypeDescriptors associated with every Identifier defined for this Entity.

Here’s a screen shot:

I could also cause this message to appear in the 12 hive log at will (using my patented high-tech-don’t-try-this-at-home  "mysterious errors" method):

11/14/2007 09:24:41.27         w3wp.exe (0x080C)         0x0B8C        SharePoint Portal Server         Business Data         6q4x        High         Exception in BusinessDataWebPart.OnPreRender: System.InvalidOperationException: The Identifier value ”, of Type ”, is invalid. Expected Identifier value of Type ‘System.String’. at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.FindSpecific(Object[] subIdentifierValues, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BdcClientUtil.FindEntity(Entity entity, Object[] userValues, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BusinessDataItemBuilder.GetEntityInstance(View desiredView) at Microsoft.SharePoint.Portal.WebControls.BusinessDataDetailsWebPart.GetEntityInstance() at Microsoft.SharePoint.Portal.WebControls.BusinessDataDetailsWebPart.SetDataSourceProperties()

I searched around and found some leads in the MSDN forum, but they weren’t enough for me to understand what I was doing wrong.  I watched a webcast by Ted Pattison that my company has squirreled away on a server and came to realize my problem.

In my ADF, I’m connecting to a SQL database as shown:

            <Property Name="RdbCommandText" Type="System.String">
              <![CDATA[
                SELECT
                      SETID, CARRIER_ID, EFFDT, DESCR, EFF_STATUS, TAXPAYER_ID, NETWORK_ID, FRT_FORWARD_FLG, ALT_NAME1, ALT_NAME2, LANGUAGE_CD,
                      COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3,
                      COUNTY, STATE, POSTAL, GEO_CODE, IN_CITY_LIMIT, COUNTRY_CODE, PHONE, EXTENSION, FAX, LAST_EXP_CHK_DTTM, FREIGHT_VENDOR,
                      INTERLINK_DLL, TMS_EXCLUDE_FLG
                FROM
                      dbo.PS_CARRIER_ID_VW WITH (nolock)
                WHERE
                  (SETID <> 'SHARE') and
                  (lower(CARRIER_ID) >= lower(@MinId)) and
                  (lower(CARRIER_ID) <= lower(@MaxId)) and
                  (lower(DESCR) LIKE lower(@InputDescr))
                ]]>
            </Property>

I was provided that SQL from a DBA person and I’m given to understand that it’s a special view they created just for me.  The unique key there is CARRIER_ID.

Here is the bug I introduced:

      <Identifiers>
        <Identifier Name="CARRIER_ID" TypeName="System.String" />
        <Identifier Name="DESCR" TypeName="System.String" /> 
      </Identifiers>

Somewhere along the line, I had managed to confuse myself over the meaning of <Identifiers> and added DESCR even though it’s not actually an identifier.  I took DESCR out of the identifiers set and presto! It all worked.

I hope this saves someone some grief 🙂

I’ve noticed some awkward and unnecessary hand-encoding of RdbCommandText in some examples (including MSDN documentation).

I wanted to point out to newcomers to BDC that commands can be wrapped inside a CDATA tag in their "natural" form.  So, this awkward construction:

<Property Name="RdbCommandText" Type="System.String">
  SELECT dbo.MCRS_SETTLEMENT.id, dbo.MCRS_SETTLEMENT.settlement from dbo.MCRS_SETTLEMENT 
    WHERE (id &gt;= @MinId) AND (id &lt;= @MaxId)
</Property>

can be better represented this way:

<Property Name="RdbCommandText" Type="System.String">
  <![CDATA[
    SELECT dbo.MCRS_SETTLEMENT.id, dbo.MCRS_SETTLEMENT.settlement from dbo.MCRS_SETTLEMENT
      WHERE (id >= @MinId) AND (id <= @MaxId)
  ]]>
</Property>

</end>

BDC Primer

Intro to BDC

I needed to wire up MOSS to a SQL database via BDC.  For testing/POC purposes, I wanted to embed the SQL account user id and password in the ADF.  Starting with this template (http://msdn2.microsoft.com/en-us/library/ms564221.aspx), I created an ADF that connects to a particular SQL server instance and logs in with a specific user id and password and shown in this snippet:

  <LobSystemInstances>
    <LobSystemInstance Name="ClaimsInstance">
      <Properties>
        <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
        <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
        <Property Name="RdbConnection Data Source" Type="System.String">actual server\actual instance</Property>
        <Property Name="RdbConnection Initial Catalog" Type="System.String">actual initial catalog</Property>
        <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
        <Property Name="RdbConnection Pooling" Type="System.String">false</Property>

        <!-- These are the key values: -->
        <Property Name="RdbConnection User ID" Type="System.String">actual User ID</Property>
        <Property Name="RdbConnection Password" Type="System.String">actual Password</Property>
        <Property Name="RdbConnection Trusted_Connection" Type="System.String">false</Property>

      </Properties>
    </LobSystemInstance>
  </LobSystemInstances>

It is not a best practice, but it’s useful for a quick and simple configuration for testing.  This was surprisingly difficult to figure out.  I never found a functional example with search keywords:

• adf embedded userid and password
• embed user id and password in adf
• embed user id and password in adf bdc
• sharepoint bdc primer
• sharepoint embed user id and password in adf

</end>

 Subscribe to my blog.