mrichmon
asked on
SQL 2005 Profiler
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER
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
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
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
select all the columns and events in the sql profiler and then see what you need.
ASKER
I did that - we still do not see these things.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
go to event selection tab and there
check box (show all events)
select sql:batchstarting
and sql:stmtstarting
it will give your desired result.