don't laugh,

is there some utility that enables a request/response style profiling on SQL
server.
this is really needed.

I mean not only seeing the SQL request like the SQL server profiler does,
but seeing also the response in some way, lets say first 10 rows etc.

it can really ease debugging

if there is no such way but you know how to do this using some profiling API
to SQL server it can also help.


TIA.

z f wrote:
Show quoteHide quoteProfiler shows this is as the Duration, which is the time SQL Server takes
to execute and the client to fetch all results. You can see this easily with
a large enough table from SQL EM (do not do this in production). Start a
Profiler trace (RPC:Starting/Completed and SQL:BatchStarting/Completed
events). Open a table from SQL EM (All Rows). You'll see the starting event.
Hit CTR+END to fetch all rows. You'll then see the Completed event. Is this
what you are looking for? If not, it may be something you'll have to program
from the data access layer in your source code.

There's no way to get at the responses with SQL Profiler.

The only way I'm aware of to get this information without changing your app
is to use a protocol analyser such as Ethereal. Good news is that Ethereal
is free & conveniently exports its captures to xml files which are then
fairly easy to work with.

It wouldn't be too hard to write an xml parser to extract the first 10 rows
from the output..

Regards,
Greg Linwood
SQL Server MVP

Show quoteHide quote

does the protocol SQL server used is being parsed by ethereal (like HTTP )
to not show only the binary data?
is it public like HTTP?
because if not there is no way to parse the data.

thanks




Show quoteHide quote

Not having a public protocol certainly does NOT mean that there's no way to
parse the binary data. For example, some client access libraries such as ADO
have documented & even supported de-serialization methods which you can use
to reverse a byte stream to an instance of an object. This was a very common
technique amongst MSMQ developers years ago ( & even today ) as they would
often serialise an ADO recordset to a byte stream, pack it into a MSMQ
message, transmit it somewhere, then de-serialize it at the other end of the
wire. I don't have that code handy right now but can definitely dig it up if
you're looking to unpack older ADO stuff.

SQL Server's TDS protocol is documented to a reasonable extend here:
http://www.freetds.org. How useful this is to you will depend on which
version of SQL Server you're using (which you haven't stated) & also what
you're actually intending to do (eg script something for your own ad-hoc use
or build a toolset that you're planning to sell which requires significantly
more efffort).

The real trick here is to know what protocols you're intending to work with
& target them individually. Trying to build an all-encompassing solution
that covers all client access protocols probably isn't feasible, given the
number & versions of apis available.

I didn't mean to trivialise this when I suggested the idea. Whether this is
worth your while reallyy depends on how badly you need a solution to this.
If you're dealing with your own application, it may well be easier to simply
insert some extra code in your data layer & pluck the results from within
whatever library you're using.

Regards,
Greg Linwood
SQL Server MVP

Show quoteHide quote

Was 'Create Stored Procedure' wizard droped from SQL Server 2005
DELETING 100 million from a table weekly SQl SERVER 2000
SQL Server 2005 & Visual Studio 205 RC
Filtering out blank TextData in Profiler
SQL 2005 Managemet Studio ...Unicode?