suppose i have two db servers.
I have the following script
Function Query1($Query1) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=db1.com;Initial
Catalog=Report Server;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query1
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]}
Function Query2($Query2) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=db2.com;Initial
Catalog=Report Server;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query2
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]}
$Result1 = Query1 "SELECT [InstanceName]
,[ItemPath]
,ca.name as ObjectName
,(case
when ca.type = 1 then 'Folder'
when ca.type = 2 then 'Report'
when ca.type = 3 then 'Resources'
when ca.type = 4 then 'Linked Report'
when ca.type = 5 then 'Data Source'
when ca.type = 6 then 'Report Model'
when ca.type = 7 then ' Report Part (SQL 2008 R2, unverified)'
when ca.type = 8 then 'Shared Dataset (SQL 2008 R2)'
when ca.type = 13 then 'PowerBI Report'
End ) as ObjectType
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ItemAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [dbo].[$reportServerView] e
join Catalog ca
on ca.Path = e.ItemPath where ItemAction = 'ConceptualSchema'or ItemAction
= 'QueryData'";
$Result1 | Export-Csv -Path $file_Path\file.csv -NoTypeInformation
$Result2 = Query2 "SELECT [InstanceName]
,[ItemPath]
,ca.name as ObjectName
,(case
when ca.type = 1 then 'Folder'
when ca.type = 2 then 'Report'
when ca.type = 3 then 'Resources'
when ca.type = 4 then 'Linked Report'
when ca.type = 5 then 'Data Source'
when ca.type = 6 then 'Report Model'
when ca.type = 7 then ' Report Part (SQL 2008 R2, unverified)'
when ca.type = 8 then 'Shared Dataset (SQL 2008 R2)'
when ca.type = 13 then 'PowerBI Report'
End ) as ObjectType
,[UserName]
,[ExecutionId]
,[RequestType]
,[Format]
,[Parameters]
,[ItemAction]
,[TimeStart]
,[TimeEnd]
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
,[AdditionalInfo]
FROM [dbo].[$reportServerView] e
join Catalog ca
on ca.Path = e.ItemPath where ItemAction = 'ConceptualSchema'or ItemAction
= 'QueryData'";
$Result2 | Export-Csv -Path $file_Path\file.csv -NoTypeInformation
The second query overwrites the results of the 1st query results in the csv file. i need both queries to append to same file, however, i dont really want to use append because everytime i run the script i want the file to be overwritten with new data, as this runs every day