How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Your Own Question
Steve
Steve, Consultant
Category: Programming
Satisfied Customers: 263
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Programming Question Here...
Steve is online now
A new question is answered every 9 seconds

I would like to get help on PowerShell scripting.

Customer Question

Hello,
I would like to get help on PowerShell scripting.
Submitted: 10 months ago.
Category: Programming
Customer: replied 10 months ago.
Once I hear back, I can make my script available. Basically, here is my problem:I have a Powershell script. Most of it is working. It runs a SQL query against multiple SQL server instances and writes the info to a .csv file. The only problem I am having is writing a message saying " ALL SUCCESS" on the first column whenever there are no results returned against a SQL server instance. The script just writes the SQL server instance name and goes to the next instance and writes the info to the .csv file. I want to add ALL SUCCESS right next to the SQL server instance name whenever there are no results returned.For a PowerShell pro, this should not take long. May be 30 min or so. Once I hear back from you, I can share my current code.Thanks.
Victor
Expert:  Steve replied 10 months ago.

Hi, Victor. My name is ***** ***** I can help you with your Windows Powershell script. If you can post the script here so I can look at it, I can tell you what you need to change.

Customer: replied 10 months ago.
Great...here is my script:
==================$FileDate = Get-Date -Format "MM-dd-yyyy"
$infile = “E:\PowerShellScripts\SQLServerList.txt”
$outfile = “E:\PowerShellScripts\BackupStatusQuery3_Output_"+$FileDate+".csv”
$header=“{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f "ServerName","StepID","StepName","SQLJobName","RunDateTime","FailureDate","StepDuration","ExecutionStatus","ErrorMessage"
$header | out-file $outfile -Force
$Servers = Get-Content $infile
Foreach ($value in $Servers)
{
"----------------------" >> $outfile
##$Servers >> $outfile
"----------------------" >> $outfile
$con = new-object System.Data.SqlClient.SqlConnection “Server=$value;Pooling=false;Integrated Security=true”
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = “
SELECT DISTINCT T1.server AS [ServerName],
T1.step_id AS [StepId],
T1.step_name AS [StepName],
SUBSTRING(T2.name,1,140) AS [SQLJobName],
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime1',
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS [FailureDate],
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS 'RunDateTime2',
T1.run_duration StepDuration,
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
T1.message AS [ErrorMessage]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status 1
AND T1.step_id != 0
AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)

$reader = $cmd.ExecuteReader()
#Process Query Results
while ($reader.Read())
{
#Retrieve the Columns from SQL Server Query
$ServerName = $Reader.GetValue(0)
$StepId =$Reader.GetValue(1)
$StepName = $Reader.GetValue(2)
$SQLJobName = $Reader.GetValue(3)
$RunDateTime1 = $reader.GetValue(4)
$FailureDate = $Reader.GetValue(5)
##$RunDateTime2 = $Reader.GetValue(6)
$StepDuration = $Reader.GetValue(7)
$ExecutionStatus = $Reader.GetValue(8)
$ErrorMessage = $Reader.GetValue(9)
##$RecoveryModel = $Reader.GetValue(10)
$record = “{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f $ServerName,$StepId,$StepName,$SQLJobName,$RunDateTime1,$FailureDate,$StepDuration,$ExecutionStatus,$ErrorMessage
$record | out-file $outfile -Append}
$Reader.Close();
}
$con.Close()
==============================
Right now, I get the following output in the first column among other columns:
----------
Server1
---------
Info
Info
Info
--------
Server2
--------
Info
Info
Info
And so on.For those SQL server instances where there is no rows returned once the SQL query is executed, I would like to show something as follows:Server3
----------
ALL SUCCESSServer4
----------
and so on.I think, I need to use Catch block, but I am having difficulty in fixing it and I have a deadline to submit. Hence, asking for help.
Thanks.
Victor.
Expert:  Steve replied 10 months ago.

The full script didn't come through but I see it now.

Customer: replied 10 months ago.
Hi Steve,The SQLServerList.txt is a simple text file as follows:Server1
Server2
Server3
Server4and so on.
Sorry, due to security, I may not be able to allow remote connect.Sure, I can pay $39.00 if you are willing to provide a resolution.Thanks.
Victor
Customer: replied 10 months ago.
The SQLServerList.txt has no heading. It just lists out the names of the SQL server instances which is connected through PowerShell script and run the SQL query. Hope, this clarifies.
Victor
Expert:  Steve replied 10 months ago.

Hi Victor. I'm not really sure why you just said that,perhaps it's some automated message from the system. I never asked about a remote connection or if you could pay. I'm looking at it now.

Yes, the other information is OK. At first, the entire script didn't display on the screen, that was the reason for my question.

Expert:  Steve replied 10 months ago.

Have you loaded either of the SQL Server snapins for PowerShell? Specifically, the database cmdlets?

Customer: replied 10 months ago.
But, here I am not using Invoke-Sqlcmd as you can see. I am seeing successful result except that on those servers where there are no sql agent job failures, the output file just shows the servername and goes to the next servername. I just want to write something like IF ($reader -eq $null) write-message "ALL SUCCESS" else run write the actual query result. I think, Catch can be tried. Something like this:
Catch
{
if(!$outfile)
{Write-host -f red $value, ” Could not connect”}
Else
{“{0}”,”`t”,”Could Not Connect” -f $value | out-file $outfile -Append}
}
Customer: replied 10 months ago.
Actually, the word "Could not connect" should be "ALL SUCCESS" to meet my requirement.
Victor
Customer: replied 10 months ago.
I have to take off for 3-4 hours. I will check back later. If you think you can provide a solution, please let me know and you can give me the link to pay. Thanks.
Victor
Expert:  Steve replied 10 months ago.

So you don't want to use that or use the fill method of the data adapter? For both approaches it's very easy to determine if the result set has no rows.

Expert:  Steve replied 10 months ago.

If I understand what you're trying to do, I believe the following should take care of it (refer to this documentation.)

If ($reader.HasRows) {

while ($reader.Read()) {

...

}

} else {

$record = "ALL SUCCESS"

$record | out-file $outfile -Append

}

Customer: replied 9 months ago.
Steve,
I am not sure about data adapter that you are referring to. With invoke-sqlcmd, I had an issue while creating delimited .csv file. If you can give me the code, I can test it.Anyway, the IF statement that you have shown, I should be placing it as follows right?$reader = $cmd.ExecuteReader()#Process Query Resultswhile ($reader.Read())
{
#Retrieve the Columns from SQL Server Query$ServerName = $Reader.GetValue(0)
$StepId =$Reader.GetValue(1)
$StepName = $Reader.GetValue(2)
$SQLJobName = $Reader.GetValue(3)
$RunDateTime1 = $reader.GetValue(4)
$FailureDate = $Reader.GetValue(5)
##$RunDateTime2 = $Reader.GetValue(6)
$StepDuration = $Reader.GetValue(7)
$ExecutionStatus = $Reader.GetValue(8)
$ErrorMessage = $Reader.GetValue(9)
##$RecoveryModel = $Reader.GetValue(10)$record = “{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f $ServerName,$StepId,$StepName,$SQLJobName,$RunDateTime1,$FailureDate,$StepDuration,$ExecutionStatus,$ErrorMessage
$record | out-file $outfile -Append}} else {$record = "ALL SUCCESS"$record | out-file $outfile -Append$Reader.Close();
}
$con.Close()
Expert:  Steve replied 9 months ago.

Hi, Victor. I don't see the If statement in what you posted? I would put it immediately following the $reader=cmd.Executereader() statement.

Expert:  Steve replied 9 months ago.

$reader = $cmd.ExecuteReader()

if ($reader.hasRows()) {

#Process Query Results

while ($reader.Read())
{
#Retrieve the Columns from SQL Server Query

$ServerName = $Reader.GetValue(0)
$StepId =$Reader.GetValue(1)
$StepName = $Reader.GetValue(2)
$SQLJobName = $Reader.GetValue(3)
$RunDateTime1 = $reader.GetValue(4)
$FailureDate = $Reader.GetValue(5)
##$RunDateTime2 = $Reader.GetValue(6)
$StepDuration = $Reader.GetValue(7)
$ExecutionStatus = $Reader.GetValue(8)
$ErrorMessage = $Reader.GetValue(9)
##$RecoveryModel = $Reader.GetValue(10)

$record = “{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f $ServerName,$StepId,$StepName,$SQLJobName,$RunDateTime1,$FailureDate,$StepDuration,$ExecutionStatus,$ErrorMessage

$record | out-file $outfile -Append}

}

else {

record = "ALL SUCCESS"

$record | out-file $outfile -Append

}

Expert:  Steve replied 9 months ago.

sorry, $record = "ALL SUCCESS"

Expert:  Steve replied 9 months ago.

Any luck?

Customer: replied 9 months ago.
OK...I just now changed the code as follows:
$reader = $cmd.ExecuteReader()#Process Query Results
if ($reader.hasRows()) {while ($reader.Read()){
#Retrieve the Columns from SQL Server Query$ServerName = $Reader.GetValue(0)
$StepId =$Reader.GetValue(1)
$StepName = $Reader.GetValue(2)
$SQLJobName = $Reader.GetValue(3)
$RunDateTime1 = $reader.GetValue(4)
$FailureDate = $Reader.GetValue(5)
##$RunDateTime2 = $Reader.GetValue(6)
$StepDuration = $Reader.GetValue(7)
$ExecutionStatus = $Reader.GetValue(8)
$ErrorMessage = $Reader.GetValue(9)
##$RecoveryModel = $Reader.GetValue(10)
$record = “{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f $ServerName,$StepId,$StepName,$SQLJobName,$RunDateTime1,$FailureDate,$StepDuration,$ExecutionStatus,$ErrorMessage
$record | out-file $outfile -Append}
} else {$record = "ALL SUCCESS"
$record | out-file $outfile -Append
}
$Reader.Close();
}
$con.Close()
================================
I am getting following error:
PS E:\PowerShellScripts> E:\PowerShellScripts\NewPSScript4.ps1
Method invocation failed because [System.Data.Common.DataRecordInternal] does not contain a method named 'hasRows'.
At E:\PowerShellScripts\NewPSScript4.ps1:56 char:8
+ if ($reader.hasRows()) {
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Basically, it is not running the SQL script at all. It is showing the following in the output file:----------------------
Server1
----------------------
----------------------
Server2
----------------------
----------------------
Server3
----------------------
----------------------
Customer: replied 9 months ago.
As you can see it is not writing anything even where the SQL query does have results.
Customer: replied 9 months ago.
I made small change to the if statement and it is correctly looking at the condition as far as ALL SUCCESS message is concerned (where there is no data returned after SQL query execution). However, it is not appending the results of the sql query where there is data which is an issue. Here is the script (partial script):
Foreach ($value in $Servers)
{"----------------------" >> $outfile
$value >> $outfile
"----------------------" >> $outfile$con = new-object System.Data.SqlClient.SqlConnection “Server=$value;Pooling=false;Integrated Security=true”
$con.Open()$cmd = $con.CreateCommand()
$cmd.CommandText = “
SELECT DISTINCT T1.server AS [ServerName],
T1.step_id AS [StepId],
T1.step_name AS [StepName],
SUBSTRING(T2.name,1,140) AS [SQLJobName],
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime1',
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS [FailureDate],
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS 'RunDateTime2',
T1.run_duration StepDuration,
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
T1.message AS [ErrorMessage]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status <> 1
AND T1.step_id != 0
AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)

$reader = $cmd.ExecuteReader()#Process Query Results
if ($reader -ne $null) {while ($reader.Read()){
#Retrieve the Columns from SQL Server Query$ServerName = $Reader.GetValue(0)
$StepId =$Reader.GetValue(1)
$StepName = $Reader.GetValue(2)
$SQLJobName = $Reader.GetValue(3)
$RunDateTime1 = $reader.GetValue(4)
$FailureDate = $Reader.GetValue(5)
##$RunDateTime2 = $Reader.GetValue(6)
$StepDuration = $Reader.GetValue(7)
$ExecutionStatus = $Reader.GetValue(8)
$ErrorMessage = $Reader.GetValue(9)
##$RecoveryModel = $Reader.GetValue(10)
$record = “{0}”,”`t”,”{1}”,”`t”,”{2}”,”`t”,”{3}”,”`t”,”{4}”,”`t”,”{5}”,”`t”,”{6}”,”`t”,”{7}”,”`t”,”{8}” -f $ServerName,$StepId,$StepName,$SQLJobName,$RunDateTime1,$FailureDate,$StepDuration,$ExecutionStatus,$ErrorMessage
$record | out-file $outfile -Append}
} else {$record = "ALL SUCCESS" | out-file $outfile -Append
#$records | out-file $outfile -Append
}
$Reader.Close();
}
$con.Close()
Expert:  Steve replied 9 months ago.

Hi there. This showed in my status that I was waiting on you, but it appears that perhaps you were waiting on me? Is this resolved or is it too late (I think you had a same-day deadline?)

Customer: replied 9 months ago.
No need for help at this time. Thanks.
Victor

Related Programming Questions