This topic contains 4 replies, has 4 voices, and was last updated by
August 27, 2018 at 4:36 pm #110056ParticipantPoints: 0Rank: Member
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"@
$connectstring = "DSN=$DSN"
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$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)
## ———- 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;
## – 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) = `
$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;
## ———- 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)
August 27, 2018 at 4:39 pm #110059
August 27, 2018 at 6:36 pm #110066ParticipantPoints: 639Rank: Major 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.
August 27, 2018 at 8:54 pm #110099ParticipantPoints: 1,154Rank: Community Hero
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.
August 28, 2018 at 12:42 pm #110138ParticipantPoints: 0Rank: 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.