cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
701
Views
0
Helpful
8
Replies
Highlighted
Beginner

Powershell not working as intended from tidal

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!

8 REPLIES 8
Highlighted

Sometimes it depends on how

Sometimes it depends on how the PS script is being called. There are some windows security models that we need to handle when calling PS scripts from Tidal

Highlighted
Beginner

Thanks for your reply!

Thanks for your reply!

Please see the screen shot of the job (Configured) attached. The powershell is working fine when I run through central server(windows server 2012).However, through tidal it's generating empty excel file without any data.
 Please advise!

Highlighted

Ok. The execution policy

Ok. The execution policy setting looks alright to me. 

Few more things that I would verify :

- are you using the right version of the powershell(32 bit/64 bit) on both instances ?

- the user id under which the Tidal agent runs, is that different from the one that runs the PS manually ?

- on the server, have you created a batch script, which calls the powershell within it ? such as powershell.exe -ExecutionPolicy bypass -File script.ps1

Highlighted
Beginner

1. I am using the 64 bit

1. I am using the 64 bit version of the powershell on both instances.
2. The user_id under which the Tidal agent is running is the same as for the PS when run manually.
3. I also created the bat script for calling power shell. This is still not giving me desired results. Please find attached screen shot of bat Job
Please advise.

Highlighted

By logging in as the same

By logging in as the same runtime user, are you able to run test.bat from command line and it works correctly ?

Highlighted
Beginner

From command line or manually

From command line or manually it runs fine. Only problem is with Tidal where the script is generating empty excel.

Thanks!

Highlighted

The server on which the agent

The server on which the agent is running, is MS Excel installed ? I am guessing yes from your comments.

Can you please try the suggestion from the posts below

You have to create a folder (or two on a 64bit-windows):

(32Bit, always)

C:\Windows\System32\config\systemprofile\Desktop

(64Bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop

http://stackoverflow.com/questions/11007350/powershell-and-excel-issue-when-automating

https://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object?forum=winserverpowershell

Highlighted
Beginner

Thanks for the suggestions.

Thanks for the suggestions. Lack of credentials took me longer time to create the following folders

C:\Windows\System32\config\systemprofile\Desktop

C:\Windows\SysWOW64\config\systemprofile\Desktop

as advised by you. However, tidal is still generating blank excel.

Please note if I run the powershell script directly after logging in under same user, it works as intended.

Thanks!

CreatePlease to create content
Content for Community-Ad

Cisco COVID-19 Survey

This widget could not be displayed.