Wednesday, August 27, 2008

Problems with BDC Definition Editor

Here is a problem I found while preparing for my TechEd Presentation next week (Wednesday evening). I want to show how to use the "Microsoft ® Business Data Catalog Definition Editor" application that helps create BDC ADF files. It allows to connect to a SQL server or a web service, and generates the ADF file, with entities that you choose, and some methods, and then allows you to modify and test your methods. A nice tool all around, but incomplete if you ask me.
The first problem I had with it was when I tried to connect to the AdventureWorksLT database. The database looks like this:

What was the problem? The Definition Editor application connected to the database without any problems, and showed me the tables and views, but when I tried to drag and drop a table to the design surface, I got the following error: "Could not process Table 'Customer'.Make sure you have SELECT Rights on the Table/VIEW."

After searching the net, I have found that many people have seen it, and some have blogged about it, but there was no solution.
I found that if I drag some of the tables in the database - BuildVersion and ErrorLog tables, then it works.
What is different between those two tables and the rest? Well - those two tables are using the DBO schema, while the rest are using the SalesLT schema.
Yes - the BDC Definition Editor doesnt play with custom schemas. Not documented anywhere - so I might as well document it here. I loaded the AdventureWorksDW database, where it doesnt use a custom schema (and all the MSDN samples use for some reason) and what do you know? it works.

Problem 2: The BDC Definition Editor does not generate finder methods. This is very annoying! I mean, when we select a table to be an entity in the design surface, we have a choice to choose which field is the title field for that table, but the editor doesnt do anything with that selection - leaving it up to us to create the finder method that will allow the users to search for an entity by name instead of by ID. WHY???!

Problem 3: This isnt really a problem with the tool, but a problem with me, so here is a hint in case I forget this again - The wildcard character attribute appears when you click on the LOB system node itself. Not at the instance level.

After TechEd I will upload some of my demos here as videos. Interested?


Anonymous said...

Any sign that Microsoft will fix these long simmering errors in their BDC editor or do they want us all to shell out $1200 for a BDC editor that actually works?

Santanayana said...

Yes I'm watching and reading everything You produced . This is concret! Let's do it.

Oliveo sterrenteam D7 said...

You can create a dbo.view? Then the bdc is working