我是SQL Server的新手,如果我的问题有明显的解决方案,但我似乎找不到它,我很抱歉。
我希望生成所有数据源及其在SQL Server2008 R2 (报告服务器)上的独立依赖项的报告(或列表)。
我知道我可以访问每个单独的数据源,以获得依赖于它的所有项的列表。我以前也这样做过,但这很耗时。
有没有办法得到一个显示所有数据源及其依赖项的报告?
提前谢谢你,
马尔万
发布于 2012-03-10 03:41:50
此查询应针对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 = <data source item ID>
和AuthType = 1
。上面的查询是此存储过程用来删除数据源特定ID的查询的黑客版本。这允许为所有数据源返回依赖项。我使用DS.Name IS NOT NULL
从结果中删除了数据源本身
发布于 2015-04-23 22:55:18
您还可以考虑使用Powershell:
#************************************************************************************************************************************
# 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
}
}
}
}
https://stackoverflow.com/questions/9638431
复制相似问题