SQL Help

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?

0712131256598360.doc

Find more posts tagged with

Infor Lawson Business Intelligence Group - Discussion

Accepted answers

delliott

Hi Greg,

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

gave me this result (which I then redacted and formatted nicely):

<data_connection type="JDBC">
<name> M3DB </name>
<description> M3 v15 ENV </description>
<jdbc_driver> com.ibm.as400.access.AS400JDBCDriver </jdbc_driver>
<jdbc_url> jdbc:as400://myserver/M3FDBENV </jdbc_url>
<jdbc_user> m3user </jdbc_user>
<jdbc_password> m3pass </jdbc_password>
<jdbc_server> </jdbc_server>
<jdbc_port> </jdbc_port>
<jdbc_database> </jdbc_database>
</data_connection>

All comments

delliott

Hi Greg,

SELECT cast(substring(attrbinaryvalue,1, 500) as varchar(500)) from ENPTREEATTRS
WHERE entrycategory = 5

gave me this result (which I then redacted and formatted nicely):

moellerg

Thanks! Just what I needed.

moellerg

I have exactly what I needed, so thank you.... just wondering, if there is a way to just get the <name>, <jdbc_url>, <jdbc_database>, and <jdbc_user> from these results? I'm thinking it has to do with the column being image type and not xml data type.. but am really new to dealing with xml in sql.

delliott

I'm not all that familiar with it either, but I was able to come up with a query that lets you select specific XPATH information after you've cast the data to XML type. There's probably a better way to do this, but this is what I got to work:

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

moellerg

All I get back when I copy/paste your code is:

Msg 9412, Level 16, State 1, Line 3
XML parsing: line 28, character 12, '>' expected

Completion time: 2023-11-07T14:39:34.9606275-06:00

Does that indicate an issue with our data? Or something else?

Copyright © 2025 Infor. All rights reserved.