Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
--
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I do
> this? I am using sp_trace_generateevent to capture before and after data in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>> Problem: I only get one record of before and after data when performing
>> multirow updates from a single update statement. I want to get before
>> and
>> after data for ALL updated records from the update statement. How can I
>> do
>> this? I am using sp_trace_generateevent to capture before and after
>> data in
>> a trace when performing updates using the following code:
>> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
>> REPLICATION AS
>> BEGIN
>> PRINT 'HERE'
>> Declare @.mval nvarchar(256)
>> Declare @.mval2 nvarchar(256)
>> Declare @.mvalall nvarchar(512)
>> SELECT
>> @.mval = ' UPDATE: Before First Name: ' + au_fname +
>> ' -
>> ' + ' Last Name: ' + au_lname + ' | '
>> FROM DELETED
>> SELECT
>> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
>> Last Name: ' + au_lname + ' | '
>> FROM INSERTED
>> Set @.mvalall=@.mval + @.mval2
>> EXEC sp_trace_generateevent
>> @.event_class = 82, @.userinfo=@.mvalall
>> END
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> > How about inserting direct to a table. This example assumes no nullable
> > columns are involved and that the key is key_col:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--