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:

Proxy settings and Scribe speed

I was working on a Scribe job on a client site.

The job migrated data from CRM4 to CRM2011.

It worked perfectly, although slowly. Running the job on other machines showed that it ran much faster. So this pointed to an incorrect configuration on my machine.

After much investigation we identified that the slowness was caused by the proxy settings on my machine.

In Internet Explorer, go to Tools, Internet Options, Connections tab

Click the LAN Settings button and you’ll see something like this:

Now these settings seemed correct, and my internet browsing worked perfectly.

However, the ISA server for the network I’m working on isn’t configured to supply the configuration settings. In normal internet browsing, if Internet Explorer will attempt to query for these settings and if nothing is returned then it will go direct (ie, no proxy). In normal internet browsing, this will cause a delay but it’s often not noticed because it’s small.

However, for Scribe usage, where we’re firing off many requests then this timing becomes important, as Scribe is continually waiting for a resposne for the proxy settings.

So, unticking ‘Automatically detect settings’ resolved the speed issue.

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

Microsoft Certified Professional

Scribe MVP