?
Solved

SQL 2005 Profiler

Posted on 2006-05-12
15
Medium Priority
?
697 Views
Last Modified: 2008-01-09
We are having trouble using the SQL 2005 Profiler.  In SQL 2000, when you profield a database you would see every command sent to the database, even if it errored.

In the SQL 2005 profiler, we are only seeing commands once they complete, and not if the error.

Is there any way to modify the new profiler to behave like the old one?  Currently it is of little use to us.
0
Comment
Question by:mrichmon
  • 8
  • 5
14 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 16682367
in the new trace after connecting to sql 2005 profileer
go to event selection tab and there

check box (show all events)

select sql:batchstarting
and sql:stmtstarting

it will give your desired result.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16683202
Sorry but that is not correct.

I already had those checked, and in fact have tried checking others as well.  We are not seeing commands that get sent to the database, but then fail during execution.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16689538
are those commands executed in stored procedure, triggers or functions because if thats the case than you have to select sp:stmtstarting.

the solution mentioned in previous post is correct you can run incorrect sql statement in management studion and check it in sql profiler in the text data column.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Author Comment

by:mrichmon
ID: 16691429
The code is in stored procedures, but the sp:stmtstarting does not achive this either.  We still only see commands for completed ones.

In fact we tried marking ALL of the sp:...  and sql:....  and still could not get the same results as the old one.  WHen it was sucessful we see the command.  If we force a crash/error we do not see it in the profiler.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16694701
Let me clarify a bit.

sp:stmtstarting shows each statement within the stored procedure as it executes WHEN it is successful.  However, all we really care about is the call itself which WHEN sucessful is reported as RPC:Completed.

So, logically we tried the RPC:Starting - hoping that would show us the call even if it did not finish successfully - no such luck.

Now this does not seem to be a problem when the stored procedure raises an error intentionally.  For example, we have a line that tests an incoming parameter and if it is not an allowed value we raise an error - in that case we do see the call in the RPC:Completed - it completed, but with errors.  It is when it crashes for some other reason that we do not see the statement in the profiler.

I wish I could remember the exact situations we had a few weeks ago so I could reproduce.  but it has happened several times on several different computers with several different programmers and databases.  So although I cannot remember at the moment how to reproduce I know it was not a one -time fluke
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16701326
Okay I was able to reproduce one that we cannot see.  We cannot see the call that was sent to the database if the user does not have permission to execute that stored procedure then we don't even see that the command was sent to the database in the profiler.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16724592
this is the reason because in profiler you can see the commands which are sent to database but in your case the user dosen't have the permission thats why u are unnable to see these in the sql profiler.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16735203
The user does have permission to logon, just not execute that stored procedure.  The call IS gettting to the SQL server as that is what is rejecting it.  Therefore I want to see the call hit the SQL server.  I was able to do this in the SQL 2000 query analyzer.

The question here is how to see everything that I was able to in the previous version.

There are other things as well, this is just the first one I remembered to be able to reproduce.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16756407
Here is another situation we were just able to reproduce.

We ran a query and based on the size of the input in one of the parameters  - if too large we see "Trace Skipped Records"  If we reduce the data size for testing of the variables then we see the exact event.

Therefore we are not selecting the wrong event to trace.

But we need to be able to see ALL data sent - not just data if it is small....

Basically we want to see any request sent to the SQL server.  Once there we don't need to see each step of a stored proc or other thing execute - just the actual request sent to the SQL server - including data.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16901573
select all the columns and events in the sql profiler and then see what you need.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 16904967
I did that - we still do not see these things.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16929595
i think you should try sql spy


www.snapfiles.com/get/sqlspy.html 

0
 
LVL 35

Author Comment

by:mrichmon
ID: 16943622
I do not want a third party tool.

I have found further information.

We can see the command if the parameter lengths are short enough.  Sending the same command with longer data in the parameters causes the Profiler not to show the command at all instead we see "Trace Skipped Records", with no way to see those records.

I think this may be a bug.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17076267
Closed, 250 points refunded.
Netminder
Site Admin
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question