ERROR AFTER UPGRADE FROM 16.0.1 TO 16.0.2 - SQL SERVER DB

[Diego Chamarelli]

Hello everyone,

First things first, that´s our  DEV enviroment:
SQL SERVER 2019 - V 15.0.4223.1
Windows Server 2016 Standard Version 10.0.14393 Build 14393
Clarity 16.0.2

Now, the problem:

We are facing some issues after upgraded from 16.0.1 to 16.0.2

When we try to xog in any project instance the error below it´s happening:

<Exception><![CDATA[SQL Exception with error code : 4104 message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The multi-part identifier "t0.id" could not be bound.]]></Exception>
</>

This is the XOG we are trying to execute:



This is the result of the xog we are trying to execute:


This only happen when we use XOG , it´s not happening when we use the interface (old or new UX) to update or create projects.

This is not happening when we xog in custom objects or when we xog out any instance of custom or native objects.

Our QA and Production enviroments still running PPM 16.0.1 and if we try to execute the same xog, it works fine.

Any help will be immensely appreciated.

Best regards from Brasil.


------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

Hello there,

Can you turn on a trace and see what the query is trying to do? The t0.id looks like a table alias (t0) and a column called "id" on it.

Paul

[Diego Chamarelli]

Hi Paul!

Thanks for the answer!

I thought that Trace would just log what was happening during the interface navigation.

can it trace the xog too ?

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

Looks like my last post didn't post.....

Turn on tracing for the user in Administration > Resources then turn on tracing for XOG in the NSA:


Then run the XOG as the user just selected for trace and the statements should appear in app-trace.xml in the logs folder.

I suspect pasting the XML made my last post disappear.

Just a quick one though, what's the compatibility level set to in SQL Server? The manual says that for SQL Server 2019 it should be 150.

[Paul Schofield]

Hello again,

There are two places you need to set this up. First in Administration > Resources so that tracing is enabled for the user that's carrying out the XOG. Second you need to enabled XOG tracing in the NSA:



Then when you run the XOG you should see things like this in app-trace.xml:

<persistence id="xog.obsLookup" elapsed="0.000" elapsedSincePriorNode="1.000" elapsedAfterLastNode="0.000" start="2:35:26:943" finish="2:35:26:943" memoryDelta="0k">
<statementSet id="xog.obsLookup" location="xog_obs.xml" elapsedSincePriorNode="0.000" start="2:35:26:943"/>
<statement elapsed="0.000" elapsedSincePriorNode="1,653,053,726,944.000" elapsedAfterLastNode="0.000" start="2:35:26:943" finish="2:35:26:943" memoryDelta="0k">
<execute id="STMT-410154296" elapsed="0.000" elapsedSincePriorNode="0.000" start="2:35:26:943" finish="2:35:26:943" memoryDelta="0k">
<![CDATA[SELECT u.type_id,
u.id unit_id,
u.name unit_name,
t.name type_name,
t.unique_name
FROM prj_obs_associations a, prj_obs_units u, prj_obs_types t
WHERE a.unit_id = u.id
AND a.record_id = ?
AND a.table_name = ?
AND u.type_id = t.id
order by t.unique_name, u.name
setLong(): 1, 5001000
setObject(): 2, cop_prj_statusrpt
]]>
</execute>
</statement>
</persistence>
<persistence id="xog.obsSecurityLookup" elapsed="1.000" elapsedSincePriorNode="1.000" elapsedAfterLastNode="0.000" start="2:35:26:944" finish="2:35:26:945" memoryDelta="0k">
<statementSet id="xog.obsSecurityLookup" location="xog_security.xml" elapsedSincePriorNode="0.000" start="2:35:26:944"/>
<statement elapsed="1.000" elapsedSincePriorNode="1,653,053,726,945.000" elapsedAfterLastNode="1.000" start="2:35:26:944" finish="2:35:26:945" memoryDelta="0k">
<execute id="STMT-819313417" elapsed="0.000" elapsedSincePriorNode="0.000" start="2:35:26:944" finish="2:35:26:944" memoryDelta="0k">
<![CDATA[SELECT a.principal_type right_mode,
r.group_code right_code,
t.unique_name type_unique_name,
u.id unit_id,
u.name unit_name,
t.name type_name
FROM cmn_sec_assgnd_right a,
prj_obs_units u,
prj_obs_types t,
cmn_sec_groups r
WHERE a.principal_id = u.id
AND a.instance_id = ?
AND a.instance_type = ?
AND a.right_id in (select id from cmn_sec_groups where right_type = ?)
and a.right_id = r.id
AND u.type_id = t.id
order by unit_name, type_unique_name, right_code
setLong(): 1, 5001000
setObject(): 2, INSTANCE
setObject(): 3, cop_prj_statusrpt
]]>
</execute>
</statement>
</persistence>

With luck the statement that contains the t0.id reference will show up.

Just a quick one though, what's the compatibility level in SQL Server set to?

[Diego Chamarelli]

Ok, thanks Paul!

i´ll try and as soon as i can, i´m gonna give you some feedback

Compatibility level 
Niku - 140
Ppm_Dwh - 140 
Jaspersoft - 150



------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Diego Chamarelli]

Hello again Paul!

That´s it!

After turn on the trace, 4 queries with this problem appears in the app,trace log.

I made the changes and executed the queries and all them returned the expected results.

There is a way to definitively solve this ?

I have 2 more servers to upgrade and if there is a way to correct this, i would like to know.

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

Looks like my previous post didn't post again.

When you say that worked, what did you change? Did upping the compatibility level fix things?

Paul

[Diego Chamarelli]

Hi Paul!
Thanks again for your interest and help.

I made 2 changes:
First was ​change "t0" in the query for "s0" and run this query and it was executed ok. 
Looks like that "s0" alias for the table "inv_investments" was changed for "t0" but there is no tabla using this alias. 
But i made this just to try the query.

Second was the change on the SQL SERVER compatibility level for 150 on my QA environment. After this change de upgrade ran smoothly and there were no errors.

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

Hello again,

Looks like we're making progress.

When you said you made the changes, what did you change? Did setting the compatibility level to 150 make things work or did you adjust the queries in SQL Server Management Studio and run them manually?

If it's the former then these command will do the trick in the other environments:

USE MASTER
EXEC SP_DBCMPTLEVEL niku, 150
EXEC SP_DBCMPTLEVEL ppm_dwh, 150

See page 1826 of the 16.0.2 documentation for more details.

Paul

[Diego Chamarelli]

Hi again Paul!

Yes, i made the 2 suggested changes.
1 - Altered the queries and ran them in SQL STUDIO in my DEV environment.
2- Change the compatibility level on QA environment. 

But now what can i do to solve the problem in my DEV environment ? Change the compatibility level ?

What are your suggestions ?

The error still standing.

Thanks for the help.



------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

The queries shouldn't need to change if the compatibility level is correct. I was hoping that was the only change needed. If you made the switch to 150 in DEV already the changes should be there already. Did the XOG work afterwards?

Paul

[Paul Schofield]

Apologies, just saw the t0/s0 message. Looks like a bug :(

[Diego Chamarelli]

Hi Paul!

We made the compatibility level change in our DEV environment, but the error still.

All the Clarity schemas are now setted to compatibility level 150.

Do you have any suggestions ?

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Diego Chamarelli]

Hi Paul!

Ok, that´s my next move.

I´ve already asked to the DB team to apply the change of the compatitbility level.

So i´m gonna wait for their answer and as soon as possible i´ll give this feedback.

And once again thanks a lot for all your help, was really important.

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Paul Schofield]

Sorry it took this long to uncover what looks like a coding error, I wish youd luck in encouraging Broadcom to fix things. The compatibility level isn't important in this case, but setting it to the correct value is always good.

Boa sorte,

Paul

[Diego Chamarelli]

Hi Paul!
Thanks for all your effort in helping me.

After a few days of upgraded our QA environment the error shows up again and i really dont know what may cause this.

We have done nothing different in this environment.

Maybe it´s the case to raise a ticket and ask Broadcom some help.

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------

[Diego Chamarelli]

Hi Paul, sorry for the late!
We raised a ticket and Broadcom support gave us a script to solve the problem.
An update was needed to solve this, anyway thanks for your help.
Best regards

------------------------------
______________________________________________
Analista de Sistemas
Ágila Tecnologia
Brasil
------------------------------