I have a powershell script that, when I run it from the powershell command line, it works fine. However, when I run it via a SQL Server Agent Job it throws this error:
A job step received an error at line 149 in a Powershell script. The corresponding line is 'if ($Datatable.Rows.Count > 0)' Correct the script and reschedule the job. The error information returned by PowerShell is 'Cannot perform operation because operation "ReportWrongProviderType" is not valid. Remove operation "ReportWrongProviderType", or investigate why it is not valid.' Process Exit Code -1.
Powershell script throwing the error:
function Get-BackupHistory
{
#module to gather all of the backup history data for the DBAdmin_Central database
Write-Host "Get Backup History"
#query to get list of servers to run queries against - primary oltp's
#this will run against the central management server
$ServerQuery =
"
select
ss.PK_ServerID as id,
ss.ServerName as name,
isnull(bms.MaxMediaSetID, 0) as MaxMediaSetID,
isnull(MaxBackupSetID, 0) as MaxBackupSetID
from DBAdmin_Central.CentMang.SQLServers ss
left outer join
(
select
FK_ServerID,
max(media_set_id) as MaxMediaSetID
from DBAdmin_Central.CentMang.backupmediaset
group by FK_ServerID
) bms
on ss.PK_ServerID = bms.FK_ServerID
left outer join
(
select
FK_ServerID,
max(backup_set_id) as MaxBackupSetID
from DBAdmin_Central.CentMang.backupset
group by FK_ServerID
) bs
on ss.PK_ServerID = bs.FK_ServerID
where IsOnline = 1
and
(
FK_StackID < 15
or FK_StackID > 15
)
order by ServerName
"
Write-Host "Get list of servers"
$Servers = Invoke-Sqlcmd -ServerInstance 'centralmanagement.p.db.local' `
-Database DBADMIN_Central `
-Query $ServerQuery `
-QueryTimeout 20 `
-OutputSqlErrors $True `
-ConnectionTimeout 5 `
-ErrorAction Continue
Write-Host "Iterate through servers"
#iterate through the list of servers and execute functions for each server
Foreach ($server in $Servers)
{
Write-Host $server.name, $server.id
GetBackupMediaSet $server.name $server.id $server.MaxMediaSetID
GetBackupMediaFamily $server.name $server.id $server.MaxMediaSetID
GetBackupSet $server.name $server.id $server.MaxBackupSetID
GetBackupFile $server.name $server.id $server.MaxBackupSetID
GetBackupFileGroup $server.name $server.id $server.MaxBackupSetID
}
}
function GetBackupMediaSet
{
[CmdletBinding()]
param
(
[string] $server,
[int] $serverid,
[int] $id
)
Write-Host " Get Backup Media Set"
#query we're running
$BMSQuery =
"
SELECT [media_set_id]
,[media_uuid]
,[media_family_count]
,[name]
,[description]
,[software_name]
,[software_vendor_id]
,[MTF_major_version]
,[mirror_count]
,[is_password_protected]
,[is_compressed]
,[is_encrypted]
FROM [msdb].[dbo].[backupmediaset]
where media_set_id > @id
"
#where we're storing query results
$Datatable = New-Object System.Data.DataTable
Write-Host " Get Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBAdmin;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $BMSQuery
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id
$DataReader = $sqlCommand.ExecuteReader()
$Datatable.Load($DataReader)
}
catch
{
Write-Host " Error Getting Backup Media Set"
LogError "Get-BackupHistory" "Get Backup Media Set for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
if ($Datatable.Rows.Count > 0)
{
Write-Host " Insert Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='centralmanagement.p.db.local'; Database=DBAdmin_Central;Trusted_Connection=True;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "[CentMang].[Insertbackupmediaset_Results]"
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCommand.CommandTimeout = 10
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $serverid
$sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Error Inserting Backup Media Set"
LogError "Get-BackupHistory" "Insert Backup Media Set for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
}
else
{
Write-Host " No Results"
}
}
function GetBackupMediaFamily
{
[CmdletBinding()]
param
(
[string] $server,
[int] $serverid,
[int] $id
)
Write-Host " Get Backup Media Family"
#query we're running
$BMFQuery =
"
SELECT [media_set_id]
,[family_sequence_number]
,[media_family_id]
,[media_count]
,[logical_device_name]
,[physical_device_name]
,[device_type]
,[physical_block_size]
,[mirror]
FROM [msdb].[dbo].[backupmediafamily]
where media_set_id > @id
"
#where we're storing query results
$Datatable = New-Object System.Data.DataTable
Write-Host " Get Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBAdmin;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $BMFQuery
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id
$DataReader = $sqlCommand.ExecuteReader()
$Datatable.Load($DataReader)
}
catch
{
Write-Host " Error Getting Backup Media Family"
LogError "Get-BackupHistory" "Get Backup Media Family for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
if ($Datatable.Rows.Count > 0)
{
Write-Host " Insert Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='centralmanagement.p.db.local'; Database=DBAdmin_Central;Trusted_Connection=True;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "[CentMang].[Insertbackupmediafamily_Results]"
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCommand.CommandTimeout = 10
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $serverid
$sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Error Inserting Backup Media Family"
LogError "Get-BackupHistory" "Insert Backup Media Family for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
}
else
{
Write-Host " No Results"
}
}
function GetBackupSet
{
[CmdletBinding()]
param
(
[string] $server,
[int] $serverid,
[int] $id
)
Write-Host " Get Backup Set"
#query we're running
$BSQuery =
"
SELECT [backup_set_id]
,[backup_set_uuid]
,[media_set_id]
,[first_family_number]
,[first_media_number]
,[last_family_number]
,[last_media_number]
,[catalog_family_number]
,[catalog_media_number]
,[position]
,[expiration_date]
,[software_vendor_id]
,[name]
,[description]
,[user_name]
,[software_major_version]
,[software_minor_version]
,[software_build_version]
,[time_zone]
,[mtf_minor_version]
,[first_lsn]
,[last_lsn]
,[checkpoint_lsn]
,[database_backup_lsn]
,[database_creation_date]
,[backup_start_date]
,[backup_finish_date]
,[type]
,[sort_order]
,[code_page]
,[compatibility_level]
,[database_version]
,[backup_size]
,[database_name]
,[server_name]
,[machine_name]
,[flags]
,[unicode_locale]
,[unicode_compare_style]
,[collation_name]
,[is_password_protected]
,[recovery_model]
,[has_bulk_logged_data]
,[is_snapshot]
,[is_readonly]
,[is_single_user]
,[has_backup_checksums]
,[is_damaged]
,[begins_log_chain]
,[has_incomplete_metadata]
,[is_force_offline]
,[is_copy_only]
,[first_recovery_fork_guid]
,[last_recovery_fork_guid]
,[fork_point_lsn]
,[database_guid]
,[family_guid]
,[differential_base_lsn]
,[differential_base_guid]
,[compressed_backup_size]
,[key_algorithm]
,[encryptor_thumbprint]
,[encryptor_type]
FROM [msdb].[dbo].[backupset]
where backup_set_id > @id
"
#where we're storing query results
$Datatable = New-Object System.Data.DataTable
Write-Host " Get Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBAdmin;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $BSQuery
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id
$DataReader = $sqlCommand.ExecuteReader()
$Datatable.Load($DataReader)
}
catch
{
Write-Host " Error Getting Backup Set"
LogError "Get-BackupHistory" "Get Backup Set for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
if ($Datatable.Rows.Count > 0)
{
Write-Host " Insert Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='centralmanagement.p.db.local'; Database=DBAdmin_Central;Trusted_Connection=True;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "[CentMang].[Insertbackupset_Results]"
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCommand.CommandTimeout = 10
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $serverid
$sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Error Inserting Backup Set"
LogError "Get-BackupHistory" "Insert Backup Set for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
}
else
{
Write-Host " No Results"
}
}
function GetBackupFile
{
[CmdletBinding()]
param
(
[string] $server,
[int] $serverid,
[int] $id
)
Write-Host " Get Backup File"
#query we're running
$BFQuery =
"
SELECT [backup_set_id]
,[first_family_number]
,[first_media_number]
,[filegroup_name]
,[page_size]
,[file_number]
,[backed_up_page_count]
,[file_type]
,[source_file_block_size]
,[file_size]
,[logical_name]
,[physical_drive]
,[physical_name]
,[state]
,[state_desc]
,[create_lsn]
,[drop_lsn]
,[file_guid]
,[read_only_lsn]
,[read_write_lsn]
,[differential_base_lsn]
,[differential_base_guid]
,[backup_size]
,[filegroup_guid]
,[is_readonly]
,[is_present]
FROM [msdb].[dbo].[backupfile]
where backup_set_id > @id
"
#where we're storing query results
$Datatable = New-Object System.Data.DataTable
Write-Host " Get Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBAdmin;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $BFQuery
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id
$DataReader = $sqlCommand.ExecuteReader()
$Datatable.Load($DataReader)
}
catch
{
Write-Host " Error Getting Backup File"
LogError "Get-BackupHistory" "Get Backup File for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
if ($Datatable.Rows.Count > 0)
{
Write-Host " Insert Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='centralmanagement.p.db.local'; Database=DBAdmin_Central;Trusted_Connection=True;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "[CentMang].[Insertbackupfile_Results]"
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCommand.CommandTimeout = 10
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $serverid
$sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Error Inserting Backup File"
LogError "Get-BackupHistory" "Insert Backup File for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
}
else
{
Write-Host " No Results"
}
}
function GetBackupFileGroup
{
[CmdletBinding()]
param
(
[string] $server,
[int] $serverid,
[int] $id
)
Write-Host " Get Backup File Group"
#query we're running
$BFGQuery =
"
SELECT [backup_set_id]
,[name]
,[filegroup_id]
,[filegroup_guid]
,[type]
,[type_desc]
,[is_default]
,[is_readonly]
,[log_filegroup_guid]
FROM [msdb].[dbo].[backupfilegroup]
where backup_set_id > '$id'
"
#where we're storing query results
$Datatable = New-Object System.Data.DataTable
Write-Host " Get Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBAdmin;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $BFGQuery
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id
$DataReader = $sqlCommand.ExecuteReader()
$Datatable.Load($DataReader)
}
catch
{
Write-Host " Error Getting Backup File Group"
LogError "Get-BackupHistory" "Get Backup File Group for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
if ($Datatable.Rows.Count > 0)
{
Write-Host " Insert Results"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='centralmanagement.p.db.local'; Database=DBAdmin_Central;Trusted_Connection=True;"
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "[CentMang].[Insertbackupfilegroup_Results]"
$sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCommand.CommandTimeout = 10
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $serverid
$sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Error Inserting Backup File Group"
LogError "Get-BackupHistory" "Insert Backup File Group for $server" $_.Exception.Message
}
finally
{
$sqlConnection.Close()
}
}
else
{
Write-Host " No Results"
}
}
function LogError
{
[CmdletBinding()]
param
(
[string] $JobName,
[string] $JobStep,
[string] $Error
)
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=report.p.db.local; Database=Monitor_Reporting;Trusted_Connection=True;"
$sqlConnection.Open()
$sql = "
Insert into CentMang.ErrorLog (JobName, JobStep, Error)
values (@JobName, @JobStep, @Error)
"
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $sql
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.Parameters.Add("@JobName", [System.Data.SqlDbType]::VarChar).value = $JobName
$sqlCommand.Parameters.Add("@JobStep", [System.Data.SqlDbType]::VarChar).value = $JobStep
$sqlCommand.Parameters.Add("@Error", [System.Data.SqlDbType]::VarChar).value = $Error
Write-Host " Insert Error"
$sqlCommand.ExecuteNonQuery()
}
catch
{
Write-Host " Failed to log error"
}
finally
{
$sqlConnection.Close()
}
}
Essentially, I generate a list of servers and iterate through that list. For each server, I go out and get data from a table and save it to a data table. I then check if there are any rows in the data table, and if there are then I insert it into our central report server.
SQL Server looks to be throwing up on checking the data table size, and I'm unsure as to why it would be doing that. I couldn't find anything on this specific issue.
@JosefZ's comment is correct, the issue was using > rather than -gt to compare the data table. Once I fixed that the powershell worked flawlessly.