SQL Table Editor - Query - External DataBase

darpan13
Contributor

SOURCE: ONESTREAM CHAMPIONS

Hi Team,

do we need to put some specific settings to connect the External DataBase… when we are connecting it via SQL Table Editor.

i am getting the below mentioned error ( where as the same connection works via Data Adaptor )

darpan13_0-1642017867259.png

 

Subledgers _ External.png977×467 21.7 KB

 

Regards and Thanks

Darpan Bhansali

10 REPLIES 10

TerryLedet
New Contributor III

You may have already done this; however, if you have not… set the Database Location to External and then select the appropriate External Database Connection.

image
image.png700×162 3.67 KB

Hi Terry,

i tried the above but it’s not working… i have OS 5.3 as of now.

it shows me error (mentioned above ) when i connect via above.

did you try connecting it on your OS , if yes, please share any screenshot for reference.

Regards and Thanks

Darpan Bhansali

@darpan.bhansali From the error message, may you select the ellipsis box at the bottom left and copy the error text from that to this thread for review?

Hi Michael,

Please find it attached here 

 

darpan13_0-1642018101638.png

 

Regards and Thanks

Hi Darpan,
Have you tried your query in a data adaptor first? What is the error?
Is the DB on the same server? If not, you will need to add a connection using the DB manager but it is only done at server level. You can not do this as an admin from the GUI.

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

Hi Nicolas,

Yeah the same connection works with Data Adaptor.

As per discussions with onestream team, with OS 5.3 ,

It is possible to use the SQL Table Editor with an external connection if the schema is not required to be included in the table name property. If it is required, then a schema property is available in the SQL Table Editor component from 6.1

So i think we have a dead end here.

Regards and Thanks

Darpan Bhansali.

TerryLedet
New Contributor III

Darpan

Suggestion based on the thread… Create an additional external DB connection as ODBC or OLE DB based on the current connection. For example, if the external DB connection is currently, ODBC create OLE DB and if ODBC create OLE DB. I suggest this because at one client the external connection stopped working one day unexpectedly and switching the connection type resolved the error. Also, if you execute SELECT * FROM SYS.SYSOBJECTS with the current connection do you get results.

Terry

Understood Terry,

Let me speak to One Stream team on the same , we do not have access to create New External DB Connection.

but as per my understanding creating new DB connection and changing the Default Schema should resolve this issue.

this issue is not per OLE DB or ODBC.

let me try both the options and confirm.

Regards

Darpan

scottr
New Contributor III

In version 6.2 it works. these are my settings.

 

image

Yeah so that’s the catch… Schema name. table name doesn’t works in sQL Table Editor.