Archive

Posts Tagged ‘Powershell’

Powershell: Function to Query SQL DB and return Array

October 20, 2011 Leave a comment

# Functions used for repeated Query tasks
function exec-query ($SqlQuery)
{
$SQLServer = “10.100.99.99\SQLEXPRESS” #use Server\Instance for named SQL instances!
$SQLDBName = “ReportServer$SQLEXPRESS”
$ReportArray = @()

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = “Server = $SQLServer; Integrated Security = True”
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SqlQuery

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount

while ($Reader.Read())
{
for ($i = 0; $i -lt $Counter; $i++)
{
$ReportArray+=$Reader.GetValue($i)
}
}
$Connection.Close()
Return $ReportArray
}

# Main starts here
$OutArray = @()
$OutArray2 = @()

$InQuery = “Select PATH FROM [ReportServer`$SQLEXPRESS].[dbo].[Catalog]
where Type = 2
and PATH like ‘%/My Dashboard/%’
and PATH not like ‘%SUB%’
and PATH in (‘/People Management/My Dashboard/Data_Summary’,’/People Management/My Dashboard/Data_Achieved_YTD’)”

$OutArray=exec-query($InQuery)

for ($i = -1; $i -lt ($OutArray.length-1); $i++)
{
$param=$OutArray[$i]
$InQuery2 = “;WITH DEF AS (SELECT R.RptNode.value(‘Name[1]’, ‘nvarchar(425)’) AS [Name] FROM (SELECT RPT.name AS ReportName
,CONVERT(xml, RPT.Parameter) AS contentXML FROM [ReportServer`$SQLEXPRESS].[dbo].[Catalog] AS RPT
WHERE RPT.Type = 2 AND RPT.PATH LIKE ‘$param’ ) AS RPT CROSS APPLY RPT.contentXML.nodes(‘/Parameters/Parameter’) AS R(RptNode)
) SELECT * FROM DEF”

$OutArray2=exec-query($InQuery2)
for ($j = -1; $j -lt ($OutArray2.length-1); $j++)
{
Write $OutArray2[$j]
}
}

Advertisements
Categories: Powershell Tags: ,