SQL Server Agent Job "Process Full in SSAS" throws error without details

Mentler, André 0 Reputation points
2024-06-14T07:30:56.9033333+00:00

We have a Tabular Model deployed in SSAS that worked until three days ago but doesnt anymore. It throws errors when calling full process from a sql server agent job.
This is basically the Error Message: Column '' in Table '' contains a duplicate value '1'
We had data changes but no structural changes to the model or the database. We have a few dozen tables and hundreds of relationships. Its very problematic to go on solving this error with so little information.

Our Error Messages are always lacking details but until now we were able to fix them. The current error gives us neither table nor column name.

I think the reason that there are no details is because its a calculated table that is being used in a relationship and in that case the ssas has problems with the logging.

-> What complicates this problem even further is that the "Process Full" command works when called directly from ssms but not when Process Full is being called from the SQL Server Agent Job!

Here is the full error message from sql server agent:

Date 13.06.2024 14:49:19

Log SQL Server Agent (Current - 13.06.2024 14:49:00)

Message

[136] Job SSAS DB Process reported: <return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Warning WarningCode="1094320140" Description="Column '' in Table '' contains a duplicate value '1' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table." Source="Microsoft Analysis Services" HelpFile=""><Location xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl410="http://schemas.microsoft.com/analysisservices/2012/engine/410" xmlns:ddl410_410="http://schemas.microsoft.com/analysisservices/2012/engine/410/410" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:ddl600="http://schemas.microsoft.com/analysisservices/2013/engine/600" xmlns:ddl600_600="http://schemas.microsoft.com/analysisservices/2013/engine/600/600" xmlns:ddl700="http://schemas.microsoft.com/analysisservices/2018/engine/700" xmlns:ddl700_700="http://schemas.microsoft.com/analysisservices/2018/engine/700/700"><Start><Line>0

This is the SQL Server Agent Job definition

{

"refresh": {

   "type": "full",

   "objects": [

     {

       "database": "BAG"

     }

]

}

}

We have SQL Server 2019 (15.0.4322.2) and Analysis Service 15.0.35.41

I got the errormessage from ssms->sql server agent->error logs

Are there other sources or options for more logging details?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,201 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,258 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 42,761 Reputation points
    2024-06-14T07:44:26.23+00:00

    or options for more logging details?

    When you process the cube manuall within SSMS, then go on "Change Settings" => "Dim error"

    Here you have to option to ignore errors and can define an error logging file for the details.

    User's image

  2. Mentler, André 0 Reputation points
    2024-06-14T14:21:14.55+00:00

    hey, thank you for your input olaf.

    this doesnt work, because the ui that you suggested isnt available. perhaps we are using another technology. we are deploying a tabular model in our analysis service so the "process database" dialog looks like this:

    grafik there is no option to change the error logging.

    also the error only is thrown when we run the full process with our sql server agent job (i wrote the definition in my first post).

    also there is a development. we found a more specific error message! we let the whole "process database - full process" run and activated the sql profiler in the background. close to the end of the process when it failed we got a more specific error message that gave us the table name. it looks like this:

    exec sp_executesql N'EXECUTE [msdb].[dbo].[sp_agent_log_job_history] @job_id = @P1, @is_system = @P2, @step_id = @P3, @sql_message_id = @P4, @sql_severity = @P5, @run_status = @P6, @run_date = @P7, @run_time = @P8, @run_duration = @P9, @operator_id_emailed = @P10, @operator_id_netsent = @P11, @operator_id_paged = @P12, @retries_attempted = @P13, @session_id

    = @P14, @message = @P15',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 int,@P15 nvarchar(4000)','20907BA2-85A0-4012-A75C-C2AED59F784A',0,1,0,0,0,20240614,151139,1344,0,0,0,0,3,N'Executed as user: NT Service\SQLSERVERAGENT. <return

    xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"><Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception"><Warning WarningCode="1094320140" Description="Column '''' in Table '''' contains a duplicate value ''1'' and this is not allowed for columns on the one side of a many-to-one relationship

    or for columns that are used as the primary key of a table." Source="Microsoft Analysis Services" HelpFile=""><Location xmlns="

    http://schemas.microsoft.com/analysisservices/2003/engine"

    xmlns:ddl2="

    http://schemas.microsoft.com/analysisservices/2003/engine/2"

    xmlns:ddl2_2="

    http://schemas.microsoft.com/analysisservices/2003/engine/2/2"

    xmlns:ddl100="

    http://schemas.microsoft.com/analysisservices/2008/engine/100"

    xmlns:ddl100_100="

    http://schemas.microsoft.com/analysisservices/2008/engine/100/100"

    xmlns:ddl200="

    http://schemas.microsoft.com/analysisservices/2010/engine/200"

    xmlns:ddl200_200="

    http://schemas.microsoft.com/analysisservices/2010/engine/200/200"

    xmlns:ddl300="

    http://schemas.microsoft.com/analysisservices/2011/engine/300"

    xmlns:ddl300_300="

    http://schemas.microsoft.com/analysisservices/2011/engine/300/300"

    xmlns:ddl400="

    http://schemas.microsoft.com/analysisservices/2012/engine/400"

    xmlns:ddl400_400="

    http://schemas.microsoft.com/analysisservices/2012/engine/400/400"

    xmlns:ddl410="

    http://schemas.microsoft.com/analysisservices/2012/engine/410"

    xmlns:ddl410_410="

    http://schemas.microsoft.com/analysisservices/2012/engine/410/410"

    xmlns:ddl500="

    http://schemas.microsoft.com/analysisservices/2013/engine/500"

    xmlns:ddl500_500="

    http://schemas.microsoft.com/analysisservices/2013/engine/500/500"

    xmlns:ddl600="

    http://schemas.microsoft.com/analysisservices/2013/engine/600"

    xmlns:ddl600_600="

    http://schemas.microsoft.com/analysisservices/2013/engine/600/600"

    xmlns:ddl700="

    http://schemas.microsoft.com/analysisservices/2018/engine/700"

    xmlns:ddl700_700="

    http://schemas.microsoft.com/analysisservices/2018/engine/700/700"><Start><Line>0</Line><Column>0</Column></Start><End><Line>0</Line><Column>0</Column></End><LineOffset>0</LineOffset><TextLength>0</TextLength><ddl200:SourceObject><ddl500_500:TableName>dim_all_editors</ddl500_500:TableName></ddl200:SourceObject><ddl200:RowNumber>275</ddl200:RowNumber></Location></Warning></Messages></root></return>.

    The step failed.'

    this is one of the still bad but at least more specific messages. "still bad" because there isnt an id column with a duplicate key of 1. so i think its a calculated table BASED on dim_all_editors that is causing the problem and somewhere there is a calculated column that generates a 1 two times and then throws an error.

    0 comments