• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Smart Notification

SQL Help

 2 Replies
 1 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
11/6/2023 4:44 PM
We're trying to automate the documentation of Smart Notes Data Sources.
(I've found the Reporting Services, Override DSN, and Landmark Data Sources)

I've partially found the SN's entries-
SELECT *
FROM [LawsonSN].[dbo].[ENPTREEATTRS]
WHERE ENTRYPARENT = 601
gives me their names and desciptoins... but no other information.. Unless it's encoded in the ATTRBINARYVALUE field...

Can anyone help me out?
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
11/7/2023 4:02 PM
I got an answer on the Infor Communities page so thought I'd share here...

you might be better off selecting by EntryCategory = 5 (which is "Data Sources", according to the ENPCategories table). And yes, the actual data source definition is stored in the ATTRBINARYVALUE, which you can access by a bit of casting:
SELECT cast(substring(attrbinaryvalue,1, 500) as varchar(500)) from ENPTREEATTRS
WHERE entrycategory = 5
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
11/9/2023 2:49 PM
Here's the query that @Elliott came up with to help me out:

with CS (ConnectionString) as
(
select cast(cast(substring(attrbinaryvalue,1,550) as varchar(550)) as xml) as ConnectionString
from LawsonSN.dbo.ENPTREEATTRS
where attrcategory = 5
)
select
--CS.ConnectionString
CS.ConnectionString.value('(/data_connection/name)[1]','varchar(50)') as DBName
,CS.ConnectionString.value('(/data_connection/jdbc_url)[1]','varchar(150)') as JDBCURL
,CS.ConnectionString.value('(/data_connection/jdbc_database)[1]','varchar(50)') as JDBCDatabase
,CS.ConnectionString.value('(/data_connection/jdbc_user)[1]','varchar(50)') as JDBCUser
from CS