Hi all,

I am looking for a solution to access a linked server using LLBLGen. At the moment I am using LLBLGen 2.0.0.0 Final September 28th, 2006 version and SQL Server 2000.

I have two tables in two different databases on the same server instance. On the server I have added linked servers. On the server I can executed the query that will do the cross database query and get the expected result, no errors here

SELECT * FROM DB1.dbo.Table1 INNER JOIN DB2.dbo.Table2 ON DB1.dbo.Table1.PKField = DB1.dbo.Table2.FKField WHERE DB1.dbo.Table1.PKField = 1;

In my LLBLGen project I have added the two catalogues that are use and manually create a relation between the two entities Table1 and Table2. The code was generated using the Adapter option.

When trying to do the same thing in a C# console application I will only receive the results of Table1. The code:

Table1Entity Table1; DataAccessAdapter adapter = new DataAccessAdapter(); EntityCollection records = new EntityCollection(new Table1EntityFactory());

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.Table1Entity); prefetchPath.Add(Table1Entity.PrefetchPathTable2);

IRelationPredicateFilter filter = new RelationPredicateFilter(); IEntityRelation relation = filter.Relations.Add(Table1Entity.Relations.Table2EntityUsingFKField); relation.CustomFilter = new PredicateExpression(Table1Fields.PKField == Table2Fields.FKField); relation.CustomFilterReplacesOnClause = true; // override is needed for correcting the relation

filter.PredicateExpression.Add(Table1Fields.Table1 == 1); filter.PredicateExpression.Add(Table2Fields.Status == 0); adapter.FetchEntityCollection(records, filter, prefetchPath);

When retrieving the data only the values of Table1 are returned... Because I created added an PrefetchPath2 I was expecting the results of both tables. Is this wrong? Or am I missing a magical setting??

Please help me out and thanks in advance.

André

First of all Relations and filters have nothing to do with PrefetchPaths. So in order to test the PrefetchPath try the following code:

DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection records = new EntityCollection(new Table1EntityFactory());

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.Table1Entity);
prefetchPath.Add(Table1Entity.PrefetchPathTable2);

adapter.FetchEntityCollection(records, null, prefetchPath);

If it doesn't work, then please post the resultant generated SQL query.

It doesn't work

the query that is generated is:

SELECT
DB1.dbo.Table1.*
FROM DB1.dbo.Table1

DB2 and Tabel2 are not used at all.

I did the test with two tables in the same database and .. wel same problem. Only one table is selected/fetched. So it looks like a other prefetch problem and not related to the multiple databases.

Time to search the forum I think

......

Ok, after searching and trying and trying I just can't get the prefetch to select a second table to do a join... I am out off ideas if somebody does have a hint. They are very very welcome. Thanks,

André

Just to be clear: linked servers, are the queries which fail targeting linked servers? As that's not really supported (getting meta-data from linked servers is also not supported).

The two database that I am using are on the same instance and on the server I created a linked server. In the LLBLGen project I have both catalogues and selected the entities representing the tables. The catalogues where added using the same query string, so the meta data is not fetched through the linked server, but directly through the catalogue.

In the project I manual created a relation between the two entities.

When generating there is a different behavior when the queries are generated. In MS SQL Management studio is it possible to execute a query like:

SELECT * FROM DB1.dbo.Table1 
INNER JOIN DB2.dbo.Table2 ON DB1.dbo.Table1.PKField = DB1.dbo.Table2.FKField
WHERE DB1.dbo.Table1.PKField = 1;

LLBLGen generates some queries also using a INNER JOIN and executes them without an error.

Adding the relations and filter that I need, the queries will changed using an WHERE field IN (sub query) . This is where SQL server gets lost and can’t find the object on the linked server in this sub query. Even when the sub query is using the full name DB2.dbo.Table2. So when using the INNER JOIN there is no problem.

So I am thinking that I need to change / force the generator to create a JOIN instead of an SUB query. Is this possible?

Ah after a search I see it is a bug in SQL server: http://support.microsoft.com/kb/825019/en-us

Thanks for the feedback.

Ok, I did a test using SQL 2005 and the queries are executed without any error So the bug in sql 2000 is the problem...

Thank you guys for you're time and help.

André