Re: SET NOCOUNT ON - am I doing this wrong?
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/03/04
- Next message: David Portas: "Re: Use of Null"
- Previous message: Terry: "backup"
- In reply to: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Next in thread: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Reply: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 3 Dec 2004 12:49:56 -0500
Maury Markowitz wrote:
> "David Gugick" wrote:
>> Are you sure those messages are coming from the trigger?
>
> Yes, they are the results of SELECT and INSERT statements inside it.
>
>> running this update. Are you using QA and executing an UPDATE or
>> running a stored procedure.
>
> Using an UPDATE in QA.
>
>> In either case, you should have set nocount on at
>> the top of those calls as well.
>
> I have no control over whether or not Access will do this -- even if
> this fixes the problem in QA it doesn't for Access and thus isn't a
> solution.
>
> I don't think this is the issue anyway. Every example of NOCOUNT I
> see is exactly as I did it in my code. It wouldn't make any sense to
> place it in the trigger if you also had to place it around every call
> that could fire the trigger!
>
> Maury
You do need it in the calling proc or script and can set this in Access
by running a set nocount on one time when you connect to the server. If
the main call was from a SP, you need it there. Whereever the parent is.
In this case if the trigger table can be inserted from an SP or from an
update directly (Access or QA), you need it in both places: trigger and
SP.
Here are some examples:
create table trigtest (col1 int)
create table trigtest2 (col1 int)
create trigger trigtest_ins on trigtest
for insert
as
begin
insert into trigtest2
select col1 from inserted
end
set nocount off
insert into trigtest values (1)
-- (1 row(s) affected)
set nocount on
insert into trigtest values (1)
-- The command(s) completed successfully.
create proc trigtest_sp
as
begin
insert into trigtest values (1)
end
set nocount off
exec trigtest_sp
-- (1 row(s) affected)
-- (1 row(s) affected)
set nocount on
exec trigtest_sp
-- The command(s) completed successfully.
alter trigger trigtest_ins on trigtest
for insert
as
begin
set nocount on
insert into trigtest2
select col1 from inserted
end
set nocount off
insert into trigtest values (1)
-- (1 row(s) affected)
set nocount on
insert into trigtest values (1)
-- The command(s) completed successfully.
alter proc trigtest_sp
as
begin
set nocount on
insert into trigtest values (1)
end
set nocount off
exec trigtest_sp
-- The command(s) completed successfully.
set nocount on
exec trigtest_sp
-- The command(s) completed successfully.
drop proc trigtest_sp
drop table trigtest
drop table trigtest2
-- David Gugick Imceda Software www.imceda.com
- Next message: David Portas: "Re: Use of Null"
- Previous message: Terry: "backup"
- In reply to: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Next in thread: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Reply: Maury Markowitz: "Re: SET NOCOUNT ON - am I doing this wrong?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
