SQL Table Editor connecting to External Database not working

[Sridhar_M]

Hi,

Can anyone explain why External connection is not working in SQL Table Editor ? The same connection is working for Data Adapter.

Screenshot for Data Adapter and it’s working.

Screenshot for SQL Table Editor and it’s not working.

I tried giving the schema name as well but no luck. We are on version 6.4.

Thanks
Sri

[NicolasArgente]

Hi there,

Did you try to put the column format like this ?

[Sridhar_M]

Hi Nicolas, 

column format is not required to test the connection. So I didn't include. 

[Sai_Maganti]

I think the problem is the SQL Table Editor doesn't know what the database is but in your SQL Query for the data adapter you're using the fully qualified name ([dbname.schemaname.tablename]) assuming OneStream_Automation is your database name. It might be the db connection is set up only to the server. Check with your OneStream support and they will help you out.

[NicolasArgente]

OR check your SQL statement directly into a data adaptor 

[ckattookaran]

Keep in mind that the editor and data adapter works differently. Also, is the user that you are using in the external connection does that user got write access to the tables?

[Sridhar_M]

Yes, that user has write access. 

[ckattookaran]

What is the error you are getting then? Prior releases won't let you write to a different schema. But I guess you are not on that version.

[Sridhar_M]

I am just trying a connection from Table editor and getting below error. I am on version 6.4. 

[ckattookaran]

Your data adapter does have aliasing on the SELECT statement. What if you remove the alias as do SELECT * from error_codes? Does that work?

[Sridhar_M]

No, I am getting below error message if I don't use fully qualified name (ailas) in Data Adapter. 

[ckattookaran]

There we go, so now use that in the schema of SQL editor and see if that works. 

[Sridhar_M]

I already tried with fully qualified name in SQL Editor but no luck. I am getting same error. 

[Sai_Maganti]

Schema Name will be dbo and NOT OneStream_Automation which is the database/catalog name. If your data adapter works with the fully qualified name then your connection must me missing the initial catalog parameter which is OneStream_Automation and hence the SQL table editor fails.

Your data adapter will not work without qualifying with "OneStream_Automation" which explains this is the database/catalog name.

Your query must know the database name either via fully qualified name or using "Use" statement at the beginning.

[ckattookaran]

I don't think your external connection is configured for that Database. You'll have to add OneStream_Automation as an external connection to make it work. Or someone should copy the data from those tables to the external connection that is defined.

[Sridhar_M]

We were able to write to the tables in OneStream_Automation using business rules.

We were able to see the data in OneStream_Automation tables using Data Adapter.

External connection worked in above 2 scenarios and I am using the same external connection for SQL Table Editor. So I am thinking there is no issue in the external connection unless we need to configure any options specific to SQL Table editor ?

[ckattookaran]

It does work differently, even using a different schema was a later addition. So maybe you can file an enhancement asking for it.

[Sai_Maganti]

So to pin point the issue, looks like SQL Table Editor relies on the connection parameter's Initial Catalog value which is missing from the connection's connection string.

This is not required when using SQL directly as you can use qualified name and hence it is working.