Cognos Data Manager Find Connection Details

Cognos Data Manager Find Connection Details

Cognos Data Manager Find Connection Details

We can find the all the available connection details using following TSQL query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT *
,CASE
WHEN CHARINDEX(''' OLEDB ''',LINE_TEXT) > 0 THEN 'OLEDB_DB'
WHEN CHARINDEX(''' ODBC ''DSN=',LINE_TEXT) > 0 THEN 'ODBC_DSN'
WHEN CHARINDEX('.def''',LINE_TEXT) > 0 THEN 'TextFile'
END AS ConnectionType
,CASE
WHEN CHARINDEX(''' OLEDB ''',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11,CHARINDEX(''' OLEDB',LINE_TEXT)-(CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11))
WHEN CHARINDEX(''' ODBC ''DSN=',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11,CHARINDEX(''' ODBC',LINE_TEXT)-(CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11))
WHEN CHARINDEX('.def''',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11,CHARINDEX(''' SQLTXT',LINE_TEXT)-(CHARINDEX('<DEFAULT> ''',LINE_TEXT)+11))
END AS ConnectionName
,CASE
WHEN CHARINDEX(''' OLEDB ''',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX('Database=',LINE_TEXT)+9,CHARINDEX(';Data Source=',LINE_TEXT)-(CHARINDEX('Database=',LINE_TEXT)+9))
WHEN CHARINDEX(''' ODBC ''DSN=',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX('ODBC ''DSN=',LINE_TEXT)+10,CHARINDEX(';DBMS=',LINE_TEXT)-(CHARINDEX('ODBC ''DSN=',LINE_TEXT)+10))
WHEN CHARINDEX('.def''',LINE_TEXT) > 0 THEN SUBSTRING(LINE_TEXT,CHARINDEX(' SQLTXT',LINE_TEXT)+8,(CHARINDEX('.def''',LINE_TEXT)+5)-(CHARINDEX(''' SQLTXT',LINE_TEXT)+8))
END AS SourceDetails
 
FROM DM_Catalog.dbo.dsb_component_line
WHERE component_type = 'A' AND line_text LIKE '%<DEFAULT>%'
GO
0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share