Hi there,

LLBLGen 2.5 Adapter Scenario(.NET 2.0) on SqlServer 2000

I dunno really if its a bug or maybe Sql-Server is freaking out. I got a quite complex query involving a prefetch path with 25 elements over 5 levels. Using the default setting of ParameterisedPrefetchPathThreshold produces an exception which I don't really understand:

System.Data.SqlClient.SqlException: Interner Fehler des Abfrageprozessors: Der Abfrageprozessor konnte keinen Abfrageplan erzeugen. Weitere Informationen erhalten Sie bei Ihrem primären Technischen Support.
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
bei System.Data.SqlClient.SqlDataReader.get_MetaData()
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
bei SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Sorry, but I don't know how to translate that into english. Raising ParameterisedPrefetchPathThreshold up to 100 solves the problem. Is there something wrong with the recently introduced prefetch optimization?

Regards, André

Now when I modify the number of parameters of the byte arrays it doesnt work no more. See attached code (the code is generated by a custom tool for modelling LLBLGEN queries)

public static EntityCollection<OrderArticleEntity> GetOrderArticles(byte[] aOrderArticleIdStatusOrderArticle,byte[] aOrderArticleIdStatusPayment,byte[] aOrderArticleIdStatusFinance,byte[] aOrderArticleIdStatusShipment,DateTime orderDateFrom,DateTime orderDateTo) {
            EntityCollection<OrderArticleEntity> _OrderArticles = new EntityCollection<OrderArticleEntity>();
            IRelationPredicateBucket bucket = new RelationPredicateBucket();

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(OrderArticleFields.IdStatusOrderArticle == aOrderArticleIdStatusOrderArticle);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusPayment == aOrderArticleIdStatusPayment);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusFinance == aOrderArticleIdStatusFinance);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.IdStatusShipment == aOrderArticleIdStatusShipment);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.PurchaseOrderDate >= orderDateFrom);
            bucket.PredicateExpression.Add(filter);
            filter.Add(OrderArticleFields.PurchaseOrderDate <= orderDateTo);
            bucket.PredicateExpression.Add(filter);

            ISortExpression sorter = new SortExpression();
            bucket.Relations.Add(OrderArticleEntity.Relations.OrderEntityUsingIdOrder);
            bucket.Relations.Add(OrderContactingEntity.Relations.OrderEntityUsingIdOrder);

            IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.OrderArticleEntity);
            // * Prefetch: OrderVolumeDiscount
            IPrefetchPathElement2 fetchOrderVolumeDiscount =
                /// FirstLevel Prefetch: OrderVolumeDiscount
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderVolumeDiscount,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderVolumeDiscount -> OrderVolumeDiscountEntry
            fetchOrderVolumeDiscount.SubPath.Add(OrderVolumeDiscountEntity.PrefetchPathOrderVolumeDiscountEntry,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticlePriceToken
            IPrefetchPathElement2 fetchOrderArticlePriceToken =
                /// FirstLevel Prefetch: OrderArticlePriceToken
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticlePriceToken,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticlePriceToken -> PriceToken
            fetchOrderArticlePriceToken.SubPath.Add(OrderArticlePriceTokenEntity.PrefetchPathPriceToken,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleTracking
            IPrefetchPathElement2 fetchOrderArticleTracking =
                /// FirstLevel Prefetch: OrderArticleTracking
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleTracking,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleTracking -> Tracking
            fetchOrderArticleTracking.SubPath.Add(OrderArticleTrackingEntity.PrefetchPathTracking,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleType
            IPrefetchPathElement2 fetchOrderArticleType =
                /// FirstLevel Prefetch: OrderArticleType
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleType,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleType -> OrderArticleTypeTranslation
            fetchOrderArticleType.SubPath.Add(OrderArticleTypeEntity.PrefetchPathOrderArticleTypeTranslation,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticlePrice
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticlePrice,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticleNote
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleNote,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderArticleGroup
            IPrefetchPathElement2 fetchOrderArticleGroup =
                /// FirstLevel Prefetch: OrderArticleGroup
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleGroup,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderArticleGroup -> OrderArticleGroupTranslation
            fetchOrderArticleGroup.SubPath.Add(OrderArticleGroupEntity.PrefetchPathOrderArticleGroupTranslation,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: Order
            IPrefetchPathElement2 fetchOrder =
                /// FirstLevel Prefetch: Order
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrder,int.MaxValue,null,null,new SortExpression());
            IPrefetchPathElement2 fetchOrderContactingViaOrder =
                // ** Prefetch: Order -> OrderContacting
                fetchOrder.SubPath.Add(OrderEntity.PrefetchPathOrderContacting,int.MaxValue,null,null,new SortExpression());

            IPrefetchPathElement2 fetchOrderContactingAddress =
                // *** Prefetch: Order -> OrderContacting -> OrderContactingAddress 
                fetchOrderContactingViaOrder.SubPath.Add(OrderContactingEntity.PrefetchPathOrderContactingAddress,int.MaxValue,null,null,new SortExpression());
            IPrefetchPathElement2 fetchOrderContactingAddressViaOrder =
                // **** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress    
            fetchOrderContactingAddress.SubPath.Add(OrderContactingAddressEntity.PrefetchPathOrderAddress,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderEmail 
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderEmail,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderTelecommunication 
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderTelecommunication,int.MaxValue,null,null,new SortExpression());
            // ***** Prefetch: Order -> OrderContacting -> OrderContactingAddress -> OrderAddress -> OrderAddressAssignment 
            fetchOrderContactingAddressViaOrder.SubPath.Add(OrderAddressEntity.PrefetchPathOrderAddressAssignment,int.MaxValue,null,null,new SortExpression());

            /// FirstLevel Prefetch: OrderArticleTranslation
            prefetchPath.Add(OrderArticleEntity.PrefetchPathOrderArticleTranslation,int.MaxValue,null,null,new SortExpression());

            // * Prefetch: OrderPrintSubstrate
            IPrefetchPathElement2 fetchOrderPrintSubstrate =
                /// FirstLevel Prefetch: OrderPrintSubstrate
            prefetchPath.Add(OrderArticleTypePaperPrintEntity.PrefetchPathOrderPrintSubstrate,int.MaxValue,null,null,new SortExpression());

            // ** Prefetch: OrderPrintSubstrate -> OrderPrintSubstrateTranslation
            fetchOrderPrintSubstrate.SubPath.Add(OrderPrintSubstrateEntity.PrefetchPathOrderPrintSubstrateTranslation,int.MaxValue,null,null,new SortExpression());

            using(DataAccessAdapter adapter = new DataAccessAdapter()) {
                adapter.ParameterisedPrefetchPathThreshold = 100;
                adapter.FetchEntityCollection(_OrderArticles,bucket,int.MaxValue,sorter,prefetchPath);
            }
            return _OrderArticles;
        }

Regards, André

"Internal error of the query processor", if my rusty german has it right ?

Hmm. that's pretty serious. Do you have all the service packs of the sqlserver version installed? Also, if you run the query (check the query sql via DQE tracing) in query analyzer, does it throw the error there as well?

Hi Otis,

got all ServicePacks installed. Is there a way to get the query with all parameters rendered? You can possibly understand that I don't want to puzzle the above query together by hand ;-) Got tracing on but it displays a parametrized query. Regards,

André

Check out the blog at http://cs.rthand.com/blogs/blog_with_righthand/pages/Implementing-more-useful-tracing-for-LLBLGenPro-2.0.aspx

for an implementation for SQL server that does just that.

Or use Sql profiler to capture the query

Thanks for the answers. I used Miha's approach to get the query rendered. I still got the same error. Rendered query is attached...

By now I found a workaround...

In the broken query I fetch OrderArticles and thier superior Orders, because I want to work with OrderArticles. The second, working approach fetches Orders and their related OrderArticles. When I got the Orders I iterate through ever OrderArticle in every Order and collect them in a list. The thing that puzzles me is that the second approach works, but the first doesn't. Got any clue?

Regards, André

Execuse me, that's a lot of code to inspect. Would you please simplify it to a couple of lines?

Thanks.

Hi Walaa,

simplifying the code was what I was actually trying to achieve before posting that bunch here. :-)

I'm sorry to say, but I wasn't able to reproduce the above explained behaviour after removing some prefetches. The problem is no more urgent as I managed to use a workaround, needless to say I understand that you want to prevent others to run into the same thing.

If I can help in other ways, I'm willing to do what I can.

Regards, André

If you run the query directly inside query analyzer do you get the same error?

Yes, got exactly the same error.

Regards, André

Jazz wrote:

Yes, got exactly the same error.

Regards, André

Then it's something internally in SqlServer, as the query is IMHO a query which should work so I don't think I can do something about it, sorry.

You could call PSS (microsofts personal support services) to get this fixed for you by them. As it's an internal error, it's a bug and they fix bugs for free. You then get an engineer assigned to you who will fix it (or perhaps there's already a hotfix available for this).