When we run a query and add a COMPUTE clause it runs fine from SSMS.
Run as a Stored Procedure via SQL Agent it fails. This is in the Agent log:
2010-06-04 13:32:20 - ! [LOG] Step 1 of job 'test' (0x4F1D4A3F03FA3148999EE07499A7D7EF) has caused an exception in the TSQL subsystem and has been terminated
This is in the Job History:
Message Executed as user: XXX\SQL_Agent_Service. An exception (0xc0000005) occurred in the TSQL subsystem. The step failed.
There are NULLs in the data. This is on SP3, the behaviour was not observed on SP2. If we comment out the COMPUTE clause it runs fine.
Also noticed is that the output file is locked open and we can't delete it. The Agent is able to overwrite it though. If the job isn't terminated by SQL then the file can be deleted as normal.
The Agent Service account has full control where the file is being created.
Any thoughts out there?