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

Question

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?

Answer 1

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.

Answer 2

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:

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.