0

How different should the programming be when you execute a powershell script from SQL Server Agent. I have been seeing very weird behavior.

  1. Any object call fails
  2. Can't we use powershell functions in these scripts. The parameters go empty if we call function through an object parameter
  3. Some commands just don't pring messages, even though I use a variable hard print or I use Write-Output.

I just wanted to know why this is too different. I have a big script that automated and helped big manual task, which works with no errors at all when done manually.

Please help me on this.
Object: $agobj = $PrimarySQLConnObj.AvailabilityGroups[$AGName] Error from Agent History: The corresponding line is ' Add-SqlAvailabilityDatabase -InputObject $agobj -Database "$db" -ErrorAction Stop '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot bind parameter 'InputObject'. Cannot convert the "[AG]" value of type "Microsoft.SqlServer.Management.Smo.AvailabilityGroup" to type "Microsoft.SqlServer.Management.Smo.AvailabilityGroup"

Adarsh
  • 52
  • 7
  • What are you trying to accomplish? – Maximilian Burszley May 22 '18 at 02:36
  • I have written a script to detect any new databases created on a SQL Server instance and add them to Availability Group. The script is super smooth while I run manually, but, gives me so many issues while I run on Agent. I definitely feel, its too incompatible to run through agent. I have tried calling the script on filesystem and running the powershell script directly through the agent. To confirm, I could verify the PS version used by the agent too, which is not an issue. – Adarsh May 22 '18 at 02:43
  • Have you considered a scheduled task with some sort of serialization to handle state for your needs? – Maximilian Burszley May 22 '18 at 02:45
  • First thing to try is create a SQL Agent proxy account that is _your_ account and run it under that to see if it solves any issues – Nick.Mc May 22 '18 at 02:56
  • The error doesn't look security related, so the next thing to do is dump parameter and variables values and make sure they are what you think they are – Nick.Mc May 22 '18 at 02:57

0 Answers0