Powershell script errror

Welcome Forums General PowerShell Q&A Powershell script errror

This topic contains 4 replies, has 4 voices, and was last updated by

 
Participant
3 months, 3 weeks ago.

  • Author
    Posts
  • #110056

    Participant
    Points: 0
    Rank: Member

    Hello-
    I have a powershell script which connects to database using ODBC connection string and produces a excel output.
    This excel output is then emailed to desired recipients by the script.
    The script when run from powershell directly was working earlier as expected.
    However, for the past few months, the script is running with below error:

    Exception calling "Fill" with "1" argument(s): "ERROR [HYT00] Timeout expired"
    At D:report_name.ps1:87 char:2
    +  $count = $da.fill($DataSetTable)
    +  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OdbcException

    Can you please guide me on how can I fix the error?

     

    e.g. I have been using following at high level:
    ————————————–
    $SQL=@"SELECT * FROM TABLE"@
    $DSN="TEST"
    $connectstring = "DSN=$DSN"
    $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
    $conn.open()
    $cmd = New-Object system.Data.Odbc.OdbcCommand($SQL,$conn)
    $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
    $DataSetTable = New-Object system.Data.datatable
    $null = $da.fill($DataSetTable)
    $conn.close()

    ## ———- Working with Excel ———- ##

    ## – Create an Excel Application instance:
    $xlsObj = New-Object -ComObject Excel.Application;
    #$xlsObj = New-Object -Com Excel.Application;

    ## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
    $xlsObj.Visible = 0;
    $xlsWb = $xlsobj.Workbooks.Add();
    $xlsSh = $xlsWb.Worksheets.item(1);

    ## – Build the Excel column heading:
    [Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

    ## – Build column header:
    [Int] $RowHeader = 1;
    foreach ($ColH in $getColumnNames)
    {
    $xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
    $xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
    $RowHeader++;
    };

    ## – Adding the data start in row 2 column 1:
    [Int] $rowData = 2;
    [Int] $colData = 1;

    foreach ($rec in $DataSetTable.Rows)
    {
    foreach ($Coln in $getColumnNames)
    {
    ## – Next line convert cell to be text only:
    $xlsSh.Cells.NumberFormat = "@";

    ## – Populating columns:
    $xlsSh.Cells.Item($rowData, $colData) = `
    $rec.$($Coln.ColumnName).ToString();
    $ColData++;
    };
    $rowData++; $ColData = 1;
    };
    #Create a Excel file to save the data
    # if the directory doesn't exist, then create it

    if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
    {
    New-Item "$DirectoryToSave" -type directory | out-null
    }

    ## – Adjusting columns in the Excel sheet:
    $xlsRng = $xlsSH.usedRange;
    $xlsRng.EntireColumn.AutoFit();

    ## ———- Saving file and Terminating Excel Application ———- ##

    ## – Saving Excel file – if the file exist do delete then save
    $xlsFile = "$DirectoryToSave$filename.xls"

    if (Test-Path $xlsFile)
    {
    Remove-Item $xlsFile
    $xlsObj.ActiveWorkbook.SaveAs($xlsFile);
    }
    else
    {
    $xlsObj.ActiveWorkbook.SaveAs($xlsFile);
    };
    ————————————–

    Thanks,
    Shami

  • #110059

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    Hi,  I request you to format the code in the forum which makes other to easily understand your code, below link will help you.

  • #110066

    Participant
    Points: 351
    Helping Hand
    Rank: Contributor

    Looks like you are running into a timeout issue. Per the documentation, you have a 30 second timeout, so you can attempt to manipulate the CommandTimeout:

    $cmd = New-Object system.Data.Odbc.OdbcCommand($SQL,$conn)
    $cmd.CommandTimeout = '300'
    

    The documentation also states the value is in seconds, so this would be a 5 minute timeout.

  • #110099

    Participant
    Points: 332
    Helping Hand
    Rank: Contributor

    Rob, is on track with the timeout thing, and manipulating the time out could help, but from what you describe, this timeout setting may be just a band aid, as the real issue can be network related or SQL host related.

    You, need to evaluate infrastructure (network congestion) / operations (firewall – IDS – IPS change) / host changes (or resource consumptions / over commits) for root cause as needed before trying o determine how much of this timeout you may or may need to address.

  • #110138

    Participant
    Points: 0
    Rank: Member

    Thank you Rob and Postanote!

    The script ran today without the timeout error.

    Going forward if I face the same issue again, I will follow up with the network team to gather timeout stats.

     

     

     

     

The topic ‘Powershell script errror’ is closed to new replies.