cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1375
Views
0
Helpful
8
Replies

Powershell not working as intended from tidal

shsharma
Beginner
Beginner

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

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

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!

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

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.

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

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

Thanks!

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

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!

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers