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 |
Please login to post your valuable comments.
Get the latest vLemonn news first