Red gate SQL Compare error while running using SQL Agent Job

Question

I created a SQL agent job to perform a SQL Compare using the below command line

powershell.exe -File D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.ps1

This below given is the command line code in DEVvsPRJ.ps1 file

set-location "C:\Program Files (x86)\Red Gate\SQL Compare 13";
./sqlcompare /s1:Server1 /db1:Database1/s2:Server2 /db2:Database2/r:"D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.html" /rt:Classic /f

So, when I tried executing the job it succeeded without any errors. But when I checked the location comparison report was not created. So I went back to Job history and found the following error.

======== Copyright Copyright c Red Gate Software Ltd 2019 Unhandled Exception: System.UnauthorizedAccessException: Access to the path is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.Pipes.NamedPipeClientStream.Connect(Int32 timeout) at RedGate.Ipc.NamedPipes.NamedPipeEndpointClient.Connect(Int32 timeoutMs) at RedGate.Ipc.Rpc.RpcConnectionProvider.TryGetConnection(TimeSpan timeout) at RedGate.Ipc.ReconnectingConnectionProvider.ReconnectionWorker() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart(). Process Exit Code 0. The step succeeded.

Can anyone please suggest me a way on how to automate this task or a way through this error.

Answer 1

Since I did not get a proper solution to this using the SQL Agent job, I had to try an alternative.

I used Windows Task Scheduler to run this. I have saved my script as DEVvsPRJ.ps1 and invoked PowerShell using the scheduler and it worked fine.

Running the Powershell script in scheduler solved the problem.