Can SQL server from a trigger or Stored procedure, post a
message to MSMQ?
Or can SQL server from a trigger or stored procedure, run
or fire a VBscript to do the same?Yes, using the sp_oamethod, haven't done it but there are a few sites out
there that walk through doing it, here's one.
http://www.tagconsulting.com/Show.asp?Id=1000
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Peter Upfold" <peter@.statmon.com> wrote in message
news:054001c36767$98c11930$a401280a@.phx.gbl...
> Can SQL server from a trigger or Stored procedure, post a
> message to MSMQ?
> Or can SQL server from a trigger or stored procedure, run
> or fire a VBscript to do the same?
>
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
msg16915
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name => 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Possibly the trigger creates a cursor without deallocating it. Check the
>> trigger code.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>>
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> > When I want to make an update on a table which make a trigger action, it
> > gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> > already exist"
> >
> > Can anyone please give me more info on msg 16915 or how to solve it?
> > Thanks.
> >
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>> Actually, I doubt I found the cause of error.
>> Here is situation. I define trigger A in table TB1 and trigger B in table
>> TB2. The change in record in TB1 make trigger A act in which write record
>> to
>> TB2 and make trigger B act in which write record to TB3. Much like a
>> chain.
>> But then when I make change in TB1, I got error message saying cursor
>> already exists. Can anyone please help? Thanks.
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> > When I want to make an update on a table which make a trigger action,
>> > it
>> > gives me error "msg16915, level 16, State 1, Procedure ..." and
>> > "cursor
>> > already exist"
>> >
>> > Can anyone please give me more info on msg 16915 or how to solve it?
>> > Thanks.
>> >
>>
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name => 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Possibly the trigger creates a cursor without deallocating it. Check the
>> trigger code.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>>
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> > When I want to make an update on a table which make a trigger action, it
> > gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> > already exist"
> >
> > Can anyone please give me more info on msg 16915 or how to solve it?
> > Thanks.
> >
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>> Actually, I doubt I found the cause of error.
>> Here is situation. I define trigger A in table TB1 and trigger B in table
>> TB2. The change in record in TB1 make trigger A act in which write record
>> to
>> TB2 and make trigger B act in which write record to TB3. Much like a
>> chain.
>> But then when I make change in TB1, I got error message saying cursor
>> already exists. Can anyone please help? Thanks.
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> > When I want to make an update on a table which make a trigger action,
>> > it
>> > gives me error "msg16915, level 16, State 1, Procedure ..." and
>> > "cursor
>> > already exist"
>> >
>> > Can anyone please give me more info on msg 16915 or how to solve it?
>> > Thanks.
>> >
>>
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
msg16915
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>
|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>
|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>
|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>
|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>
|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>
|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
msg16915
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the tri
gger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx
.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thank
s.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was
created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.g
bl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record
to
> TB2 and make trigger B act in which write record to TB3. Much like a chai
n.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the tri
gger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx
.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thank
s.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was
created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.g
bl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record
to
> TB2 and make trigger B act in which write record to TB3. Much like a chai
n.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Monday, March 12, 2012
msg 3721
I tried to run the following in query analyzer:
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.
Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>
|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.
Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>
|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>
msg 3721
I tried to run the following in query analyzer:
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none
>> of them is relevant to my case. Can anyone please help? Thanks.
>>
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages table and didn't find
error 3721, which makes it very hard for us to suggest something.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl...
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> See if the table name provided is correct.
>> Thanks
>> Hari
>> " 009SteveJobs" <steve@.jobs.com> wrote in message news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none of them is relevant to
>> my case. Can anyone please help? Thanks.
>>
>>
>
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none
>> of them is relevant to my case. Can anyone please help? Thanks.
>>
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages table and didn't find
error 3721, which makes it very hard for us to suggest something.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl...
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> See if the table name provided is correct.
>> Thanks
>> Hari
>> " 009SteveJobs" <steve@.jobs.com> wrote in message news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>>I tried to run the following in query analyzer:
>> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
>> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
>> And I tried to search Microsoft support website for this message and none of them is relevant to
>> my case. Can anyone please help? Thanks.
>>
>>
>
msg 3721
I tried to run the following in query analyzer:
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages ta
ble and didn't find
error 3721, which makes it very hard for us to suggest something.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl
..
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>
ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
And I tried to search Microsoft support website for this message and none of
them is relevant to my case. Can anyone please help? Thanks.Hi,
See if the table name provided is correct.
Thanks
Hari
" 009SteveJobs" <steve@.jobs.com> wrote in message
news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>I tried to run the following in query analyzer:
> ALTER TABLE Products_table enable Trigger XXX_ins_upd_trg;
> But I got the error message : Server: Msg 3721, Level 16, State 1, Line 1
> And I tried to search Microsoft support website for this message and none
> of them is relevant to my case. Can anyone please help? Thanks.
>|||Just checked. It's correct.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> See if the table name provided is correct.
> Thanks
> Hari
> " 009SteveJobs" <steve@.jobs.com> wrote in message
> news:uyEImlEDHHA.4024@.TK2MSFTNGP04.phx.gbl...
>|||You didn't post the message text. I looked in SQL Server 2000 sysmessages ta
ble and didn't find
error 3721, which makes it very hard for us to suggest something.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
" 009SteveJobs" <steve@.jobs.com> wrote in message news:e88ceZFDHHA.3604@.TK2MSFTNGP03.phx.gbl
..
> Just checked. It's correct.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:Owr6xIFDHHA.4396@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
MSDTC on server <Server-Name> is unavailable.
I got this error after I put an update trigger on a table which resides
in Database A and queries a table contained in Database B via a
linkserver. I have looked at the server and have bounced both the SQL
Server service and the DTC service and still it says that it can't find
it. Since this is a production server, resetting it is kind of out of
the question (but I'll do the old Microsoft fix if I have to). I was
just curious if anyone else has had this problem?Topper (Kinky.Taylor@.gmail.com) writes:
> I got this error after I put an update trigger on a table which resides
> in Database A and queries a table contained in Database B via a
> linkserver. I have looked at the server and have bounced both the SQL
> Server service and the DTC service and still it says that it can't find
> it. Since this is a production server, resetting it is kind of out of
> the question (but I'll do the old Microsoft fix if I have to). I was
> just curious if anyone else has had this problem?
Firewalls?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try the following from the command prompt:
msdtc -uninstall
then...
msdtc -install
This will reset the msdtc instance on the server.
Probably will require a reboot, but this has worked for me in the past.
Good Luck...John|||This is how I got into this predicament, but I'll try a reboot later
on.
Thanks
in Database A and queries a table contained in Database B via a
linkserver. I have looked at the server and have bounced both the SQL
Server service and the DTC service and still it says that it can't find
it. Since this is a production server, resetting it is kind of out of
the question (but I'll do the old Microsoft fix if I have to). I was
just curious if anyone else has had this problem?Topper (Kinky.Taylor@.gmail.com) writes:
> I got this error after I put an update trigger on a table which resides
> in Database A and queries a table contained in Database B via a
> linkserver. I have looked at the server and have bounced both the SQL
> Server service and the DTC service and still it says that it can't find
> it. Since this is a production server, resetting it is kind of out of
> the question (but I'll do the old Microsoft fix if I have to). I was
> just curious if anyone else has had this problem?
Firewalls?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Try the following from the command prompt:
msdtc -uninstall
then...
msdtc -install
This will reset the msdtc instance on the server.
Probably will require a reboot, but this has worked for me in the past.
Good Luck...John|||This is how I got into this predicament, but I'll try a reboot later
on.
Thanks
Subscribe to:
Posts (Atom)