我是SQL Server的新手,如果我的问题有一个明显的解决方案,但我似乎找不到它,对此我感到抱歉。
我想生成一个报告(或列表),列出所有数据源及其在SQL Server 2008 R2(报告服务器)上的相应依赖关系。
我知道可以访问每个单独的数据源以获取其所有依赖项的列表。我以前做过这件事,但很费时间。
是否有一种方法可以获得显示所有数据源及其依赖项的报告?
提前致谢,
Marwan
我是SQL Server的新手,如果我的问题有一个明显的解决方案,但我似乎找不到它,对此我感到抱歉。
我想生成一个报告(或列表),列出所有数据源及其在SQL Server 2008 R2(报告服务器)上的相应依赖关系。
我知道可以访问每个单独的数据源以获取其所有依赖项的列表。我以前做过这件事,但很费时间。
是否有一种方法可以获得显示所有数据源及其依赖项的报告?
提前致谢,
Marwan
以下代码是在beargle的代码基础上修改而来,实现了我需要的功能。它将列出所有数据源及其依赖项:
SELECT
C2.Name AS Data_Source_Name,
C.Name AS Dependent_Item_Name,
C.Path AS Dependent_Item_Path
FROM
ReportServer.dbo.DataSource AS DS
INNER JOIN
ReportServer.dbo.Catalog AS C
ON
DS.ItemID = C.ItemID
AND
DS.Link IN (SELECT ItemID FROM ReportServer.dbo.Catalog
WHERE Type = 5) --Type 5 identifies data sources
FULL OUTER JOIN
ReportServer.dbo.Catalog C2
ON
DS.Link = C2.ItemID
WHERE
C2.Type = 5
ORDER BY
C2.Name ASC,
C.Name ASC;
此查询应针对ReportServer
数据库运行。
SELECT
DS.Name AS DatasourceName,
C.Name AS DependentItemName,
C.Path AS DependentItemPath
FROM
ReportServer.dbo.Catalog AS C
INNER JOIN
ReportServer.dbo.Users AS CU
ON C.CreatedByID = CU.UserID
INNER JOIN
ReportServer.dbo.Users AS MU
ON C.ModifiedByID = MU.UserID
LEFT OUTER JOIN
ReportServer.dbo.SecData AS SD
ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
INNER JOIN
ReportServer.dbo.DataSource AS DS
ON C.ItemID = DS.ItemID
WHERE
DS.Name IS NOT NULL
ORDER BY
DS.Name;
报告管理器中的相关项页面执行 dbo.FindItemsByDataSource
存储过程,提供以下参数:ItemID = <数据源项 ID>
和 AuthType = 1
。上述查询是该存储过程使用的查询的一个破解版本,以删除数据源特定的 ID。这允许返回所有数据源的相关项。我使用 DS.Name IS NOT NULL
从结果中删除了数据源本身。
#************************************************************************************************************************************
# FileName: Delete-DataSources.ps1
# Date: 2015/04/23
# Author: Hugh Scott
#
# Description:
# This script finds data sources with no dependencies in SSRS and removes them.
#
# Parameters:
# $serverBase - base URL for the server to check (ie, myserver.mydomain.com)
# [$WhatIf] - Option wwitch parameter to prevent actual deleting of objects (will list out reports that need to be deleted)
#***********************************************************************************************************************************
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true,Position=0)]
[string]$serverBase,
[Parameter(Mandatory=$false,Position=1)]
[switch]$WhatIf
)
$url = "http://$serverBase/reportserver/ReportService2010.asmx?WSDL"
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService"
$outFile = ".\DeleteItems_$serverBase.txt"
# Connection to Web Service, grab all data sources
$items = $ssrs.ListChildren("/", $true) | where-object {$_.typename -eq "DataSource"}
foreach($item in $items) {
$dependencies = $ssrs.ListDependentItems($item.Path)
$dependentReports = $dependencies.Count
if($dependencies.Count -eq 0){
[string]$itemName = $item.Path
if($WhatIf){
Write-Host "Item $itemName would be deleted."
Add-Content $outFile "Item $itemName would be deleted."
} else {
try {
$ssrs.DeleteItem($item.Path)
Write-Host "Item $itemName deleted."
Add-Content $outFile "Deleted item $itemName ."
} catch [System.Exception] {
$Msg = $_.Exception.Message
Write-Host $itemName $Msg
Add-Content $itemName $msg
}
}
}
}