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

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" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ueL9RRyBGHA.2920@xxxxxxxxxxxxxxxxxxxxxxx
> 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." <c.w@nospam_com> wrote in message
> news:empZjtrBGHA.3396@xxxxxxxxxxxxxxxxxxxxxxx
>>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
>>
>>
>
>


.

Relevant Pages

  • Re: Flexibility of Eventriggers in Win2003 - query on source + wil
    ... I created a very broad event trigger that looks only at entries of /EID ... The event trigger runs a batch file that then calls a vbs script that uses ... Namely to tweak it so that it will check to see if it has sent the admin ...
    (microsoft.public.windows.server.general)
  • Script endlessly looping
    ... The following script has a loop that executes ftp to look for a trigger ... trigger file and renames the data file on the remote server. ...
    (comp.unix.shell)
  • Re: ADP code hangs while running the same query in query analyser works
    ... The Resync command is used by ADP after the update is done to refresh the ... > trigger again, and realised that I did force to trigger to run only if the ... >> be two different connections instead. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: How do I to make a Script avoid being blocked by popup blocker
    ... Using RegisterStartupScript means it is not triggered ... You may have had the user click to trigger a postback, ... had a startup script, however, that means the user triggered a postback, not ... often what triggers the popup blocker to block it. ...
    (microsoft.public.vsnet.general)
  • Re: [2.6.27] filesystem (ext3) corruption (access beyond end)
    ... Ok I ran several disk-intensive jobs (git clone, kernel build, apt-get dist-upgrade) ... but none of those could trigger any bugs. ... BUT, running my http://vanheusden.com/pyk/ script, I definately can trigger the ...
    (Linux-Kernel)