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