How to access external database data from script activity in web application in ACC

Avatar

Avatar

kattyice

Avatar

kattyice

kattyice

10-03-2020

Hi,

 

I Have an external Micrsoft SQL server database defined in the external account in my ACC. I am able to use the database from the "JavaScript code" activity  in the ACC workflows with no problem. However, when I am trying to query that external database from the "Script" Activity in the ACC web application(for example, by just a simple select sql query or a simple join), in the preview debug window. it shows some errors like below:

 

Cannot load document of type 'External Accounts (nms:extAccount)' satisfying condition '([/@name] = 'MY_EXTERNAL_DATABASE_NAME)'. 
XSV-350062 Unable to start data source 'nms:extAccount:MY_EXTERNAL_DATABASE_NAME'. SOP-330011 Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.
 
The same query works in the javascript code activity in workflow but does NOT work in the script activity in web application. Is there a way to configure the webapplication or other methods, that can let me to access the external database data from my ACC web application? 
 
Thank you and regards
Claude

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Farah-Mohammed

Employee

Avatar

Farah-Mohammed

Employee

Farah-Mohammed
Employee

04-05-2020

Hi @kattyice ,

Can you try using SQL code like the below in JS of the WebApp?

sqlExecOnDataSource (query, dataSource [, value1, ... ])


The authorized values for the data source can be:

  • "" or "default" points to the Neolane database
  • "nms:extAccount:InternalName" points to a datasource defined in the Neolane external accounts (FDA)
  • "name" (name of the datasource) points to a data source defined in the instance configuration file (config-instance.xml) in the following form:

·                          <dataStore hosts="*" lang="">·               <dataSource name="datasourcename">·                 <dbcnx encrypted="" login="" password=""·                        provider="" server=""/>·               </dataSource>    </dataStore>

Example:

sqlExecOnDataSource("UPDATE XtkOption SET sStringValue = $(sz) WHERE sName = $(sz)", <DataSource>sTableSpace, 'WdbcOptions_TableSpaceWork_nms:extAccount:' + sExtAccount)

 

Answers (0)