Database Connection Problem

Database Connection Problem

  
Hi.

I've created a Database Connection to an external database in Service Center. Used the "Test Connection String" button, and the result was success.

I've imported the external entities with Integration Studio from this Database Connection, and everything went fine and published the extension.

Finally referenced the external entities in Service Studio but the queries don't run. Running the Simple Query Test or just running the app I get this error:

Invalid object name 'MY_DATABASE.dbo.MY_TABLE'.

at OutSystems.HubEdition.RuntimePlatform.DBHelper.#sbb(DbException e, IDbTransaction trans)
   at OutSystems.HubEdition.RuntimePlatform.DBHelper.ExecuteReader(IDbCommand cmd, String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)
   at OutSystems.HubEdition.RuntimePlatform.DBHelper.#tbb(RecordList rl, IDbCommand cmd, String description, Boolean transformParameters, Boolean skipLog)
   at OutSystems.HubEdition.RuntimePlatform.DBHelper.NewExecuteSimpleQuery(RecordList rl, IDbCommand cmd, String description)
   at ssRH.Flows.FlowColaborador.WBlkColaborador.FuncssPreparation.QueryGetFuncionario(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int32& outParamCount, String qpCodigo)
   at ssRH.Flows.FlowColaborador...r.DataBind()
   at System.Web.UI.Control.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at System.Web.UI.Control.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at System.Web.UI.Control.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.OSPage.DataBind()
   at ssRH.Flows.FlowColaborador.ScrnColaborador_Show.DataBind()
   at ssRH.Flows.FlowColaborador.ScrnColaborador_Show.Page_Load(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


Why can't Service Studio connect to the database using the Database Connection, since Service Center and Integration Studio have no problem connecting to this database?
It seems that Service Studio\Platform and not using the Database Connection, and it's running the query in the Outsystems database, where this table does not exists and then the error makes sense.
I would say it may be caused by user permissions. The user that generated the entity is probably different from the one running the query. One has access, the other doesn't.
I think the problem is not about permissions. The error is about a object not found in the database, if the user does not have permissions SQL Server returns another message.

Platfrom Server is not using the Database Connection I have created and that is associated with this entities I have imported, when running the queries. I just don't know how to fix this.
This should be a very simple thing, but I only found info about creating the Database Connection and using it in the Integration Studio. I tought it would also be simple to use the entities with Service Studio :/

If I can't find a solution I'll have to use a Linked Server..
Hi Carlos,

Did you associate your extension with the correct database connection in Service Center?
Check in Service Center on the "Operations" tab of your extension details if the relation between the Logical Database and the Database Connection is correct.

Regards,
João Rosado
Also, please check the integration studio and the Physical table name, when using a wizard it can happen that the value is something like "dbo"."My_table".
make sure only the tablename is there, not any prefixes for schemas, namespaces etc. 
I'm connecting to the external database with a Linked Server because I didn't have time to solve this.

As soon I get some free time I will try this again and check your suggestions.
Thanks.
Statler & Waldorf and the amazing flappy tweedles! wrote:
Also, please check the integration studio and the Physical table name, when using a wizard it can happen that the value is something like "dbo"."My_table".
make sure only the tablename is there, not any prefixes for schemas, namespaces etc. 
 I've got the same issue, but the objects I need are not on the dbo schema on the remote database.

If in the Physical table name I have SERVER.DBNAME.SCHEMA.OBJECTNAME it works and I can use the entity.

If I have SCHEMA.OBJECTNAME and configure the logical database in service studio, as Carlos said above, it tries to query on the Outsystems database.

Linked server is one solution I suppose, but I'd rather use the recommended way if possible.  Any suggestions?

Thanks
 
Statler & Waldorf and the amazing flappy tweedles! wrote:
Also, please check the integration studio and the Physical table name, when using a wizard it can happen that the value is something like "dbo"."My_table".
make sure only the tablename is there, not any prefixes for schemas, namespaces etc. 
 Thank you for your solution. I encountered the same problem and getting rid of prefixes as you suggested resolved the problem! :)