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?

3 comments:

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...

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