1

I'm trying to run the following code, but PowerShell is not exporting the output correct and getting the following error:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the code:

$SERVERS = gc "C:\Users\listOfServers.txt"

$out = foreach ($SERVER in $SERVERS) { 
    $InvokeParams = @{
        Server = $SERVER
        Database = 'test'
        Username = 'admin'
        Password = 'testpassword'
        InputFile = 'C:\business.sql'
    }
    Invoke-SqlCmd @InvokeParams |
        Select-Object -Property *, @{L='Server';E={$SERVER}}
}

$path = 'C:\Users\test1.csv'
$out | Export-Csv -Path $path 
Invoke-Item -Path $path

Goal is to export the current output to Excel, also ADD a statement for the servers that does not have any output as well.

ValueDate: 4/30/2019 12:00:00 AM
PrevValueDate: 4/29/2019 12:00:00 AM
Count:2100
Server 1  

Server 2 : no errors **NEEDS TO BE ADDED  

ValueDate: 4/30/2019 12:00:00 AM
PrevValueDate: 4/29/2019 12:00:00 AM
Count:100
Server 3

Based on this questions and some helpful answers: Powershell - Separate output based on Server + export to Excel.

Improve this question
6
  • What dose $out look like before you export it and How much servers are in $servers? Do you get the Error for every server or once? Commented Jun 26, 2019 at 13:55
  • looks like the output above , but i still need some assistance that returns a message such as server 2: no errors - 45 total Commented Jun 26, 2019 at 14:01
  • This error happens because the query timeout is exceeded. How long do you expect the queries to take before they return data? On my test system, this happens after 30 seconds. Commented Jun 26, 2019 at 14:14
  • im not really sure, wait until data is in for each server . Try to add : SqlCommand c = new SqlCommand() c.CommandTimeout = 0; somewhere in the code ?? Commented Jun 26, 2019 at 14:18
  • 2
    If you expect a query to take longer than 30 seconds to return data, then you should add the -querytimeout x parameter where x is the number of seconds. Commented Jun 26, 2019 at 14:19

1 Answer 1

Reset to default
1

This should output what you require:

$SERVERS = gc "C:\Users\listOfServers.txt"

$out = foreach ($SERVER in $SERVERS) { 
    $InvokeParams = @{
        Server = $SERVER
        Database = 'test'
        Username = 'admin'
        Password = 'testpassword'
        InputFile = 'C:\business.sql'
        QueryTimeout = 60
    }

    try {
        $queryResults = $null
        $queryResults = Invoke-SqlCmd @InvokeParams -Erroraction stop
        if ($queryResults) {
            $queryResults | Select-Object -Property *, @{L='Server';E={$SERVER}}
        }
        else {
            "" | Select-Object @{L='Server';E={"{0} has no errors" -f $SERVER}}
        }
    } 
    catch {
        "" | Select-Object @{L='Server';E={"{0} produced an Error" -f $SERVER}}
        Continue
    }
}
$path = 'C:\Users\test1.csv'
$out | Export-Csv -Path $path -NoTypeInformation

Explanation:

I added the -Querytimeout parameter inside of your hash table ($InvokeParams) with a value of 60 seconds. You can remove that Querytimeout = 60 line entirely if you are okay with your queries timing out after 30 seconds. You can increase or decrease that value if you know of something more appropriate. The default based on my testing seems to be 30 seconds.

I implemented try-catch blocks and added the required -Erroraction Stop parameter to your Invoke-SqlCmd. If there is a query timeout, the catch block will activate and output the Server property with a value of Servername produced an error. Feel free to edit this however you choose. The Continue statement is key here as it allows you to catch the exception and then continue onto the next item in $Servers. Without Continue, your script would just stop processing.

I added if-else statements to verify if your SQL script outputted any results. If there are results, you return the output your original code expected. If there are errors, then it outputs the Server property with Servername has no errors. Again, feel free to edit that however you choose.

Since we are outputting the script results to CSV, we must output results as objects with matching property names. Because of the nature of the Export-Csv command, you can't just output free text strings with messages. They need to belong to a property of our custom object. "A String" | Export-Csv results in the length of your string because length is the only property that the command reads.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks for your time and explanation. Im running the code at the moment, got the timeout error again , might have to increase it. Once the error appeared, the csv file was generated with a blank result. Ill increase the time know and run it again
Be sure to copy the code as it is now. I added one more statement that may help data output consistency.
Also the $queryResults = $null to wipe out the data it contains on every iteration.
We are almost there and thanks again - the errors are not displayed in the excel sheet. It exported the following: Server 1 has no errors, Server 2 has no errors, Server 3, Server 4 . The errors for 3 and 4 are missing in the spreadsheet
also instead of displaying the error, its displaying the # of errors. Sevrver 5 has 3 errors, on the spreadsheet i see the following : Server 5 , Server 5, Server 5 , one on top of the other
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.