How to use DeBUG window in QUERY Analyzer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to use DeBUG window in QUERY Analyzer

Author

Topic

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 15:35:47

I wanted debug stored procedure step by step is tehre a way
to debug Step by step or line by line in QUERY Analyzer
SQL server 2000

Thanks
chandra

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 15:39:05

Look up Transact-SQL Debugger in SQL Server Books Online for instructions on how to use.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 16:13:39

Thak you so much I got it instruction from BOL how to use debug window,
but if I wanted debug Trigger step by step how do I do that can I
open Trigger in debug mode in Query Analyzer or how can I debug

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 16:15:15

I don't think you can debug triggers via that debugger. I use PRINT statements and SELECT * to debug when things aren't working right.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 16:24:05

Thank you so much tara, you mean I can not debug trigger
or is there any other way of doing this,
because I have a problem with Update trigger but some reason
it is not updating the table here is Error that I have got
but I am updateing only one row at a time still I am gettting this
error.

Server: Msg 512, Level 16, State 1, Procedure ut_Currhold, Line 132
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 16:36:33

Well you've got a subquery that is returning more than one row which is not allowed. So your UPDATE might only be affecting one row, but your subquery is returning more than one. Please post the code.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 16:52:53

Hi tara,
here is the my trigger code which is taking from Inseted table so that means only one row
in inset=rted table..

/* Update Trigger for table Currhold */
CREATE trigger ut_Currhold
on Currhold for update as
declare @currency_code character(3),
@forward_ref integer
select @currency_code = currency_code, @forward_ref = forward_ref from inserted
/* currency_code references primary key FXCurr (currency_code) */
/* Child Update Restrict */
if @currency_code<>NULL
begin
if (select count(*)
from FXCurr, inserted
where FXCurr.currency_code = inserted.currency_code) <> (select count(*) from inserted)
begin
raiserror 30008 'You may not update Currhold unless a corresponding FXCurr exists'
rollback transaction
return
end
end
/* instrument_type references primary key Instrument_Types (instrument_type) */
/* Child Update Restrict */
if update (instrument_type)
begin
if (select count(*)
from Instrument_Types, inserted
where Instrument_Types.instrument_type = inserted.instrument_type) <> (select count(*) from inserted)
begin
raiserror 30009 'You may not update Currhold unless a corresponding Instrument_Types exists'
rollback transaction
return
end
end
/* counter_party references primary key Ratings (counter_party) */
/* Child Update Restrict */
if update (counter_party)
begin
if (select count(*)
from Ratings, inserted
where Ratings.counter_party = inserted.counter_party) <> (select count(*) from inserted)
begin
raiserror 30010 'You may not update Currhold unless a corresponding Ratings exists'
rollback transaction
return
end
end
/* der_type references primary key Derivative_Types (der_type) */
/* Child Update Restrict */
if update (der_type)
begin
if (select count(*)
from Derivative_Types, inserted
where Derivative_Types.der_type = inserted.der_type) <> (select count(*) from inserted)
begin
raiserror 30011 'You may not update Currhold unless a corresponding Derivative_Types exists'
rollback transaction
return
end
end
/* account references primary key Account (account) */
/* Child Update Restrict */
if update (account)
begin
if (select count(*)
from Account, inserted
where Account.account = inserted.account) <> (select count(*) from inserted)
begin
raiserror 30012 'You may not update Currhold unless a corresponding Account exists'
rollback transaction
return
end
end
/* forward_ref references primary key Currhold (currhold_id) */
/* Child Update Restrict */
if @forward_ref<>NULL
begin
if (select count(*)
from Currhold, inserted
where Currhold.currhold_id = inserted.forward_ref) <> (select count(*) from inserted)
begin
raiserror 30013 'You may not update Currhold unless a corresponding Currhold exists'
rollback transaction
return
end
end
/* currhold_id referenced by foreign key Currhold_Sec (currhold_id) */
/* Parent Update Cascade */
if update (currhold_id)
begin
update Currhold_Sec
set Currhold_Sec.currhold_id = inserted.currhold_id
from Currhold_Sec, deleted, inserted
where Currhold_Sec.currhold_id = deleted.currhold_id
end
/* currhold_id referenced by foreign key Currhold (forward_ref) */
/* Parent Update Cascade */
if update (currhold_id)
begin
update Currhold
set Currhold.forward_ref = inserted.currhold_id
from Currhold, deleted, inserted
where Currhold.forward_ref = deleted.currhold_id
end

/* A.G. */
/* If forward reference is set to some value for existing forward it means that
existing FX Forward is unwound.
Create Marked To Spot journal entry. */
/* Modified: 7/02/03 to calculate unrealized Gain / Loss differently and to set
discount to what's currently is sitting in discount field of the holding
instead of calculating it.
4/30/04 to add a condition when updating forward_ref field: do further
processing only if the holding of 'FR' derivative type since some 'IR'
holdings will be using forward_ref to indicate if IR Swap originated
from Swaption.
5/21/04 to create journal entry for IR or CD Swaps to reverse
statement value amount (composed of all 'MTFV' entries' amounts) when
hedge effectiveness flag for the holding is fliped from ineffective to
effective. */

DECLARE @currhold_id integer,
@transac_date datetime,
@tran_type varchar(8),
@entered_by varchar(30),
@unrl_gl_me numeric(14,4),
@unrl_gl_to_date numeric(14,4),
@unrl_gl_total numeric(14,2),
@discount numeric(14,2),
@fgn_notional numeric(14,2),
@der_type varchar(8),
@sv numeric(14,4)

SET @der_type = (SELECT der_type FROM inserted)
SET @currhold_id = (SELECT currhold_id FROM inserted)
SET @entered_by = (SELECT edited_by FROM inserted)

IF UPDATE (forward_ref)
BEGIN

IF @der_type = 'FR'
BEGIN
SET @fgn_notional = (SELECT fgn_notional FROM inserted)

IF (SELECT forward_ref FROM deleted) IS NULL AND
(SELECT forward_ref FROM inserted) IS NOT NULL
IF @fgn_notional > 0
BEGIN
/* Set variables values */

SET @transac_date = (SELECT trade_date
FROM currhold
WHERE currhold_id = (SELECT forward_ref FROM inserted))

/* Unrealized G/L for the period of time between last month-end
and the date when this update is happening */
SET @unrl_gl_to_date = dbo.Gain_Loss(@fgn_notional,
(SELECT fx_rate FROM FXCurr WHERE currency_code = @currency_code),
(SELECT spot_rate FROM inserted))

SET @unrl_gl_me = (SELECT der_unrl_gl_ytd_c FROM deleted)

SET @unrl_gl_total = (SELECT der_unrl_gl_ytd_c FROM inserted)

SET @discount = (SELECT discount FROM deleted)

/* Create journal entry */
EXEC Ins_Entry_Header_FR_MTS
@currhold_id,
@transac_date,
@entered_by,
@unrl_gl_to_date,
@unrl_gl_me,
@unrl_gl_total,
@discount
END /* @fgn_notional > 0 */
END /* IF @der_type = 'FR' */
END /* IF UPDATE (forward_ref) */

IF UPDATE (hedge_effective_flag)
BEGIN

IF @der_type IN ('IR', 'CD') AND
(SELECT hedge_effective_flag FROM deleted) = 0 AND
(SELECT hedge_effective_flag FROM inserted) = 1
BEGIN

SET @sv = (SELECT sv FROM deleted)

/* Create journal entry */
EXEC Ins_Entry_Header_IR_CD_RVRSMTFV
@currhold_id,
@sv,
@entered_by

END /* IF @der_type IN ('IR', 'CD') ... */
END /* IF UPDATE (hedge_effective_flag) */

chandra shekar

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 16:54:15

one way to debug triggers is to create a "Trigger Log" table, and add entries to that table during the course of your trigger. then check out the table after your trigger has executed to see how it did. of course, it is up to you to decide what to store in that table, how often, and in what format.

and don't forget to disable that feature when you move to production !

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 16:57:00

holy smokes -- are you familiar with foreign key constraints ? the first ~50 lines of your trigger can all be handled with a few basic SQL constraints. Why are you trying to do this manually?

And it is extremely imporant to understand that you need to always work with INSERTED on the assumption that it contains sets of rows that are inserted, not just one row ! That is why you are getting the specific error you mentioned.

My advice -- write down the business requirements of this trigger and start over from scratch. and use foreign key constraints between your tables to handle them.

- Jeff

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 16:57:18

Could please tell me how do I create log file on teh table
Trigger Log table, is that I have to put with in trigger to write to
log file or do I have to give log file name or what Thanks
help

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 16:59:58

quote:
Originally posted by cshekar

Hi tara,
here is the my trigger code which is taking from Inseted table so that means only one row
in inset=rted table..

That isn't true. inserted table contains all of the rows affected by the INSERT statement.

But that's not necessarily why it is failing. It has to do with the subquery returning more than one row. So if the subquery references the inserted table, then it's because the trigger isn't designed to handle more than one row in the inserted table. If the subquery references a real user table, then you need to figure out it is returning more than one row. You can usually fix this by changing the WHERE clause.

Your code isn't formatted with code tags so it's very hard to read and I can't find the subquery in there. Please edit your post with code tags.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 17:02:31

but same trigger works in Beta and developemnet but I do not known
some reason it is nor working in production I am having this problem
Server: Msg 512, Level 16, State 1, Procedure ut_Currhold, Line 132
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 17:04:31

It has to do with the data. Please either post the query that contains the subquery or modify your post with code tags so that it is formatted for us.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 17:08:05

Here is the Sub Query with in trigger

IF @der_type = 'FR'
BEGIN
SET @fgn_notional = (SELECT fgn_notional FROM inserted)

IF (SELECT forward_ref FROM deleted) IS NULL AND
(SELECT forward_ref FROM inserted) IS NOT NULL
IF @fgn_notional > 0
BEGIN
/* Set variables values */

SET @transac_date = (SELECT trade_date
FROM currhold
WHERE currhold_id = (SELECT forward_ref FROM inserted))

/* Unrealized G/L for the period of time between last month-end
and the date when this update is happening */
SET @unrl_gl_to_date = dbo.Gain_Loss(@fgn_notional,
(SELECT fx_rate FROM FXCurr WHERE currency_code = @currency_code),
(SELECT spot_rate FROM inserted))

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 17:11:29

Ok, your subquery is selecting from the inserted table, which can contain more than one row. That is not allowed in a subquery, thus the failure. Your trigger needs to be able to handle more than one row in the inserted table. A complete redesign of this trigger will be needed to correct.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 17:17:43

Thank you, I understand that Inserted table got all rows that's why it is taking more than one row but in development same trigger same subquery I am using I am able to update to table

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 17:20:47

It's because in development, the data is different. Your INSERT statement in development only affected one row, thus only one row in the inserted table. You will have the same problem in development if you performed the actions that caused the INSERT to insert more than one row.

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 17:28:03

I did try to update only one from Query analyzer still I got same error
I have used this update statement
UPDATE currhold
SET inc_recv_ytd = 0
WHERE expire_flag = 0

still i got same error

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 17:30:12

So how many rows does this query return:

SELECT *
FROM currhold
WHERE expire_flag = 0

Tara

cshekar
Starting Member

49 Posts

Posted - 2004-09-14 : 17:31:48

There are about 500 rows effect by this query

chandra shekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 17:35:16

Then your UPDATE statement that you posted will cause 500 rows to be put into the inserted table causing your error.

Tara