PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/9/2023 2:49 PM by  Greg Moeller
SQL Help
 2 Replies
 1 Subscribed to this topic
 71 Subscribed to this forum
Sort:
You are not authorized to post a reply.
Author Messages
Greg Moeller
Private
Private
Veteran Member
(4175 points)
Veteran Member
Posts:1493


Send Message:

--
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
    Private
    Private
    Veteran Member
    (4175 points)
    Veteran Member
    Posts:1493


    Send Message:

    --
    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
    Private
    Private
    Veteran Member
    (4175 points)
    Veteran Member
    Posts:1493


    Send Message:

    --
    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
    You are not authorized to post a reply.