Post reply

SQL Profiler

  • June 24, 2008 at 3:03 am

    #187493

    Hi

    I am trying to save all the SQL text from profiler into a table. It is simple to save the profiler table either to file or to table, but what I am looking for is to save more than 256 chars in the table for the TextData (SQL text). There are 2 windows, if you click on the top window it shows the full text in the bottom one, which is what I want stored.

    Anyone got any ideas?

  • June 24, 2008 at 5:22 am

    #833227

    If you save the profile trace to a file or table, the entire textdata is saved. If saving to a table, the column gets defined as type Text.

    Are you only seeing 256 characters in query analyser? If so, check the value of the option 'Max Characters per column' (Options ->Results). It defaults to 256.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 24, 2008 at 5:35 am

    #833238

    Maybe its because Im working on SQL 2000? I cannot find that setting. In the trace properties there is only Save to table, and 'set maximum rows'. Yes, in query analyzer only save 256 chars. The type of the column in the table is ntext.

  • June 24, 2008 at 5:40 am

    #833243

    I've worked extensivly with profiler in 2000 and 2005. It doesn't limit column width in either version.

    Run the following query (substituting the correct table name) and see what it returns.

    SELECT DATALENGTH(TextData) FROM NameOfTheTableStoringTheProfile

    Re the setting that I mentioned. In Query Analyser, go to the tools menu, choose options. Go to the Results tab. Check the value of the 'Max Characters per column'

    If it's 256, set it to something like 8000

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 24, 2008 at 5:47 am

    #833246

    Thanks a lot! I looked at the wrong place to set the result length. I was looking in profiler options, not in query analyzer. It does show the whole text now.

    Thanks again.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply