SQL Profiler show data captured

Last Post 25 Feb 2008 01:02 AM by SQLUSA. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Amber_Robertsona
New Member
New Member

--
22 Feb 2008 12:22 AM
Dear All, does anybody know how to use SQL Profiler to generate data being captured back to the client as it happened? I can use SQL Server 2000 or SQL Server 2005. Recently a developer wanted to know what specific data was return by a user defined stored procedure which runs select statements on a table frequently updated.

Any suggestions much appreciated.

Kind Regards
Amber
SQLUSA
New Member
New Member

--
22 Feb 2008 04:12 AM
This is an absolutely fascinating topic.

I am not aware of option to record the general output (SELECT) of a sproc.

>rows< is one of the colums which you can monitor - not sure exactly what's the meaning

The Stored Procedure RPCutput Parameter event class traces the output parameter values of remote procedure calls (RPCs) after execution.

Use this class to examine the output values returned by stored procedures. For example, if an application is not producing the expected output values after executing a remote procedure call, you can use this event class to help isolate the problem between the client code and the server code


Let us now what you find!

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
22 Feb 2008 04:14 AM
Smiley!

RPC OUTPUT PARAMETER I entered with :

No joke here, serious topic!

Amber_Robertsona
New Member
New Member

--
25 Feb 2008 12:43 AM
Dear Kalman, thank you for the advice provided, I tried running SQL Profiler using RPC Output Parameter event class unfortunately this still didn't generate the actual results I wanted. I wanted SQL Profiler to not only show the stored procedure in running but the actual data generated by the stored procedure. I created the test stored procedure TestASelect in Northwind database:

Create procedure TestASelect
As
select * from Customers
where ContactTitle like '%Own%'

Exeucte query in SQL Query Analyzer I get the following data:
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner Avda. de la Constitución 2222 México D.F. NULL 05021 Mexico (5) 555-4729 (5) 555-3745
ANTON Antonio Moreno Taquería Antonio Moreno Owner Mataderos 2312 México D.F. NULL 05023 Mexico (5) 555-3932 NULL
BOLID Bólido Comidas preparadas Martín Sommer Owner C/ Araquil, 67 Madrid NULL 28023 Spain (91) 555 22 82 (91) 555 91 99
BONAP Bon app' Laurence Lebihan Owner 12, rue des Bouchers Marseille NULL 13008 France 91.24.45.40 91.24.45.41
CHOPS Chop-suey Chinese Yang Wang Owner Hauptstr. 29 Bern NULL 3012 Switzerland 0452-076545 NULL
DUMON Du monde entier Janine Labrune Owner 67, rue des Cinquante Otages Nantes NULL 44000 France 40.67.88.88 40.67.89.89
FOLKO Folk och fä HB Maria Larsson Owner Åkergatan 24 Bräcke NULL S-844 67 Sweden 0695-34 67 21 NULL
GROSR GROSELLA-Restaurante Manuel Pereira Owner 5ª Ave. Los Palos Grandes Caracas DF 1081 Venezuela (2) 283-2951 (2) 283-3397
LETSS Let's Stop N Shop Jaime Yorres Owner 87 Polk St. Suite 5 San Francisco CA 94117 USA (415) 555-5938 NULL
LINOD LINO-Delicateses Felipe Izquierdo Owner Ave. 5 de Mayo Porlamar I. de Margarita Nueva Esparta 4980 Venezuela (8) 34-56-12 (8) 34-93-93
OTTIK Ottilies Käseladen Henriette Pfalzheim Owner Mehrheimerstr. 369 Köln NULL 50739 Germany 0221-0644327 0221-0765721
PARIS Paris spécialités Marie Bertrand Owner 265, boulevard Charonne Paris NULL 75012 France (1) 42.34.22.66 (1) 42.34.22.77
SANTG Santé Gourmet Jonas Bergulfsen Owner Erling Skakkes gate 78 Stavern NULL 4110 Norway 07-98 92 35 07-98 92 47
SIMOB Simons bistro Jytte Petersen Owner Vinbæltet 34 Kobenhavn NULL 1734 Denmark 31 12 34 56 31 13 35 57
TORTU Tortuga Restaurante Miguel Angel Paolino Owner Avda. Azteca 123 México D.F. NULL 05033 Mexico (5) 555-2933 NULL
WHITC White Clover Markets Karl Jablonski Owner 305 - 14th Ave. S. Suite 3B Seattle WA 98128 USA (206) 555-4112 (206) 555-4115
WILMK Wilman Kala Matti Karttunen Owner/Marketing Assistant Keskuskatu 45 Helsinki NULL 21240 Finland 90-224 8858 90-224 8858
WOLZA Wolski Zajazd Zbyszek Piestrzeniewicz Owner ul. Filtrowa 68 Warszawa NULL 01-012 Poland (26) 642-7012 (26) 642-7012

I would like to use SQL Profiler to capture the same result sets as if another user was running the query. Please let me know if this makes sense.


Kind Regards
Amber




SQLUSA
New Member
New Member

--
25 Feb 2008 12:56 AM
Thanks Andy.

So the rowcount can be used in debugging context in some cases.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/bestpractices...procedure/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
25 Feb 2008 01:02 AM
Amber, You made me hungry.

We do understand what you need.

I am afraid there is no such an option in SQL Profiler for capture.

If the table(s) is not that dynamic, Andy's suggestion can be followed. Just take the input query (textdata column) and rerun it manually for the result generation.

Naturally if the data changed in between, you will not get the same result.

In SQL Server 2005 you can take snapshots e.g. end of the day, and run it in the snapshot to get as close as possible to the actual results.

How dynamic is your data?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/bestpractices...innerjoin/ The Best SQL Server 2005 Training in the World!

SQLUSA
New Member
New Member

--
25 Feb 2008 01:11 AM
OK Amber, finally it clicked.

Here is what you have to do:

Set up 2 tables for tracing: SprocCall ( SprocCallID (PK), name, parms, datetimestamp)

SprocResult( SprocCallID (FK), result colums).

Place 2 INSERT statements before the final SELECT in the stored proc.

Let us know if this works for you.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/bestpractices2005/ The Best SQL Server 2005 Training in the World!
Amber_Robertsona
New Member
New Member

--
25 Feb 2008 03:55 AM
Kalman, sorry I didn't mean to appear annoyed but just wasn't sure if I made the original request clear. I like the idea of adding 2 insert statements and next time a developer wants to know what data output is being generated I will add that.

Take care
Amber
Amber_Robertsona
New Member
New Member

--
26 Feb 2008 12:41 AM
Russell, some of the tables we have change very frequently especially customer order status. So occasionally I think it would be helpful for a developer to know what data is captured at a specific time. Any ideas how this could be achieved without degrading SQL Server performance dramatically is much appreciated.

Regards
Amber
SQLUSA
New Member
New Member

--
26 Feb 2008 04:02 AM
Clarification.

I meant to add the 2 insert statements for testing/debugging only, not permanently.

Russell's idea is also good: let the app log it.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/sql-server-2008-training/ The Best SQL Server 2008 Training in the World!

You are not authorized to post a reply.

Acceptable Use Policy