One of the things I love best about SharePoint is I am always learning something new every day. In my experience, when things work right the first time you try something, you don’t learn much. Conversely, when things don’t work correctly, I learn tons of stuff. ‘Nuff said about learning opportunities in SharePoint.
I recently was working with a customer who wanted to use BCS to update records in an external database. After some initial research I learned I could use a workflow to update an external list (http://msdn.microsoft.com/en-us/library/office/ff394479(v=office.14).aspx).
I set up a test table in a SQL database, created the External Content Type in Designer, and after a few minor errors that I was able to figure out, was finally able to display the SQL database in SharePoint through an external list. Hurray! However, when I ran a workflow to update a record, the workflow would fail with the following error: “An error occurred in the UpdateRecord Workflow”. Not real helpful, right? So I went through the log files, Googled the problem, did a lot of reading but came up snookered. I finally opened a MSDN help ticket and through the course of 3 months and many hours of phone conversations with a variety of tech support people, they finally were able to debug the problem as a data type mismatch. Here is the tech’s explanation:
I was able to debug and confirmed the root cause of the problem is because the backend SQL DB table has nchar as the data type for the ID field. The reason for that is because in the call stack shown below, the condition that evaluates whether the field value looked up from external list matches with the value we specified does not pass because the comparison is between:
“1 “ == “1”
Internally we always do a string comparison. Since the values of the ID field has the data type nchar, the actual value becomes “1 “ instead of just “1”.
To mitigate this problem, I suggest two approaches:
- Use integer data type for identity fields when possible (recommended)
- Use nvarchar instead of nchar as that will ensure the trailing spaces are trimmed and the above validation will succeed
Hopefully this solution will shave several months off of someone else’s BCS troubleshooting ordeal.
