LLBLGen v2.5 Final (February 4th, 2008 ) Runtime Version: 2.5.08.0228 Compiling for C# Adapter Pattern targeting .NET 3 Running off SQL Server 2005

Getting the below exception in production but not when executing locally when making adapter calls from a WCF Service. In development, the WCF Service runs under ASP.NET Development Server, in Production, on a remote machine under IIS. The web.configs for the dev and production WCF Services have the same settings as far as connection strings and LLBLGen settings go.

For testing purposes both services are calling the same Database (Production) with the same connection string. The schema of the Production Database is identical to that of the Development Database.

Under development, when fetching data for a m:n prefetch path this query is generated:

exec sp_executesql N'SELECT DISTINCT [LPA_P1].[Id] AS [Id0], [dbo].[Price].[Id] AS [Id1] FROM (( [dbo].[PriceGroup] [LPA_P1]  INNER JOIN [dbo].[PriceGroupPrice] [LPA_P2]  ON  [LPA_P1].[Id]=[LPA_P2].[PriceGroupId]) INNER JOIN [dbo].[Price]  ON [dbo].[Price].[Id]=[LPA_P2].[PriceId]) WHERE ( ( [LPA_P2].[PriceGroupId] IN (SELECT [dbo].[PriceGroup].[Id] FROM ( [dbo].[ItemGroup]  INNER JOIN [dbo].[PriceGroup]  ON  [dbo].[ItemGroup].[Id]=[dbo].[PriceGroup].[ItemGroupId]) WHERE ( ( ( [dbo].[PriceGroup].[IsActive] = @IsActive1 AND [dbo].[PriceGroup].[BaanCompanyId] = @BaanCompanyId2 AND [dbo].[ItemGroup].[ManagerId] = @ManagerId3))))))',N'@IsActive1 
bit,@BaanCompanyId2 int,@ManagerId3 int',@IsActive1=1,@BaanCompanyId2=1,@ManagerId3=21

Which is all as it should be.

However, in production a very slightly different query is generated for the same m:n fetch:

exec sp_executesql N'SELECT DISTINCT [dbo].[PriceGroup].[Id] AS [Id0], [dbo].[Price].[Id] AS [Id1] FROM (( [dbo].[PriceGroup] [LPA_P1]  
INNER JOIN [dbo].[PriceGroupPrice] [LPA_P2]  ON  [LPA_P1].[Id]=[LPA_P2].[PriceGroupId]) INNER JOIN [dbo].[Price]  ON  
[dbo].[Price].[Id]=[LPA_P2].[PriceId]) WHERE ( ( [LPA_P2].[PriceGroupId] IN (SELECT [dbo].[PriceGroup].[Id] FROM ( [dbo].[ItemGroup]  
INNER JOIN [dbo].[PriceGroup]  ON  [dbo].[ItemGroup].[Id]=[dbo].[PriceGroup].[ItemGroupId]) WHERE ( ( ( [dbo].[PriceGroup].[IsActive] = 
@IsActive1 AND [dbo].[PriceGroup].[BaanCompanyId] = @BaanCompanyId2 AND [dbo].[ItemGroup].[ManagerId] = @ManagerId3))))))',N'@IsActive1 
bit,@BaanCompanyId2 int,@ManagerId3 int',@IsActive1=1,@BaanCompanyId2=1,@ManagerId3=2

Notice that in the query generated on production the [LPA_P1] alias for the Price Group table is not used, as it should be, hence we get the following exception:

Event Type: Error Event Source: Univar Q Error Logging Event Category: None Event ID: 100 Date: 28/03/2008 Time: 16:23:30 User: N/A Computer: UKI-PRICING01 Description: Timestamp: 28/03/2008 16:23:30 Message: HandlingInstanceID: d4903255-d8e6-42d4-8066-dc0b7c5da3f6

An exception of type 'System.Data.SqlClient.SqlException' occurred and was caught.

03/28/2008 16:23:30 Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : The multi-part identifier "UnivarQ.dbo.PriceGroup.Id" could not be bound. Source : .Net SqlClient Data Provider Help link : Errors : System.Data.SqlClient.SqlErrorCollection Class : 16 LineNumber : 1 Number : 4104 Procedure : Server : 10.2.50.101\UKI_SQL2_DEV2 State : 1 ErrorCode : -2146232060 Data : System.Collections.ListDictionaryInternal TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean) Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, Int64 maxNumberOfItemsToReturn, IEntityCollection2 rootEntities) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields) at UnivarQ.DataGateway.PriceGroupWorker.SearchForPriceGroups(String itemGroupName, String priceGroupName, String skuDesc, String skuCode, Nullable1 managerId, Nullable1 baanCompanyId, Nullable1 priceUnitId, Boolean includePrices, Boolean useExactPhrase) in C:\Projects\UnivarQ\Current\Product\Production\Facade\DataGateway\PriceGroupWorker.cs:line 220 at UnivarQ.QService.UnivarQService.SearchForPriceGroups(String itemGroupName, String priceGroupName, String skuDesc, String skuCode, Nullable1 managerId, Nullable1 baanCompanyId, Nullable1 priceUnitId, Boolean includePrices, Boolean useExactPhrase) in C:\Projects\UnivarQ\Current\Product\Production\Services\QService\UnivarQService.cs:line 808 at SyncInvokeSearchForPriceGroups(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Additional Info:

MachineName : UKI-PRICING01 TimeStamp : 28/03/2008 16:23:30 FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a AppDomainName : /LM/W3SVC/1/Root/UnivarQServices-1-128511939806115858 ThreadIdentity : WindowsIdentity : NT AUTHORITY\NETWORK SERVICE

Category: General Priority: 0 EventId: 100 Severity: Error Title:Exception Management Application Exception Machine: UKI-PRICING01 Application Domain: /LM/W3SVC/1/Root/UnivarQServices-1-128511939806115858 Process Id: 16372 Process Name: c:\windows\system32\inetsrv\w3wp.exe Win32 Thread Id: 15388 Thread Name: Extended Properties: HelpLink.ProdName - Microsoft SQL Server HelpLink.ProdVer - 09.00.2047 HelpLink.EvtSrc - MSSQLServer HelpLink.EvtID - 4104 HelpLink.LinkId - 20476

The same problem occurs on other m:n fetches carried out when m:n prefetch paths feature in the code.

We're really pulling our hair out over this one, please help. We've tried reverting to 2.5.07.1129 runtime library versions, as the problem started after we moved to using the above version of LLBLGen, but no luck, same exception.

Also, we've tried using it with and without catalog names specified, makes no difference.

If you get a different query on another machine, you have a different runtime version there or something else is different.

Anyway, we applied some fixes to the runtime library for m:n relation fixes for inheritance entities on february 28th. This requires new code being generated with the latest templates.

Please do the following: - be sure you use the latest templates. If you're not using templates released on february 28th, you have wrong aliases setup in the prefetch path code - be sure you use a runtime library released on or after february 28th.

So please regenerate teh code using the latest runtime lib and templates and deploy BOTH on the production server. If you just deploy the runtime lib update on the production server, you'll likely run into the bug we fixed on february 28th, as it still has the wrong aliases.

So it might be you did all this except deploying an updated compiled version of the generated code as it works in the development environment.

Well, I did I diskwide search for llblgen assemblies and found some old 2.5 runtime libraries in the GAC. It looks like the web service was using those in preference to the ones in its /bin directory, producing a mismatch between runtime libraries and generated code that resulted in the bad query.

Took a while to figure out though. All the while I was assuming that if the correct version was in the /bin directory of the service, that was the version that would be used at execution.