ADP code hangs while running the same query in query analyser works

  • Thread starter Thread starter C.W.
  • Start date Start date
C

C.W.

I have got an updateable snapshot based on a view. When close the form, it
automatically sends out the following script to update the underlying table.

exec sp_executesql N'UPDATE "arpos".."tafe_hdr" SET "ref2"=@P1 WHERE
"seqno"=@P2 AND "created_date"=@P3 AND "enrolmentno"=@P4 AND "ord_ref"=@P5
AND "ref1"=@P6 AND "ref2"=@P7 AND "notes" IS NULL AND "invno"=@P8', N'@P1
varchar(6),@P2 int,@P3 datetime,@P4 varchar(12),@P5 varchar(4),@P6
varchar(10),@P7 varchar(4),@P8 int', 'testts', 246, 'Feb 3 2003
12:00:00:000AM', 'p02062693446', 'test', '0203000085', 'test', 246

This screen in ADP simply times out. In SQL Server locks, I can see that
this connection is blocked by itself?! (how is that possible).

I run the same script in query analyser, the script completes under 1 sec.

Any idea what is going on?

I have a trigger on the underlying table that is being updated

CREATE TRIGGER ASAS_UPD_TAFE_HDR ON [dbo].[TAFE_HDR]
FOR UPDATE
AS
if exists (select * from master.dbo.sysprocesses where program_name like
'microsoft office%' and spid=@@spid)
begin
update
a
set
a.transdate=b.created_date,
a.ref2=b.enrolmentno,
a.x_enrol_id=b.enrolmentno,
a.ord_ref=b.ord_ref,
a.ref1=b.ref1
from
dr_trans a inner join inserted b on
a.jobno=b.seqno


insert into asas_audit
select
'Update Invoice Header',
system_user,
host_name(),
getdate(),
invno
from
inserted
end

I don't think it makes any difference. I have included it just in case if
anyone can spot any problem here.

Thanks in advance
 
From the test « where program_name like 'microsoft office%' ... »; I don't
think that much of this trigger is running when you run this script in the
query analyser.

Have you took the precaution of setting a Resync Command? Otherwise, ADP
will make independant requeries for all tables and this could lead to a
deadlock because these commands are run asynchroneously.

Does this problem occurs only when you are closing the form or if it happens
too when you are moving from record to record?

You could also try with an INSTEAD OF Trigger for your view (personally, I
never used them with ADP but we never know).

Finally, by default, ADP opens three connections to the SQL-Server, not just
one; so it's quite possible that the self blocking connection might be two
different connections instead.
 
Hi Silvain

Really appreciate the help. I had to kick myself when I looked at the
trigger again, and realised that I did force to trigger to run only if the
update query is issued out of an office application, and hence there wasn't
a problem when I issue the update command from query analyser.

Now can you help me with using the resync and parameter options of forms? I
looked at the documention and couldn't figure out how they can be used. The
reason I used a view is because I couldn't figure out how to update back to
the database when data is retrieved through stored proc.

Many thanks for your help

Regard
Sylvain Lafontaine said:
From the test « where program_name like 'microsoft office%' ... »; I don't
think that much of this trigger is running when you run this script in the
query analyser.

Have you took the precaution of setting a Resync Command? Otherwise, ADP
will make independant requeries for all tables and this could lead to a
deadlock because these commands are run asynchroneously.

Does this problem occurs only when you are closing the form or if it
happens too when you are moving from record to record?

You could also try with an INSTEAD OF Trigger for your view (personally, I
never used them with ADP but we never know).

Finally, by default, ADP opens three connections to the SQL-Server, not
just one; so it's quite possible that the self blocking connection might
be two different connections instead.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


C.W. said:
I have got an updateable snapshot based on a view. When close the form, it
automatically sends out the following script to update the underlying
table.

exec sp_executesql N'UPDATE "arpos".."tafe_hdr" SET "ref2"=@P1 WHERE
"seqno"=@P2 AND "created_date"=@P3 AND "enrolmentno"=@P4 AND
"ord_ref"=@P5 AND "ref1"=@P6 AND "ref2"=@P7 AND "notes" IS NULL AND
"invno"=@P8', N'@P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(12),@P5
varchar(4),@P6 varchar(10),@P7 varchar(4),@P8 int', 'testts', 246, 'Feb
3 2003 12:00:00:000AM', 'p02062693446', 'test', '0203000085', 'test', 246

This screen in ADP simply times out. In SQL Server locks, I can see that
this connection is blocked by itself?! (how is that possible).

I run the same script in query analyser, the script completes under 1
sec.

Any idea what is going on?

I have a trigger on the underlying table that is being updated

CREATE TRIGGER ASAS_UPD_TAFE_HDR ON [dbo].[TAFE_HDR]
FOR UPDATE
AS
if exists (select * from master.dbo.sysprocesses where program_name like
'microsoft office%' and spid=@@spid)
begin
update
a
set
a.transdate=b.created_date,
a.ref2=b.enrolmentno,
a.x_enrol_id=b.enrolmentno,
a.ord_ref=b.ord_ref,
a.ref1=b.ref1
from
dr_trans a inner join inserted b on
a.jobno=b.seqno


insert into asas_audit
select
'Update Invoice Header',
system_user,
host_name(),
getdate(),
invno
from
inserted
end

I don't think it makes any difference. I have included it just in case if
anyone can spot any problem here.

Thanks in advance
Click to expand...
Click to expand...
 
For the Views and the Stored Procedures (SP), there are absolutely no
difference for the update: ADP will do it in exactly the same way for both
of them.

The Resync command is used by ADP after the update is done to refresh the
data: it is essentially the same SP as the SP used as the source for the
form but with only a single (usually) parameter - which is the primary key -
and that return all the field for the current record.

For example, if the following SP is the record source for the form:

Create Procedure dbo.Source (@a int, @b int) as
Select ID, V1, V2, V3 from table T where T.a >= @a and T.b <= @b

then the following could be used as the resync command:

Create Procedure dbo.Source_Resync (@ID int) as
Select ID, V1, V2, V3 from table T Where T.ID = @ID

and the Resync property will be: « Source_Resync ? » (without the quote).
For another example, see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprodynupdateresync.asp

Don't forget to set the Unique Table property, too.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


C.W. said:
Hi Silvain

Really appreciate the help. I had to kick myself when I looked at the
trigger again, and realised that I did force to trigger to run only if the
update query is issued out of an office application, and hence there
wasn't a problem when I issue the update command from query analyser.

Now can you help me with using the resync and parameter options of forms?
I looked at the documention and couldn't figure out how they can be used.
The reason I used a view is because I couldn't figure out how to update
back to the database when data is retrieved through stored proc.

Many thanks for your help

Regard
Sylvain Lafontaine said:
From the test « where program_name like 'microsoft office%' ... »; I
don't think that much of this trigger is running when you run this script
in the query analyser.

Have you took the precaution of setting a Resync Command? Otherwise, ADP
will make independant requeries for all tables and this could lead to a
deadlock because these commands are run asynchroneously.

Does this problem occurs only when you are closing the form or if it
happens too when you are moving from record to record?

You could also try with an INSTEAD OF Trigger for your view (personally,
I never used them with ADP but we never know).

Finally, by default, ADP opens three connections to the SQL-Server, not
just one; so it's quite possible that the self blocking connection might
be two different connections instead.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


C.W. said:
I have got an updateable snapshot based on a view. When close the form,
it automatically sends out the following script to update the underlying
table.

exec sp_executesql N'UPDATE "arpos".."tafe_hdr" SET "ref2"=@P1 WHERE
"seqno"=@P2 AND "created_date"=@P3 AND "enrolmentno"=@P4 AND
"ord_ref"=@P5 AND "ref1"=@P6 AND "ref2"=@P7 AND "notes" IS NULL AND
"invno"=@P8', N'@P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(12),@P5
varchar(4),@P6 varchar(10),@P7 varchar(4),@P8 int', 'testts', 246, 'Feb
3 2003 12:00:00:000AM', 'p02062693446', 'test', '0203000085', 'test',
246

This screen in ADP simply times out. In SQL Server locks, I can see that
this connection is blocked by itself?! (how is that possible).

I run the same script in query analyser, the script completes under 1
sec.

Any idea what is going on?

I have a trigger on the underlying table that is being updated

CREATE TRIGGER ASAS_UPD_TAFE_HDR ON [dbo].[TAFE_HDR]
FOR UPDATE
AS
if exists (select * from master.dbo.sysprocesses where program_name like
'microsoft office%' and spid=@@spid)
begin
update
a
set
a.transdate=b.created_date,
a.ref2=b.enrolmentno,
a.x_enrol_id=b.enrolmentno,
a.ord_ref=b.ord_ref,
a.ref1=b.ref1
from
dr_trans a inner join inserted b on
a.jobno=b.seqno


insert into asas_audit
select
'Update Invoice Header',
system_user,
host_name(),
getdate(),
invno
from
inserted
end

I don't think it makes any difference. I have included it just in case
if anyone can spot any problem here.

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top