Powershell script errror

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: (:slight_smile: [], 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:

$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)
Remove-Item $xlsFile


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

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.

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.

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.