blocks|key|628176|text|此查询应针对ReportServer数据库运行|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|628177|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;|code-block|syntax|javascript|628178|报表管理器中的“依赖项”页将执行dbo.FindItemsByDataSource存储过程，并提供以下参数：ItemID+=+<data+source+item+ID>和AuthType+=+1。上面的查询是此存储过程用来删除数据源特定ID的查询的黑客版本。这允许为所有数据源返回依赖项。我使用DS.Name+IS+NOT+NULL从结果中删除了数据源本身|628179|entityMap^0|6|C|0|0|G|P|1I|U|2D|C|43|J|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$]]

This query should be run against the <code>ReportServer</code> database

<pre><code>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;
</code></pre>

The dependent items page in Report Manager executes the <code>dbo.FindItemsByDataSource</code> stored procedure, supplying these parameters: <code>ItemID = &lt;data source item ID&gt;</code> and <code>AuthType = 1</code>. The above query is a hacked version of the query used by this stored procedure to remove the data source specific ID. This allows dependent items to be returned for all data sources. I removed the data sources themselves from the results with <code>DS.Name IS NOT NULL</code>

blocks|key|628209|text|您还可以考虑使用Powershell：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|628210|++++#************************************************************************************************************************************
#+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)+%7C+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
++++++++++++}
++++++++}
++++}
}|code-block|syntax|javascript|628211|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You might also consider using Powershell:

<pre><code> #************************************************************************************************************************************
# 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
 }
 }
 }
}
</code></pre>

I am new to SQL Server, and I am sorry if there is an obvious solution to my question but I can't seem to find it.

I am looking to generate a report (or list) of all the data sources and their individual dependencies on an SQL Server 2008 R2 (reporting server).

I know that I can access each individual data source to get a list of all the items that are dependent on it. I have done this in the past but it is time consuming.

Is there a way to get a report that would display all the data sources and their dependent items?

Thanks in advance,

Marwan

Listing all Data Sources and their Dependencies (reports, items, etc) in SQL Server 2008 R2

SQL Server

数据库

服务器

我是SQL Server的新手，如果我的问题有明显的解决方案，但我似乎找不到它，我很抱歉。我希望生成所有数据源及其在SQL Server2008 R2 (报告服务器)上的独立依赖项的报告(或列表)。我知道我可以访问每个单独的数据源，以获得依赖于它的所有项的列表。我以前也这样做过，但这很耗时。有没有办法得到一个显示所有数...

问列出SQL Server2008 R2中的所有数据源及其依赖项(报表、项等)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列出SQL Server2008 R2中的所有数据源及其依赖项(报表、项等)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列出SQL Server2008 R2中的所有数据源及其依赖项(报表、项等)
EN