Error Message on certain Servers

Sep 20, 2012 at 10:48 AM

Hi,

First, it's a great solution and I have already deployed it successfully on several servers.

It seems though that on some servers I'm getting below error message. From what I can tell, the results don't conform to what your solution expects. I don't know though how I could tell at what Catalog/Database/Cube it gets stuck and throws this error message.

Is there any way you can tell by looking at the error message?

**********************************************

Date  20.09.2012 11:39:46
Log  Job History (SSASMetaQuery)

Step ID  1
Server  Server\Instance
Job Name  SSASMetaQuery
Step Name  Run Package
Duration  00:37:16
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: Domain\User. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:39:46  Error: 2012-09-20 12:16:59.96     Code: 0x00000001     Source: Load AMO DB      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.  The statement has been terminated.     at System.Data.SqlClient.SqlConnection.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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()     at ST_d283575e8ba349dd97aa9f085feeaa38.csproj.ScriptMain.Main()     --- End of inner exception stack trace ---     at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)     at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)     at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)     at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)     at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:39:46  Finished: 12:16:59  Elapsed:  2235.07 seconds.  The package execution failed.  The step failed.

Coordinator
Sep 24, 2012 at 2:13 PM

Hello Rw561,

 Right now from the error message i cannot figure out where the error is caused. But i can recommend a way to identify which place it is errorring out. Please run a profiler against the database in which the AMO tables are created. Basically the c# codes calls the stored prcoedures and inserts the respective data into the table. By the trace you should be able to figure out which procedure it errors and please post the details of the procedure so that i can work further on this to release a patch.

On a side note basically this error is due to it returns multiple value for a inline subquery. Once you figure out the proedure which fails , please take the inline subqueries and run it with the parameters passed from the prcoedure so that we can understand which inline query returns multiple value. That will be ease the work on the fix for this issue.

Best Regards

Sorna

 

Sep 24, 2012 at 2:18 PM

Hello Sorna,

Thanks for the reply.

Which profiler counters would you recommend?

Coordinator
Sep 25, 2012 at 6:50 AM

Hello RW561,

  Please add the profiler events "RPC:Stored procedure start" , "RPC :Stored Proceudure complete" , "SQL Statment Start" , "SQL statement complete", "SQL batch start" & "SQL Batch complete".

Best Regards

Sorna

Sep 25, 2012 at 9:32 AM

Hi Sorna,

I think I might have found the 'bug' although I'm not entirely sure it is.

What came to my attention is that the processing always stops here

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">          
<DatabaseID>Catalog</DatabaseID>          
<CubeID>CubeIDorName</CubeID>          
<MeasureGroupID>Vf DM BEW Plus Abgang ALOASU AS2K8</MeasureGroupID>          
          <PartitionID>Vf DM BEW Plus Abgang ALOASU AS2K8</PartitionID>          
<ObjectExpansion>ExpandObject</ObjectExpansion>        
</RestrictionList>

As you can see, the MeasureGroupID and the PartitionID are the same.

Could that be the cause of the stop in processing the package?

Coordinator
Sep 29, 2012 at 7:47 AM

Hello Rw5611,

First fo all apologies for my late reply as i was on personal work without any internet accesss.

I dont think this is the cause of the problem.

As we discussed already could you please run the profiler and let me know the results. That will help us to pin down the issue.

 

 

Oct 1, 2012 at 6:43 AM

Hi Sorna,

Thanks for the reply.

I think I found the part which makes the package fail in my environment.

SELECT AD.AggregationDesignId, *

   FROM AggregationDesign AD

      JOIN MeasureGroup MG

      ON AD.MeasureGroupId = MG.MeasureGroupId

      JOIN Cube cu

      ON MG.CubeId = CU.CubeId

   WHERE cu.CubeId = 274

     AND AD.[ID] = N'AggregationDesign'

This code is part of the 'sp_AMODBPartition' Stored Procedure. I already substituted @CubeID and @AggregationDesignID. Unfortunately sometimes the developers give all @AggregationDesignIDs the same Name, which in turn results in several Aggregations being returned, as the assumption is that @AggregationDesignID have a distinct name.

Is there any way you could rewrite the code so that you could catch that, not only for @AggregationDesignID but for @MeasureGroupIDs as well?

Thanks lots and sorry about this situation.

Coordinator
Oct 6, 2012 at 4:08 AM

Hello Rw5611,

  I am looking into this and let me think about a solution for this. But please expect some delay on this fix as i am very busy with my perosnal work.

Best Regards

Sorna