My idea is to insert records from a table into another one in different server using T-SQL.
I've made maintenance plan along with its job and found below error.
Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user
'MANYAR\MIT-DB2$'. [SQLSTATE 28000] (Error 18456)Unable to open Step
output file. The step failed.
I suspect SQLSERVERAGENT from source server might not have permission in destination server. I've tried to execute the T-SQL script in query and the scripts work well.
Best Answer
I believe you are seeing two different errors on your history report.
Recreating the step output error
Testing with a location that does not exist
Create a jobstep that tries to access a step output folder location that does not exist
Regular T-SQL job steps might report the error but not fail the job, I used a maintenance plan to force the failure here
Start the job
Resulting error:
Testing with insufficient permissions
The same error is returned when no permissions are granted
Recreating the linked server permission error in combination with the step output error
Create a linked server
Authenticate with a non existing login
Create a job and jobstep to select from the newly created linked server
Point to a non existing output file
With the final resulting error on job execution matching yours
Validate the job step output & the linked server permissions and authentication and you should be good to go.