Changing the comment format in Webcon 2022 (dbo.f_clear_description error)

0

Hi Michał,

is the f_clear_desription a custom function? I can't find it in my databases.

Have you checked whether the value is converted, when an instance is saved? I don't have a similar environment, so I can't verify it.
If this the case you could use a date or alternatively you could test whether the value starts with the old or new format.

If you need to process the new comment format you could use either one as a starting point. Both approaches return each comment in an own record, so it won't match your case.
This one requires a database compatibility level for sql server 2016 (130).
https://daniels-notes.de/posts/2023/comments-to-data-table-and-pdf#sql-server-2016

For lower version you could use:
https://daniels-notes.de/posts/2023/comments-to-data-table-and-pdf#sql-server-2014-and-less

Best regards,
Daniel

Hello Daniel
I'm not sure if this is a standard feature or not. But now that it compares to other functions, after you wrote that you don't have it in your environment,
there's a chance that maybe my predecessor created it. Function use a cursor that concatenates comments into one string.
I will have to improve this function so that it extracts comments depending on the pattern.

What a coincidence, I got a request this afternoon to modify a data table which displays information about subworkflows.
The comments from the subworkflows should be displayed.

Here's the part regarding extracting the comments and concatenating them back

Best regards,
Daniel

,(select STUFF(
(

select left(Comments.Date,16)+' '+ Displayname +':' +Comment+Char(10)
from OPENJSON(WFD_Description)
with (
Date DATETIME2 '$.d',
Account varchar(200) '$.l',
Displayname varchar(200) '$.a',
Comment nvarchar(max) '$.c'
) as Comments
for xml path ('')
),1,0,'') as dummy
) as Comments
from V_WFElements

Hello Daniel
Thank you very much for your answer.
Unfortunately, I couldn't use OPEN JSON because my SQL Compatibility Level is too low.
So all newer solutions are out of my reach :/
INFO: https://kb.webcon.pl/tryb-zgodnosci-baz-danych-webcon-bps-compatibility-level-dobre-praktyki/ .

I did a quick solution the old way, for comments I use a function that checks whether it is an old or new comment and
uses the appropriate function to read it. I use it in one report so I can afford such a move.

Maybe it will be useful to someone I couldn't add the code so I added screenshot with code.