-1

I have a SQL Server 2008 SP3, I have an agent job that ran successfully for last the month, however as of yesterday it keeps failing error is below.

Executed as user: Domain\Account. File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file. [SQLSTATE 42000] (Error 567). The step failed.

Here is the error message from log to table & included step output in history Msg 567, Sev 16, State 5, Line 23:

File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file. [SQLSTATE 42000]

DECLARE @trc_path VARCHAR(500), @message VARCHAR(MAX), @message1 VARCHAR(MAX), @textdata VARCHAR(1000)
SELECT @trc_path=CONVERT(VARCHAR(500),value)
FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

INSERT INTO #temp_cfg SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime, EventSeq‌​uence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%' AND
      SPID<>@@spid AND
      fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes) AND
      TEXTData NOT LIKE '%Insert into #temp_cfg%'
ORDER BY StartTime DESC
  • You might check Anthony Geoghegan's answer at dba.stackexchange.com/questions/43539/… – RLF Feb 19 '16 at 16:20
  • 1
    trc_path is being inserted into temp table not actual file or directory – James Youkhanis Feb 19 '16 at 16:45
  • The way I read the error, something is trying to write to a file. Look through all the error logs available to you (e.g. SQL Server, Windows, etc.). Add an error output file to the agent step. Outside of that we need to know what the job or that step is doing. – Shawn Melton Feb 19 '16 at 16:51
0

Your function, fn_trace_gettable() is not able to open the file you're passing to it with the @trc_path variable. I don't know if you redacted the filename, or if it actually has an empty string in single quotes, which would mean the variable was empty. You probably should debug and check the results from fn_trace_getinfo().

Also note, these functions are deprecated and it's recommended to use Extended Events instead. sys.fn_trace_gettable (Transact-SQL)

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.