Prev
Next
Forums Performance Management Smart Notification

SQL Help

 2 Replies
 1 Subscribed to this topic
 72 Subscribed to this forum
Sort:
Author
Messages
Greg Moeller
Veteran Member
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
    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
      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