All uses of connections and data sources via SQL query

0

Hi Marcin, yes it is possible to get that information with SQL Query, here some simple examples, which should help you start:

Data Sources <-> Applications:
SELECT WFS_Name, APP_Name FROM AppDataSourceAssocs JOIN WFDataSources ON WFS_ID = ADSA_WFSID JOIN WFApplications ON APP_ID = ADSA_APPID

Data Connnections <-> Applications:
SELECT WFC_Name, APP_Name FROM AppDataConnectionAssocs JOIN WFDataConnections ON WFC_ID = ADCA_WFCID JOIN WFApplications ON APP_ID = ADCA_APPID

To list it based on process, you will need WFDefinitions, WFConfigurations. It looks like you should be also able to query for specific places where a data connection/source is used - WFConfigurations, and WFBusinessRuleDefinitions will help with that.

In attachment a quick diagram (with missing AppDataConnectionAssocs though) of those tables in schema, that should help. I'd recommend creating your own diagram to see specific FK to join on :)

@Edit, it happend that i needed this type of query today, and here is query to get application, process and field name for chosen data sources:

SELECT
APP_Name,
DEF_Name,
WFCON_Prompt,
WFS_Name,
WFCON_ID,
WFS_ID
FROM
WFConfigurations JOIN
WFDataSources ON WFS_ID = WFCON_WFSID JOIN
WFDefinitions ON DEF_ID = WFCON_DEFID JOIN
WFApplications ON APP_ID = DEF_APPID
WHERE
WFS_ID IN (325, 108, 262, 261) -- Could be WFS_Name too.

Thank you Maksymilian for the hint.
Do you think this idea is also good for retrieving the sql queries themselves from the data sources?
Or is this rather unrealistic due to the possibility of nested "SQL COMMAND" action calls?

You could easily retrieve SQL Queries from Data Sources, they are stored in WFS_SelectCommand column (WFDataSources table).
I'm not sure what you want to do with those queries later, you should be able to execute them using sp_executesql function, and there is no need to nest SQL Command - you use just one.

Although I'm not sure if i'd recommend it, as i don't know the use case - could you share an idea why you want to do it?

Also, it works well for SQL Sources, for Internal Sources, there is no query, but some XML structure with filter values, so this might be harder.

Needs to list from all processes all SQL queries along with what connection/data source they relate to, process, workflow, attribute/path, etc.

The whole system consists of many complex processes that may use data pulled directly from SQL databases.
I am hoping that this approach will be more accurate than manually reviewing each process.

I'm almost sure that it will be more accurate and faster, but it will require a lot of thinking on where SQL could be used, to make sure that you get all the places.
But you have to find them either way, manually, or with query.

Why is there a SQL COMMAND needed though?
It will be much easier to create that query using some external tools like Azure Data Studio, DBeaver, or SQL Server Management Studio.
If you can query database with webcon, there should be no problem to get permissions to query database directly. It's basically the same access.

Maybe I wasn't very clear 😊 - I don't want to use "sql command" to call this query. I am using SSMS.
I meant to find all the places in the system where the functions visible in Studio as "Integration -> SQL COMMAND" are used, and the rest where pure SQL is used.

I tried to do some more digging, and the hardest ones to do present will be probably actions, as SQL queries for actions, are stored as XML.

In WFActions there is ACT_Configuration column, which contains xml like this:

<filters>
<filter propertyid="4" datasourceid="#SRC:0#" connectionid="#WFC:1#">
<connectionquery>select det_id
,0 as {DCNCOL:134}
from wfelementdetails where det_wfdid = {WFD_ID} and det_wfconid = {WFCON:101} and ({DCNCOL:126} + {DCNCOL:127}+ {DCNCOL:128} &lt;&gt; {DCNCOL:51})</connectionquery>
</filter>
</filters>

For Business rules in WFBusinessRuleDefinitions there is BRD_Value column, and you should be able to filter it out with BRD_Name = 'SQL COMMAND'
Another place where SQL Queries show up is WFConfigurations in WFCON_SelectOrCaml column.

It seems like in the end both ways you'll have to check it manually for the first time, but doing it in SQL will help you in case you'll have to do it again.

Maksymilian, thank you for your help and clarification :)