>it was successfully finished. ____________________________________ then try following: SELECT textdata,applicationname,loginname,SPID,starttime FROM ::fn_trace_gettable('\\Server2\ind_act\t11.trc', default) Server: Msg 567, Level 16, State 2, Line 1 File '\\Server2\ind_act\t11.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file. ________________________ why? what's wrong with my select statement? another question: how can I stop my trace activities if I didn't define the stop time in it's definition coding like above? any help would be gratly appreciated, -- Using M2, Opera's revolutionary e-mail client: [url]http://www.opera.com/m2/[/url] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => M [ip] => mary2001@canada [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) -->>it was successfully finished. >____________________________________ >then try following: > >SELECT textdata,applicationname,loginname,SPID,starttime[/quote] FROM[quote] >::fn_trace_gettable('\\Server2\ind_act\t11.trc', default) > >Server: Msg 567, Level 16, State 2, Line 1 >File '\\Server2\ind_act\t11.trc' either does not exist or[/quote] is not a[quote] >recognizable trace file. Or there was an error opening[/quote] the file.[quote] > >________________________ > >why? what's wrong with my select statement? >another question: how can I stop my trace activities >if I didn't define the stop time in it's definition >coding like above? > >any help would be gratly appreciated, > >-- >Using M2, Opera's revolutionary e-mail client:[/quote] [url]http://www.opera.com/m2/[/url][quote] >. >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <036701c3451a$2a201e00$a001280a@phx.gbl> [ref] => [htmlstate] => on_nl2br [postusername] => Greg Linwood [ip] => g_linwood@hotma [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) -->>it was successfully finished. > ____________________________________ > then try following: > > SELECT textdata,applicationname,loginname,SPID,starttime FROM > ::fn_trace_gettable('\\Server2\ind_act\t11.trc', default) > > Server: Msg 567, Level 16, State 2, Line 1 > File '\\Server2\ind_act\t11.trc' either does not exist or is not a > recognizable trace file. Or there was an error opening the file. > > ________________________ > > why? what's wrong with my select statement? > another question: how can I stop my trace activities > if I didn't define the stop time in it's definition > coding like above? > > any help would be gratly appreciated, > > -- > Using M2, Opera's revolutionary e-mail client: [url]http://www.opera.com/m2/[/url][/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Jacco Schalkwijk [ip] => NOSPAMjaccos@eu [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> difiiculties with ::fn_trace_gettable() - Microsoft SQL / MS SQL Server

difiiculties with ::fn_trace_gettable() - Microsoft SQL / MS SQL Server

Hi here is a sample trace I defined on SQL server 2000 and want to visit the result from it's .trc file: /************************************************** **/ /* Created by: SQL Profiler */ /* Date: 08/07/2003 10:28:12 AM */ /************************************************** **/ -- Create a Queue declare rc int declare TraceID int declare maxfilesize bigint declare DateTime datetime set DateTime = '2003-07-09 12:26:40.000' set maxfilesize = 5 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to ...

  1. #1

    Default difiiculties with ::fn_trace_gettable()

    Hi
    here is a sample trace I defined on SQL server 2000
    and want to visit the result from it's .trc file:


    /************************************************** **/
    /* Created by: SQL Profiler */
    /* Date: 08/07/2003 10:28:12 AM */
    /************************************************** **/


    -- Create a Queue
    declare rc int
    declare TraceID int
    declare maxfilesize bigint
    declare DateTime datetime

    set DateTime = '2003-07-09 12:26:40.000'
    set maxfilesize = 5

    -- Please replace the text InsertFileNameHere, with an appropriate
    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
    extension
    -- will be appended to the filename automatically. If you are writing from
    -- remote server to local drive, please use UNC path and make sure server
    has
    -- write access to your network share

    exec rc = sp_trace_create TraceID output, 0, N'\\Server2\ind_act\t11',
    maxfilesize, Datetime
    if (rc != 0) goto error

    -- Client side File and Table cannot be scripted

    -- Set the events
    declare on bit
    set on = 1
    exec sp_trace_setevent TraceID, 10, 1, on
    exec sp_trace_setevent TraceID, 10, 6, on
    exec sp_trace_setevent TraceID, 10, 9, on
    exec sp_trace_setevent TraceID, 10, 10, on
    exec sp_trace_setevent TraceID, 10, 11, on
    exec sp_trace_setevent TraceID, 10, 12, on
    exec sp_trace_setevent TraceID, 10, 14, on
    exec sp_trace_setevent TraceID, 12, 1, on
    exec sp_trace_setevent TraceID, 12, 6, on
    exec sp_trace_setevent TraceID, 12, 9, on
    exec sp_trace_setevent TraceID, 12, 10, on
    exec sp_trace_setevent TraceID, 12, 11, on
    exec sp_trace_setevent TraceID, 12, 12, on
    exec sp_trace_setevent TraceID, 12, 14, on
    exec sp_trace_setevent TraceID, 14, 1, on
    exec sp_trace_setevent TraceID, 14, 6, on
    exec sp_trace_setevent TraceID, 14, 9, on
    exec sp_trace_setevent TraceID, 14, 10, on
    exec sp_trace_setevent TraceID, 14, 11, on
    exec sp_trace_setevent TraceID, 14, 12, on
    exec sp_trace_setevent TraceID, 14, 14, on
    exec sp_trace_setevent TraceID, 15, 1, on
    exec sp_trace_setevent TraceID, 15, 6, on
    exec sp_trace_setevent TraceID, 15, 9, on
    exec sp_trace_setevent TraceID, 15, 10, on
    exec sp_trace_setevent TraceID, 15, 11, on
    exec sp_trace_setevent TraceID, 15, 12, on
    exec sp_trace_setevent TraceID, 15, 14, on


    -- Set the Filters
    declare intfilter int
    declare bigintfilter bigint

    exec sp_trace_setfilter TraceID, 6, 0, 7, N'sqladmin'
    exec sp_trace_setfilter TraceID, 10, 0, 7, N'SQL Profiler'


    -- Set the trace status to start
    exec sp_trace_setstatus TraceID, 1

    -- display trace id for future references
    select TraceID=TraceID
    goto finish

    error: select ErrorCode=rc

    finish: go

    /***************************************

    -->>it was successfully finished.
    ____________________________________
    then try following:

    SELECT textdata,applicationname,loginname,SPID,starttime FROM
    ::fn_trace_gettable('\\Server2\ind_act\t11.trc', default)

    Server: Msg 567, Level 16, State 2, Line 1
    File '\\Server2\ind_act\t11.trc' either does not exist or is not a
    recognizable trace file. Or there was an error opening the file.

    ________________________

    why? what's wrong with my select statement?
    another question: how can I stop my trace activities
    if I didn't define the stop time in it's definition
    coding like above?

    any help would be gratly appreciated,

    --
    Using M2, Opera's revolutionary e-mail client: [url]http://www.opera.com/m2/[/url]
    M Guest

  2. #2

    Default difiiculties with ::fn_trace_gettable()

    SQL Server locks the .trc file whilst it is running the
    trace, so you've got to stop the trace first to use the
    trace output file (.trc).

    To do this, you use sp_trace_setstatus, eg:

    exec sp_trace_setstatus TraceID, 0

    Before doing that though, you need to get the trace id for
    the TraceID parameter. You can get this from:

    SELECT * FROM :: fn_trace_getinfo(default)

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP
    >-----Original Message-----
    >Hi
    >here is a sample trace I defined on SQL server 2000
    >and want to visit the result from it's .trc file:
    >
    >
    >/************************************************** **/
    >/* Created by: SQL Profiler */
    >/* Date: 08/07/2003 10:28:12 AM */
    >/************************************************** **/
    >
    >
    >-- Create a Queue
    >declare rc int
    >declare TraceID int
    >declare maxfilesize bigint
    >declare DateTime datetime
    >
    >set DateTime = '2003-07-09 12:26:40.000'
    >set maxfilesize = 5
    >
    >-- Please replace the text InsertFileNameHere, with an
    appropriate
    >-- filename prefixed by a path, e.g.,
    c:\MyFolder\MyTrace. The .trc
    >extension
    >-- will be appended to the filename automatically. If you
    are writing from
    >-- remote server to local drive, please use UNC path and
    make sure server
    >has
    >-- write access to your network share
    >
    >exec rc = sp_trace_create TraceID output, 0, N'\\Server2
    \ind_act\t11',
    >maxfilesize, Datetime
    >if (rc != 0) goto error
    >
    >-- Client side File and Table cannot be scripted
    >
    >-- Set the events
    >declare on bit
    >set on = 1
    >exec sp_trace_setevent TraceID, 10, 1, on
    >exec sp_trace_setevent TraceID, 10, 6, on
    >exec sp_trace_setevent TraceID, 10, 9, on
    >exec sp_trace_setevent TraceID, 10, 10, on
    >exec sp_trace_setevent TraceID, 10, 11, on
    >exec sp_trace_setevent TraceID, 10, 12, on
    >exec sp_trace_setevent TraceID, 10, 14, on
    >exec sp_trace_setevent TraceID, 12, 1, on
    >exec sp_trace_setevent TraceID, 12, 6, on
    >exec sp_trace_setevent TraceID, 12, 9, on
    >exec sp_trace_setevent TraceID, 12, 10, on
    >exec sp_trace_setevent TraceID, 12, 11, on
    >exec sp_trace_setevent TraceID, 12, 12, on
    >exec sp_trace_setevent TraceID, 12, 14, on
    >exec sp_trace_setevent TraceID, 14, 1, on
    >exec sp_trace_setevent TraceID, 14, 6, on
    >exec sp_trace_setevent TraceID, 14, 9, on
    >exec sp_trace_setevent TraceID, 14, 10, on
    >exec sp_trace_setevent TraceID, 14, 11, on
    >exec sp_trace_setevent TraceID, 14, 12, on
    >exec sp_trace_setevent TraceID, 14, 14, on
    >exec sp_trace_setevent TraceID, 15, 1, on
    >exec sp_trace_setevent TraceID, 15, 6, on
    >exec sp_trace_setevent TraceID, 15, 9, on
    >exec sp_trace_setevent TraceID, 15, 10, on
    >exec sp_trace_setevent TraceID, 15, 11, on
    >exec sp_trace_setevent TraceID, 15, 12, on
    >exec sp_trace_setevent TraceID, 15, 14, on
    >
    >
    >-- Set the Filters
    >declare intfilter int
    >declare bigintfilter bigint
    >
    >exec sp_trace_setfilter TraceID, 6, 0, 7, N'sqladmin'
    >exec sp_trace_setfilter TraceID, 10, 0, 7, N'SQL
    Profiler'
    >
    >
    >-- Set the trace status to start
    >exec sp_trace_setstatus TraceID, 1
    >
    >-- display trace id for future references
    >select TraceID=TraceID
    >goto finish
    >
    >error: select ErrorCode=rc
    >
    >finish: go
    >
    >/***************************************
    >
    >-->>it was successfully finished.
    >____________________________________
    >then try following:
    >
    >SELECT textdata,applicationname,loginname,SPID,starttime
    FROM
    >::fn_trace_gettable('\\Server2\ind_act\t11.trc' , default)
    >
    >Server: Msg 567, Level 16, State 2, Line 1
    >File '\\Server2\ind_act\t11.trc' either does not exist or
    is not a
    >recognizable trace file. Or there was an error opening
    the file.
    >
    >________________________
    >
    >why? what's wrong with my select statement?
    >another question: how can I stop my trace activities
    >if I didn't define the stop time in it's definition
    >coding like above?
    >
    >any help would be gratly appreciated,
    >
    >--
    >Using M2, Opera's revolutionary e-mail client:
    [url]http://www.opera.com/m2/[/url]
    >.
    >
    Greg Linwood Guest

  3. #3

    Default Re: difiiculties with ::fn_trace_gettable()

    In addition to Greg's comments:

    If you want to keep your trace running, but also will be able to view the
    results regularly, you can set the second parameter (options) of the
    sp_trace_create to 2, to enable trace file rollover, and change the
    maxfilesize parameter from 5 to 1. This will create a number of files with
    a maximum size of 1 MB instead of 1 file with a size of 5 MB, so you can
    check on the trace in all the files except the one that is currently being
    written to.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "M" <mary2001canada.com> wrote in message
    news:oprry91ng8hu9gohmsnews.microsoft.com...
    > Hi
    > here is a sample trace I defined on SQL server 2000
    > and want to visit the result from it's .trc file:
    >
    >
    > /************************************************** **/
    > /* Created by: SQL Profiler */
    > /* Date: 08/07/2003 10:28:12 AM */
    > /************************************************** **/
    >
    >
    > -- Create a Queue
    > declare rc int
    > declare TraceID int
    > declare maxfilesize bigint
    > declare DateTime datetime
    >
    > set DateTime = '2003-07-09 12:26:40.000'
    > set maxfilesize = 5
    >
    > -- Please replace the text InsertFileNameHere, with an appropriate
    > -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
    > extension
    > -- will be appended to the filename automatically. If you are writing from
    > -- remote server to local drive, please use UNC path and make sure server
    > has
    > -- write access to your network share
    >
    > exec rc = sp_trace_create TraceID output, 0, N'\\Server2\ind_act\t11',
    > maxfilesize, Datetime
    > if (rc != 0) goto error
    >
    > -- Client side File and Table cannot be scripted
    >
    > -- Set the events
    > declare on bit
    > set on = 1
    > exec sp_trace_setevent TraceID, 10, 1, on
    > exec sp_trace_setevent TraceID, 10, 6, on
    > exec sp_trace_setevent TraceID, 10, 9, on
    > exec sp_trace_setevent TraceID, 10, 10, on
    > exec sp_trace_setevent TraceID, 10, 11, on
    > exec sp_trace_setevent TraceID, 10, 12, on
    > exec sp_trace_setevent TraceID, 10, 14, on
    > exec sp_trace_setevent TraceID, 12, 1, on
    > exec sp_trace_setevent TraceID, 12, 6, on
    > exec sp_trace_setevent TraceID, 12, 9, on
    > exec sp_trace_setevent TraceID, 12, 10, on
    > exec sp_trace_setevent TraceID, 12, 11, on
    > exec sp_trace_setevent TraceID, 12, 12, on
    > exec sp_trace_setevent TraceID, 12, 14, on
    > exec sp_trace_setevent TraceID, 14, 1, on
    > exec sp_trace_setevent TraceID, 14, 6, on
    > exec sp_trace_setevent TraceID, 14, 9, on
    > exec sp_trace_setevent TraceID, 14, 10, on
    > exec sp_trace_setevent TraceID, 14, 11, on
    > exec sp_trace_setevent TraceID, 14, 12, on
    > exec sp_trace_setevent TraceID, 14, 14, on
    > exec sp_trace_setevent TraceID, 15, 1, on
    > exec sp_trace_setevent TraceID, 15, 6, on
    > exec sp_trace_setevent TraceID, 15, 9, on
    > exec sp_trace_setevent TraceID, 15, 10, on
    > exec sp_trace_setevent TraceID, 15, 11, on
    > exec sp_trace_setevent TraceID, 15, 12, on
    > exec sp_trace_setevent TraceID, 15, 14, on
    >
    >
    > -- Set the Filters
    > declare intfilter int
    > declare bigintfilter bigint
    >
    > exec sp_trace_setfilter TraceID, 6, 0, 7, N'sqladmin'
    > exec sp_trace_setfilter TraceID, 10, 0, 7, N'SQL Profiler'
    >
    >
    > -- Set the trace status to start
    > exec sp_trace_setstatus TraceID, 1
    >
    > -- display trace id for future references
    > select TraceID=TraceID
    > goto finish
    >
    > error: select ErrorCode=rc
    >
    > finish: go
    >
    > /***************************************
    >
    > -->>it was successfully finished.
    > ____________________________________
    > then try following:
    >
    > SELECT textdata,applicationname,loginname,SPID,starttime FROM
    > ::fn_trace_gettable('\\Server2\ind_act\t11.trc', default)
    >
    > Server: Msg 567, Level 16, State 2, Line 1
    > File '\\Server2\ind_act\t11.trc' either does not exist or is not a
    > recognizable trace file. Or there was an error opening the file.
    >
    > ________________________
    >
    > why? what's wrong with my select statement?
    > another question: how can I stop my trace activities
    > if I didn't define the stop time in it's definition
    > coding like above?
    >
    > any help would be gratly appreciated,
    >
    > --
    > Using M2, Opera's revolutionary e-mail client: [url]http://www.opera.com/m2/[/url]

    Jacco Schalkwijk Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •