SQL Profiler

Question

SQL Profiler

Telammica

Ten Centuries

Points: 1288
More actions
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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Telammica Ten Centuries Points: 1288 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Telammica Ten Centuries Points: 1288 More actions · Answer 4

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.