Hi Monique de Vos,
Your problem is as follows:
As your tables are extensions, external sql tables, and you do

SELECT {Table}. *
FROM {Table}
FOR JSON AUTO

It works normally because OutSystems already knows the database and the tables it should access.
but when you try to enter a parameter in your query and you pass only "{TABLE}" text, it can not interpret.

As you saw the error, he hopes that in the front comes a owner from his bank. What you can do is the following: On your test everything happens ok, run it again, go to the "Executed SQL" tab and see how the full name of your table is being interpreted by OutSystems. Then take that name and test it as an input parameter again and see if it works.

i'm trying to help you testing it, and i have this error:

I think you need to put dbo.@Physical_table_name to access your table.

Such as:

where Physical_Table_Name is something like "OSUSR_OXZ_TABLE2"

I hope it will helps you!

Thanks and Best Regards,

Nuno Pereira

Hi Monique de Vos,

You can achieve that like below.

Hope this helps

Sravan

Hi all,

Thanks so much for taking the time to reply!

@Bernardo, OutSystems has been able to parse the {Table} test value just fine; after submitting {Table} as a test value, the correct string ([DATABASE].[dbo].[Table]) appeared in the Executed SQL tab. Putting an OutSystems entity between the brackets - like {Entity} - worked fine, too. 

@Nuno, thanks for helping me on the right path! After looking at your post and my stored procedure call (calling [dbo].[StoredProcedure]), I changed my query to [dbo].[@Table]. It still didn't work when I tried to test it with the Test button, but running it 'for real' worked!

@Sravan, I'm sorry I haven't got to testing your solution, after I got it running I had to focus on another issue. Thanks for the help!

TL;DR, using the Test button in the Advanced SQL widget produced errors when trying to call an external database with a variable as the table name, while running the same code/module (in the browser) worked fine.

Hi Sravan Vanteru,

Your response is really helpful. Thanks.

Piggy backing off of this because I have spent hours scouring other posts and still can't seem to find an answer. I'm trying to do a truly dynamic SQL statement as a back-end IT Admin tool that will allow production DBA's to run SQL statements in the instance where a User can't solve their problem directly.

Here is the setup in the Advanced SQL node : 

In "Test SQL" in Service Studio I am experiencing an "Invalid Object Name" response that others here were seeing so I set up a screen to pass the parameter directly from the browser

 

This is still resulting in an error in ServiceCenter regardless of the format input into the table syntax (I've tried 'Partner', '{Partner}', '[dbo].{Partner}', '[dbo].[Partner]')

I also tried splitting this into 3 separate inputs and it's only the dynamic table name that's failing

@Nick MATES

I was just wrestling with a similar challenge and got it working by assigning all of my dynamic variables in a prior step in the process and then inserting those into a single "LocalSQL" variable which is executed in my advanced SQL step.  This has the advantage of skipping the step of querying the system Entity tables for the physical table names and using that in the query since the I can just use the simple entity names as variables in my LocalSQL variable.  Just make sure you set SQL Query Parameter to Expand Inline = 'Yes'.

Hope this helps.

Advanced SQL:

I can't for the life of me figure out what we are doing different. In the 5 years I've used OutSystems I've never been at more of a loss. I'm attempting to do an update statement (generally) instead of an insert but I still wasn't getting my desired result so instead I tried to mirror your exact logic. I'm still getting a "Invalid object name 'dbo.State'. " response even with the below : 

1. Setting up the LocalSQL variable

2. Advanced SQL with LocalSQL being the input parameter (with Expand Inline = Yes")

Running this test in the AdvancedNode as a test with @Table being a new expanded inline parameter set to "{State}" results in no error.

Closing the loop on this because this has been resolved from a functional perspective. 

I believe this boils down to the Advanced SQL node pointing to a different DB connection (all of these entities are stored in external databases mapped from extension modules) than expected with no way for us to specify a DB Connection we would like to use for an Advanced SQL node. 

We were able to solve this by repurposing the Execute Server Action from SQL Sandbox forge component (huge shoutout to our TSM & CSM at OutSystems for this idea) and it's now working like a charm in our custom application that includes 2-person authorization on these SQL statements.