/********************************************************************************
* PROCEDURE NAME: dbo.usp_ReportGlossary
* PROCEDURE DESC: Provides details around reports including connections, users, data sources
and report glossary details
@QueryPart provides particular section of detail
-- 1 = Created by
-- 2 = Parameters
-- 3 = Data Sources, Sets, Commands and Fields
-- 4 = Subscriptions
-- 5 = Snapshot
-- 6 = Authorised Users
-- 7 = Execution Log
-- 8 = Report Glossary Details
*********************************************************************************/
CREATE PROCEDURE [dbo].[usp_ReportGlossary]
(
@ReportName VARCHAR(850),
@QueryPart INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Namespace VARCHAR(500)
DECLARE @SQL VARCHAR(MAX)
DECLARE @ReportID VARCHAR(50) = (SELECT C.ItemID FROM [ReportServer].[dbo].[Catalog] C WHERE C.Content is not null AND C.Type = 2 AND C.Name = @ReportName)
SELECT @Namespace= SUBSTRING(
x.CatContent
,x.CIndex
,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
)
FROM
(
SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
,CIndex = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
FROM Reportserver.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) X
SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
---------------------------------
-- Get report creation details
---------------------------------
IF @QueryPart = 1
SELECT Name,CreatedBy = U.UserName
,CreationDate = C.CreationDate
,ModifiedBy = UM.UserName
,ModifiedDate
FROM Reportserver.dbo.Catalog C
JOIN Reportserver.dbo.Users U
ON C.CreatedByID = U.UserID
JOIN Reportserver.dbo.Users UM
ON c.ModifiedByID = UM.UserID
WHERE Name = @ReportName
---------------------------------
-- Get parameters of the report
---------------------------------
IF @QueryPart = 2
SELECT Name = Paravalue.value('Name[1]', 'VARCHAR(250)')
,Type = Paravalue.value('Type[1]', 'VARCHAR(250)')
,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
,AllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
,MultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
,UsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
,State = Paravalue.value('State[1]', 'VARCHAR(250)')
FROM (
SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
AND C.Name = @ReportName
) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Data Sets , Command , Data fields Associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 3
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT DISTINCT ReportName = name
,DataSetName = x.value(''(@Name)[1]'', ''VARCHAR(250)'')
,DataSourceName = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'')
,CommandText = x.value(''(Query/CommandText)[1]'',''VARCHAR(MAX)'')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
AND C.Name = ''' + @ReportName + '''
) a
CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
CROSS APPLY x.nodes(''Fields/Field'') f(df)
ORDER BY name '
EXEC(@SQL)
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get subscription Associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 4
SELECT Reportname = c.Name
,SubscriptionDesc=su.Description
,Subscriptiontype=su.EventType
,su.LastStatus
,su.LastRunTime
,Schedulename=sch.Name
,ScheduleType = sch.EventType
,ScheduleFrequency =
CASE sch.RecurrenceType
WHEN 1 THEN 'Once'
WHEN 2 THEN 'Hourly'
WHEN 4 THEN 'Daily/Weekly'
WHEN 5 THEN 'Monthly'
END
,su.Parameters
FROM Reportserver.dbo.Subscriptions su
JOIN Reportserver.dbo.Catalog c
ON su.Report_OID = c.ItemID
JOIN Reportserver.dbo.ReportSchedule rsc
ON rsc.ReportID = c.ItemID
AND rsc.SubscriptionID = su.SubscriptionID
JOIN Reportserver.dbo.Schedule Sch
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Snapshot associated with the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 5
SELECT C.Name
,H.SnapshotDate
,S.Description
,ScheduleForSnapshot = ISNULL(Sc.Name,'No Schedule available for Snapshot')
,ScheduleType = sc.EventType
,ScheduleFrequency =
CASE sc.RecurrenceType
WHEN 1 THEN 'Once'
WHEN 2 THEN 'Hourly'
WHEN 4 THEN 'Daily/Weekly'
WHEN 5 THEN 'Monthly'
END
,sc.LastRunTime
,sc.LastRunStatus
,ScheduleNextRuntime = SC.NextRunTime
,S.EffectiveParams
,S.QueryParams
FROM Reportserver.dbo.History H
JOIN Reportserver.dbo.SnapshotData S
ON H.SnapshotDataID = S.SnapshotDataID
JOIN Reportserver.dbo.Catalog c
ON C.ItemID = H.ReportID
LEFT JOIN Reportserver.dbo.ReportSchedule Rs
ON RS.ReportID = H.ReportID
AND RS.ReportAction = 2
LEFT JOIN Reportserver.dbo.Schedule Sc
ON Sc.ScheduleID = rs.ScheduleID
WHERE C.Name = @ReportName
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Get Users List having access to reports and tasks they can perform on the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 6
SELECT C.Name
,U.UserName
,R.RoleName
,R.Description
,U.AuthType
FROM Reportserver.dbo.Users U
JOIN Reportserver.dbo.PolicyUserRole PUR
ON U.UserID = PUR.UserID
JOIN Reportserver.dbo.Policies P
ON P.PolicyID = PUR.PolicyID
JOIN Reportserver.dbo.Roles R
ON R.RoleID = PUR.RoleID
JOIN Reportserver.dbo.Catalog c
ON C.PolicyID = P.PolicyID
WHERE c.Name = @ReportName
ORDER BY U.UserName
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Execution Log fo the report
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 7
SELECT C.Name
,Case E.Requesttype
WHEN 1 THEN 'Subscription'
WHEN 0 THEN 'Report Launch'
ELSE ''
END
,E.TimeStart
,E.TimeProcessing
,E.TimeRendering
,E.TimeEnd
,E.Status
,E.InstanceName
,E.UserName
FROM Reportserver.dbo.ExecutionLog E
JOIN Reportserver.dbo.Catalog C
ON E.ReportID = C.ItemID
WHERE C.Name = @ReportName
ORDER BY E.TimeStart DESC
----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Report Glossary
----------------------------------------------------------------------------------------------------------------------------------------------------------
IF @QueryPart = 8
SELECT rg.ReportGlossaryID,rg.ReportID, rg.TypeID, rgt.Description , rg.context, rg.CreatedOn, rg.ModifiedOn FROM [DW_CTL].[dbo].[ReportGlossary] rg
LEFT JOIN [DW_CTL].[dbo].[ReportGlossaryType] rgt ON rgt.TypeID = rg.TypeID
WHERE ReportID IN(@ReportID)
END
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Tuesday, March 14, 2017
Thursday, June 25, 2015
SSIS - SQL Data Type Mapping
SSIS Data Type
|
SSIS Expression
|
SQL Server
|
single-byte signed integer
|
(DT_I1)
| |
two-byte signed integer
|
(DT_I2)
|
smallint
|
four-byte signed integer
|
(DT_I4)
|
int
|
eight-byte signed integer
|
(DT_I8)
|
bigint
|
single-byte unsigned integer
|
(DT_UI1)
|
tinyint
|
two-byte unsigned integer
|
(DT_UI2)
| |
four-byte unsigned integer
|
(DT_UI4)
| |
eight-byte unsigned integer
|
(DT_UI8)
| |
float
|
(DT_R4)
|
real
|
double-precision float
|
(DT_R8)
|
float
|
string
|
(DT_STR, «length», «code_page»)
|
char, varchar
|
Unicode text stream
|
(DT_WSTR, «length»)
|
nchar, nvarchar, sql_variant, xml
|
date
|
(DT_DATE)
|
date
|
Boolean
|
(DT_BOOL)
|
bit
|
numeric
|
(DT_NUMERIC, «precision», «scale»)
|
decimal, numeric
|
decimal
|
(DT_DECIMAL, «scale»)
|
decimal
|
currency
|
(DT_CY)
|
smallmoney, money
|
unique identifier
|
(DT_GUID)
|
uniqueidentifier
|
byte stream
|
(DT_BYTES, «length»)
|
binary, varbinary, timestamp
|
database date
|
(DT_DBDATE)
|
date
|
database time
|
(DT_DBTIME)
| |
database time with precision
|
(DT_DBTIME2, «scale»)
|
time(p)
|
database timestamp
|
(DT_DBTIMESTAMP)
|
datetime, smalldatetime
|
database timestamp with precision
|
(DT_DBTIMESTAMP2, «scale»)
|
datetime2
|
database timestamp with timezone
|
(DT_DBTIMESTAMPOFFSET, «scale»)
|
datetimeoffset(p)
|
file timestamp
|
(DT_FILETIME)
| |
image
|
(DT_IMAGE)
|
image
|
text stream
|
(DT_TEXT, «code_page»)
|
text
|
Unicode string
|
(DT_NTEXT)
|
ntext
|
Subscribe to:
Posts (Atom)