December 7, 2009 at 3:31 pm
I am creating a trace file for all SP that execute on my server. A new file is created daily. That part works. I am trying to load them into a table using this command.
insert INTO StoredProcedures
SELECT EventClass,
ApplicationName,
NTUserName,
LoginName,
CPU,
Reads,
Writes,
Duration,
SPID,
StartTime,
EndTime,
DatabaseID,
DatabaseName,
Error,
HostName,
IsSystem,
ObjectName,
RowCounts,
BinaryData
FROM ::fn_trace_gettable('\\xxx.xxx.xxx.xxx\F$\trace files\sp_trace4122009.trc', default)
I get this error
Msg 567, Level 16, State 7, Line 1
File '\\xxx.xxx.xxx.xxx\F$\trace files\sp_trace4122009.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
I am running sql 2008 enterprise on Windows server 2008 enterprise.
I first thought it was a permissions error, but I have opened the folder and file up to everybody, domain user, and the user account of the sql service to full rights.
anybody got a thought on why I am not able to load the file?
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
December 7, 2009 at 7:41 pm
The file could be corrupted. Have you tried opening it with profiler? If it opens there it is unlikely that it is corrupted.
I did notice you are using a default share, is the user trying to access the file (in this case SQL) in the administrators group on the machine? Better question, does SQL login as a user or is it something like LocalSystem or Network Service?
CEWII
December 8, 2009 at 9:06 am
I am using a UNC Path because the examples all used them. I have also tried f:\trace files\sp_trace4122009.trc and F$\trace files\sp_trace4122009.trc to go directly to the file. The file is on the same machine.
I can open the file in Profiler it comes up just fine.
My sql server service is using a domain account. I have given the file and the folder full permissions for that user. I then gave that folder open permissions for everyone and domain users to test out the permissions. I am willing to try almost anything along the permissions line that is suggested.
I have tried using sa and setting this up in a sql job, both with the same result.
I hope this answers you questions and please ask more if you need more info.
John
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
December 8, 2009 at 9:28 am
Ok, so let me make sure I understand.
1. Using UNC but direct references (ie: F:\dir\file.trc) also fail.
2. Opens ok in profiler.
3. SQL/Agent login as domain account.
4. File and directory have wide open permissions.
5. Tried it as a job but no good.
What all that tells me is that it is unlikely to be a corrupted file (#2). But I'm wondering if you have tried a direct reference (F:\dir\file.trc) before or after #4. The reason I ask is that there could have been a permissions issue but was solved. Not sure, that might be barking up wrong tree. If you are using UNC I wouldn't use the default shares (C$, D$, etc.) because they are limited to what groups can access them and they can't be changed.
I would verify the filename, use a direct reference, verify the permissions on the file, and simplify the query, such as:
SELECT * FROM ::fn_trace_gettable('F:\trace files\sp_trace4122009.trc', 1)What I'm trying to do here is take as many variables out of the equation as possible. A file named sp_trace4122009.trc must exist at F:\trace files\ and have rights to be read either by Everyone or at least by the domain user that SQL logs in as. You are also only asking for a single file and all fields.
You can verify what login account SQL is using by executing:
EXEC master.dbo.xp_cmdshell 'SET'In the output you should see USERDOMAIN= and USERNAME= they will show you conclusively what account is being used.
CEWII
December 8, 2009 at 10:05 am
I think you are seeing everything correct.
I have ran this statement
EXEC master.dbo.xp_cmdshell 'SET' and verified it is running as the user I thought it was.
I then ran this statement
SELECT * FROM ::fn_trace_gettable('F:\trace files\sp_trace4122009.trc', 1)
and I got this
Msg 567, Level 16, State 8, Line 2
File 'F:\trace files\sp_trace4122009.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
I have run all this in the past 5 minutes and still no luck.
Has sql 2008 changed something in the way it loads trace files?
Is there a switch in SQL that turns this ability on? I have not heard of one or came across one in my research, but that does not mean it is not out there.
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
December 8, 2009 at 12:32 pm
I'm not aware of a switch either.
The only thing I can think of is the most obvious to check. The file does exist with that name..
You have checked everything I would.. I'm sorry I don't have any more ideas.
I'm sure it is something so obvious and easy that we both just can't see it..
CEWII
December 8, 2009 at 3:22 pm
OK after racking my brain for about a week I figured out the problem. I did a DIR in dos only to discover that the trace program adds .trc to the already .trc making the file sp_trace4122009.trc.trc. In windows in only showed one .trc even when you rename it. Thank you DOS for showing the truth.
You were right it was to simple of a problem.
Problem solved.
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
December 8, 2009 at 3:55 pm
Glad you figured it out, I was plain out of ideas..
CEWII
February 5, 2016 at 8:16 am
I do have same issue and it says "is not a recognizable trace file", I did check with DIR on DOS prompt and it shows only .trc one time.
Any other idea why its causing to run the query. I have tried on local domain and on server but it seems same issue.
Any help?
May 9, 2016 at 8:32 pm
Verify that the name not have two ".trc" extensions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply