Data Porter Detail V2

-- 
-- Cubby's useful SQL
--
-- This extracts details from the Cadis Data Porter XML
-- Output:
-- porter sql
-- delete sql
--      output table name
--
 


Declare @PorterName varchar(250)
set @PorterName = '%' 

select comps.Name 
, row_number() over (partition by [Name] order by  [Name]) as seq
,dpref.value('Description[1]','nvarchar(max)') Descr
,dpref.value('Name[1]','nvarchar(max)') Name
,dpref.value('SourceDataSource[1]/Select[1]/Text[1]','nvarchar(max)') SourceSQL
,dpref.value('SourceConnection[1]/Name[1]','nvarchar(200)') SourceConnection
,dpref.value('Custom[1]/SelectedProc[1]/Name[1]','nvarchar(200)') SelectedProcess
,dpref.value('DeleteDataSource[1]/Table[1]/Name[1]','nvarchar(200)') DeleteTable
,dpref.value('Email[1]/Subject[1]','nvarchar(200)') EmailSubject
,dpref.value('External[1]/FileName[1]','nvarchar(200)') ExecutableLocation
,dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') as 'OutputFile'
,dpref.value('Targets[1]/list[1]/Item[1]/TargetConnection[1]/Name[1]','nvarchar(max)') TargetConnection
,dpref.value('Targets[1]/list[1]/Item[1]/TargetDataSource[1]/Table[1]/Name[1]','nvarchar(max)') TargetTable
from CADIS_SYS.DP_DATAPORT as comps 
CROSS APPLY comps.DEFINITION.nodes('//CadisXml/Content/Inputs/list/Item') AS DP(dpref)
where comps.Name like @PorterName
order by seq

SQL Script to use the CADIS search stored procedure

--  Using the CADIS search stored procedure so that you dont have to wait for next page
--
--   Usage 
--
--   Options for table to include
--   set @SearchStr = Set to required search string for a single quote enter 2 
--                    e.g. to search for 'SEDOL'  set @SearchStr = N'''SEDOL'''  
--
-- 
--  Rev     Who  Date   Change
--  01     EDDCUB    First Version (set to 1000 rows output
--
--

declare @searchStr nvarchar(2000)
declare @searchStrLower nvarchar(2000)
declare @searchComponentType int 

declare @Results Table  
(ComponentType nvarchar(200)
,compGUID nchar(500)
,identifier int
,name nvarchar(200)
,DefXml XML
,RowNumber int
,RecordCount int
)

--  Component Types
--  0 DataPorter
--  1 DataInspector
--  2 DataMatcherProcess
--  3 DataMatcherInbox
--  4 DataMatcherRealign
--  5 DataConstructor
--  6 DataIllustrator
--  7 DataIllustratorTemplate
--  8 Solution
-- 11 DataGeneratorFunction
-- 12 DataGeneratorInbox
-- 13 DataManagerFunction
-- 14 DataManagerInbox
-- 15 EventWatcher
-- 16 ProcessLauncher
-- 17 RuleBuilder
-- 18 ManagementDashboard
-- 21 ManagementDashboardItem
-- 22 ManagementDashboardUserGroup
-- 23 Archive
-- 24 Diagram
-- -1   All
--set @searchComponentType = -1    -- All
--set @searchComponentType = 0     -- DataPorter
set @searchComponentType = -1

set @SearchStr = N'''SECTYPE'''

SET @searchStrLower = LOWER(@SearchStr)

INSERT INTO @Results (
 ComponentType
 ,compGUID
 ,identifier
 ,NAME
 ,DefXml
 ,RowNumber
 ,RecordCount
 )
EXEC CADIS_SYS.spSearchComponentXml @SearchTerm = @searchStrLower
 ,@ComponentType = @SearchComponentType
 ,@PageNumber = 1
 ,@RowsPerPage = 1000

SELECT res.*
FROM @Results res
INNER JOIN CADIS_SYS.CO_COMPONENT com ON com.NAME = res.ComponentType
INNER JOIN CADIS_SYS.VW_ALL_PROCESSES OP ON OP.COMPONENTID = com.ID
 AND OP.GUID = res.compGUID
order by rownumber

SQL to Extract Data Porters

SQL to extract Data Porters - useful for impact analysis

with cte_soln as (SELECT [SolutionName] AS [Solution Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentName]
  ELSE RuleBuilderName 
 END AS [Component Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentType]
  WHEN RuleBuilderName is not null THEN 'RuleBuilder'
 END AS [Component Type]
, [Description]
, [xml]
FROM (
 SELECT nref.query('.') as [xml],
 nref.value('../../../Name[1]', 'nvarchar(250)') [SolutionName],
 nref.value('ProcessSpecification[1]/ProcessName[1]', 'nvarchar(500)') [ComponentName],
 nref.value('ProcessSpecification[1]/ComponentType[1]', 'nvarchar(500)') [ComponentType],
 nref.value('RuleBuilderName[1]', 'nvarchar(500)') RuleBuilderName,
 nref.value('Description[1]', 'nvarchar(4000)') [Description]
 FROM CADIS_SYS.CO_SOLUTION CROSS APPLY DEFINITION.nodes('//Content/Items/list/Item') AS R(nref)
 WHERE OBSOLETE=0 ) VW)
select [Solution Name] as SolutionName,[Component Name] as ComponentName
 ,[Component Type] as ComponentType
-- , Definition as [DataPorter]
, row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
,dpref.value('SourceDataSource[1]/Select[1]/Text[1]','nvarchar(max)') SourceSQL
,dpref.value('Custom[1]/SelectedProc[1]/Name[1]','nvarchar(200)') SelectedProcess
,dpref.value('DeleteDataSource[1]/Table[1]/Name[1]','nvarchar(200)') DeleteTable
,dpref.value('Email[1]/Subject[1]','nvarchar(200)') EmailSubject
,dpref.value('External[1]/FileName[1]','nvarchar(200)') ExecutableLocation
,dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DP_DATAPORT as comps on comps.Name = [Component Name]
CROSS APPLY DEFINITION.nodes('//CadisXml/Content/Inputs/list/Item') AS DP(dpref)
where [solution name] like '%'
and dpref.value('SourceFile[1]/FileName[1]','nvarchar(200)') is null

SQL to extract Cadis Constructors

SQL to extract Cadis Constructor SQL

with cte_soln as (SELECT [SolutionName] AS [Solution Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentName]
  ELSE RuleBuilderName 
 END AS [Component Name]
, CASE 
  WHEN [ComponentName] is not null THEN [ComponentType]
  WHEN RuleBuilderName is not null THEN 'RuleBuilder'
 END AS [Component Type]
, [Description]
, [xml]
FROM (
 SELECT nref.query('.') as [xml],
 nref.value('../../../Name[1]', 'nvarchar(250)') [SolutionName],
 nref.value('ProcessSpecification[1]/ProcessName[1]', 'nvarchar(500)') [ComponentName],
 nref.value('ProcessSpecification[1]/ComponentType[1]', 'nvarchar(500)') [ComponentType],
 nref.value('RuleBuilderName[1]', 'nvarchar(500)') RuleBuilderName,
 nref.value('Description[1]', 'nvarchar(4000)') [Description]
 FROM CADIS_SYS.CO_SOLUTION CROSS APPLY DEFINITION.nodes('//Content/Items/list/Item') AS R(nref)
 WHERE OBSOLETE=0 ) VW)
select 
    [Solution Name] as SolutionName
    ,[Component Name] as ComponentName
    ,[Component Type] as ComponentType
    , row_number() over (partition by [Solution Name], [Component Name] order by  [Solution Name], [Component Name] ) as seq
 --, Definition as [DataPorter]
,Content.value('(Text)[1]', 'varchar(max)') as SourceSQL
,NULL SelectedProcess
,NULL DeleteTable
,NULL EmailSubject
,NULL ExecutableLocation
,NULL as 'OutputFile'
from cte_soln
left outer join CADIS_SYS.DC_CONSTRUCTION as comps on comps.Name = [Component Name]
CROSS APPLY [DEFINITION].nodes('/CadisXml/Content/Inputs/list/Item/RowSource/Select') AS Tbl(Content)
where [solution name] like '%'
and [component type] = 'DataConstructor'

SQL to Extract Cadis Database Parameters from the XML Config

declare @xmlParams xml

declare @name varchar (100)
set @name = '%%'
IF  OBJECT_ID(N'[tempdb]..[#temp]') is not null
drop table #temp

CREATE TABLE #temp
(Name nvarchar(200)
,DefinitionXml xml
,CRC nchar(1000)
,details image
 )
 insert #temp exec CADIS_SYS.SPCO_GLOBALSELBYNAME @Name=N'Database Parameters'
select @xmlParams = DefinitionXml from #temp

-- select * from cadis_sys.co_global

-- select @xmlParams

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Value[1]','nvarchar(2000)') as Value
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Globals/nv/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)')  like @name
or T.col.value('Value[1]','nvarchar(2000)') like @name
order by 1

select T.col.value('(Name)[1]','nvarchar(200)') as name
,T.col.value('Path[1]','nvarchar(2000)') as NamePath
,T.col.value('UNCPath[1]','nvarchar(2000)') as UncPath
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Locs/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(200)') like @name
or T.col.value('Path[1]','nvarchar(2000)') like @name

select T.col.value('(Name)[1]','nvarchar(2000)') as ConnectionName
,T.col.value('(Info/Description)[1]','nvarchar(2000)') as Description
,T.col.value('(Info/DataSource)[1]','nvarchar(2000)') as DataSource
,T.col.value('(Info/Type)[1]','nvarchar(2000)') as ConnectionType
-- ,T.col.query('.')
from @xmlParams.nodes('/CadisXml/Content/Conns/list/Item') T(col)
where T.col.value('(Name)[1]','nvarchar(2000)')  like @name
or T.col.value('(Info/Description)[1]','nvarchar(2000)') like @name



drop table #temp

SQL to show the run history of a solution/ solutions

--
-- To See the run history of a solution/ solutions
--  
-- Rev     Who      Comment
--  01      CB      Optimised

declare @DateFrom datetime
declare @DateTo   datetime
declare @SolutionName nvarchar(200)
declare @SolutionName2 nvarchar(200)
declare @SolutionName3 nvarchar(200)

set @DateFrom =  dateadd(dd,-180,getdate()  )
set @DateTo   =  NULL
declare @runidFrom int

select @runidFrom = MAX(runid) from CADIS_SYS.CO_PROCESSHISTORY where runstart < isnull(@DateFrom, '2099-12-31 23:59:59'  )

set @SolutionName = '4 Master ' 
-- set @SolutionName2 = ''
-- set @SolutionName3 = ''
-- set @SolutionName3 = '%Industry%'
;with cte_jobs as (SELECT H1.runid, Name, datename(WeekDay,H1.RUNSTART) as StartDay
       --            ,datename(WeekDay,(dateadd(Hour,8,H1.RUNSTART))) as StartDaySG
       --            ,dateadd(Hour,8,H1.RUNSTART) as StartSG
                  ,H1.RUNSTART, H1.RUNEND, H1.RUNSUCCESSFUL
                  ,right('00' + convert(nvarchar(2),datename(hh,H1.RUNSTART)),2)+':'
                  + right('00' + convert(nvarchar(2),datename(mi,H1.RUNSTART)),2)  as startTime
                  ,H1.LAUNCHEDBY
                  --,datediff(MI,H1.RunStart, H1.RunEnd) as Duration
                  --,datediff(SS,H1.RunStart, H1.RunEnd) as DurationSeconds
                  ,RIGHT('0' + CAST(datediff(SS,H1.RunStart, H1.RunEnd)  / 3600 AS VARCHAR),2) + ':' +
      RIGHT('0' + CAST((datediff(SS,H1.RunStart, H1.RunEnd)  / 60) % 60 AS VARCHAR),2) + ':' +
      RIGHT('0' + CAST(datediff(SS,H1.RunStart, H1.RunEnd)  % 60 AS VARCHAR),2) as RunDuration
   FROM CADIS_SYS.CO_PROCESSHISTORY H1
   INNER JOIN (
      SELECT * FROM CADIS_SYS.CO_PROCESSHISTORY
      where  runid > @runidFrom
      and runstart > @DateFrom
      and runstart < isnull(@DateTo, '2099-12-31 23:59:59'  )
      ) H2
    ON H2.COMPONENTID = H1.COMPONENTID
    AND H2.GUID = H1.GUID
    AND H2.RUNID = H1.RUNID
 INNER JOIN CADIS_SYS.VW_ALL_PROCESSES ap on  ap.GUID = H1.GUID)
 select * from cte_jobs where (Name like @SolutionName or Name like @SolutionName2 or Name like @SolutionName3 )
 order by Runstart desc