CODEWITHSUNDEEP
sqlsql-server-2005delphi
Rob
Rob

Reputation: 33

How to capture stored procedure text when passing from Dephi 2006

HI all,

I have a large (100+) parameter list for my SP in my delphi code. This is for MS SQL Server 2005. For debugging purposes, I want to capture the text of the stored procedure command, so i can execute it on the SQL server and debug the SP. Is there a way i can capture what is exactly passed to the database? I thought about using a trace, and I'll try that tomorrow if this fails, but its cumbersome to set up and sift through and catch the SP.

Thanks

Upvotes: 3

Views: 372

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

I normally don't care for playing with the programming environment.

Profiling would be a good option if you can identify the ClientProcessID (the PID showing in Task Manager of your client program) - that should narrow it down enough.

Another alternative I like is to simply capture it at the SQL Server end.

Sample proc

create proc takes3params
@a int, @b varchar(100), @c datetime
as
select @a, @b, @c

Becomes

alter proc takes3params
@a int, @b varchar(100), @c datetime
as
insert capture_takes3params(a,b,c) select @a, @b, @c  -- << -- added
select @a, @b, @c

The support table is a mirror of the params, with 2 additional control columns

create table capture_takes3params(
id int identity primary key, captured datetime default(getdate()), -- control
a int, b varchar(100), c datetime
)

This doesn't work when the proc has defaults though.

EDIT

We use ADO to connect to MS SQL. Not sure what the alternative to 100+ params is, maybe pass table structures? Advice welcome! We are passing in HL7 messages which typically have 100 or so fields. –

Table valued parameters are only available from SQL Server 2008 onwards, from what I recall. That seems unwieldy as well from Delphi - I would instead look at a single XML parameter dissected in TSQL, which 2005+ has good support for.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You should use the SQL Server Profiler for this. Start a new trace with default settings. Let it run while your client executes the SP. Stop the trace. Use ctrl-F and search for you SP name.

Upvotes: 1

Related Questions