0

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.

  • 2
    The error is quite clear: Access to the path is denied. -- check to see if the directory is writeable by the ultimate user that is executing the script – Philᵀᴹ May 17 at 14:29
  • Why don't you run this as a powershell job instead of adding all these additional layers? – Aaron Bertrand May 17 at 14:32
  • @Philᵀᴹ Yes, the account has full control over the directory. – l.lijith May 17 at 14:37
  • 1
    Which account? Are you sure you checked the account that the SQL Server Agent job is running as? How did you validate this? Is D:\ a mapped drive? Mapped to who? Also you should be able to troubleshoot the error you are getting from powershell even before introducing this to Agent - SQL Server isn't going to make that error go away. – Aaron Bertrand May 17 at 15:30
  • 1
    When you run the same script in PowerShell terminal as you, or when you run the same script in PowerShell terminal as the SQL Agent account? Big difference. What is the SQL Server service / SQL Server Agent service account(s)? How are you running PowerShell terminal as that account? SQL Server doesn't just make up "Access to the path is denied" errors, it's likely because the agent account doesn't have access, and you're not verifying that for that specific account. – Aaron Bertrand May 17 at 19:05
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.