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

[kattyice]

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

[Farah-Mohammed · Accepted solution]

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)