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