Archive for the 'Scribe' Category

Error when creating a Connection – ‘ownerid cannot be null’

I had a DTS that created Connection records in CRM2011.

One of the data links was setting the owner of the connection.

Upon running the DTS, I received:

‘ownerid cannot be null’

And the error report shows:

The problem is in the file DynamicsCRMAdapter2011Metadata.xml, which can be found in the Scribe installation directory (normally C:\Program Files (x86)\Scribe). The file that ships with the Scribe installation is missing a row. It needs:

<BusinessEntityTableMetadata TableName="connection" SupportsOwned="true" 

The important parts are the SupportsOwned and SupportsSetState attributes. These are required in order that Scribe can change the ownership or set the state of the Connection record.

After you update the metadata file, it’s necessary to refresh Scribe’s metadata cache. This is stored in the ScribeInternal database, in the Scribe.AdapterMetadata table.

The SQL to delete the cached data, and force Scribe to reload from the modified XML file is as follows:

 where NAME='DynamicsCrm 2011 Adapter'

Watch out for Nulls

Consider this (albeit fictional) formula:

One would expect that this would return either “Fruit” or “Not a fruit” depending upon the value of s1.

However, what happens if s1 is null?

The engine that Scribe uses to test equality will return null if one of the operators is null.

So :

<anything> == #NULL!  –>  #NULL!

And an ‘if’ statement using this test will also return #NULL!

So, if s1 is null, like this:

Then Scribe will behave like this:

The solution?

Nulls have to handled specifically, using the iserror() function:

Null data in resultset? Use an Alias

Consider a Scribe job that reads from CRM 4.0, and joins two entities (in this example we’re joining Activity to Contact)

I want to retrieve all the columns from the activity records, along with some columns from the Contact (in this example, the Owner of the Contact).

The source query would look something like this:

Now, I want to retrieve all fields from the activity record.

From the contact record, I want just the name of the owner.

So the Fields tab looks like this:

Easy. What could possibly go wrong?

Now I ‘test’ the DTS and the results aren’t what I’m expecting:

How can the ownerid of the contact be null?

The activity must be linked to a contact (because I ticked the ‘Required (inner join)’ box on the screenshot above), and every contact has an owner. Moreover, if I open the contact record in CRM, then I can see the owner.

The problem is because the activity record is also returning an ownerid field, and Scribe doesn’t like resultsets with duplicate column names. A similar issue occurs with SQL as the source (perhaps a topic for a later post).

How to fix it?

You need to specify an alias to make the column name unique:

And now when I test the DTS, the field is populated:

Scribe Certified Partner
Scribe MVP