Hi Jeff,

Thanks for reaching out.

Unfortunately, Synapse SQL Serverless Pool didn't give a line number of where the error occurs:

However, I think I'm getting the error here:

LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,

            
July 25, 2022 at 5:47 pm
            
#4068026

            
            
                                                
        

ED B

"What is it that you want it to do? if it has ts_companytype = '717750001;717750002' do you want to see ''Target;Vendor'?"

Yes, yes, yes, yes .... Ed B, you understand....

Ed B

I just realised that the following additonal sample tables will be required in order for someone to help me, because if someone executes the script it will be looking...

• This reply was modified 2 years, 4 months ago by  carlton 84646. Reason: update

Ed B,

Now, that you have provided sample data, I'm really hoping someone can help out - this is a tough request

Ed B, thanks for creating the tables.

Not sure what you mean "Please correct the script so that it has the right columns with the right data"

The script is untouched...

Hi Community,

What I'm about to ask might sound a little crass, as I know members of this forum provide their time free-of-charge, however I'm a little desperate to find solution...

I really don't think it's as easy as modifying the script with CROSS APPLY - not that I would know where to make the amendment on the script

You're sample focuses on a table 'account' whereas the only tables the script focuses on are

'OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'

However, in order to make things simple, I am just referring to GlobalOptionsetMetadata....

I am essentially asking for help modifying the script to achieve the results I have set out above

Hi Johan Bijnens

I have been reading  Jeff Moden's article for over an hour, and I must say I'm struggling to understand it.

I'm also struggling getting to grips with your sample...

Hi Johan Bijnens,

Thanks for reaching out.

Can you provide a sample of where I would add your code to the script?

@Jeffrey,

It was also suggested that I shouldn't be using two different databases

Hi Frederico

I have just been informed that you're suggestion of

changing dbo to sys won't work

'EXEC [' + @EnrichedViewDatabase + '].sys.sp_executesql @'

What was actually said was:

 You can't reach across database boundaries ...

Any other thoughts?

Ahhh ... ok

I changed as follows: 'EXEC [' + @EnrichedViewDatabase + '].sys.sp_executesql @'

But I'm now getting the error:

Reference to database and/or server name in 'testdb.sys.sp_executesql' is not supported in this...