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 runs from powershell directly works as expected.
However when the same script is scheduled in tidal, it generates empty excel file without any data.
Do you know why this is happening? Looks like either it is not probably using ODBC properly or not able to handle excel functionality.
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);
};
--------------------------------------
Appreciate your help in this regard!
