Sql-server – SQL Server Agent Job – Unable to open Step output file. The step succeeded

restoresql serversql-server-2017sql-server-agent

I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to:

$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Many other jobs on the same server are using the exact same output location (verified exact character match). This job is owned and being executed as the same user as the jobs which are successfully creating output files.

The job is performing a database restore of a user database with the job config pointed at the master database. This is the only job pointing at master, but I would be surprised if that made the difference.

Any thoughts?

Best Answer

It was recommended that I review the job name for any characters that would resolve to a bad windows file path since I am using a variablized path. This ended up being due to a '/' in the SQL Server Agent Job name.

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

Sql-server – How to succesfully run a batch file in an SQL Agent job

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.

Best Answer

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Sql-server – How to succesfully run a batch file in an SQL Agent job

Related Question