Help needed with SQL Server Agent Job and PowerShell script issue
I’m stuck with a problem. We’ve got this PowerShell script that grabs report data from an API. It uses Invoke-Sqlcmd to connect to our database and run a query.
Here’s the weird part: when I run the script directly on the server using PowerShell, everything works fine. The data shows up in the table like it should. But when I try to run it as a SQL Server Agent job, it’s a different story.
The job finishes and says it’s successful, but the table stays empty. No data at all!
We’re using a SQLProxy account for the Agent job.
I’m scratching my head here. Any ideas what could be going wrong? Has anyone run into something similar before? I’d really appreciate any tips or suggestions to get this working properly.
hmmm, that’s a tricky one! have you checked the job’s execution context? maybe the powershell script isn’t finding the right path or credentials when run as a job. could you try adding some logging to the script to see where it might be failing? also, is the SQLProxy account has the necessary permissions to execute the script and write to the table? just some thoughts to explore!
hey finn, sounds frustrating! have u checked if the job is actually running the script? maybe add some error logging or Write-Output statements in ur ps script to see whats happening. also, double-check if SQLProxy has proper permissions for both script execution and db writes. good luck!
I’ve encountered similar issues before. One often overlooked aspect is the execution context of the SQL Server Agent job. It might not have access to the same environment variables or network resources as your user account. Try using fully qualified paths in your script and ensure all necessary modules are available system-wide.
Additionally, check the SQL Server Agent service account. It needs appropriate permissions to execute PowerShell scripts and interact with the API. You might want to temporarily grant it elevated permissions for troubleshooting.
Lastly, implement detailed logging in your script. Output each step to a file, including API responses and SQL query results. This should help pinpoint where exactly the process is breaking down when run as a job.