MS SQL Server blocked.
Joe Boyle
joeboyle_adt@hotmail.com
Wed, 21 Apr 2004 11:40:32 +0100
<html><div style='background-color:'><DIV class=RTE>
<P>I looked up my notes on this topic and found that the problem is due to the fact that the lookup is done in the process phase which uses the update TX as Peter mentions below. </P>
<P>There are three other mainstream solutions, you can either,</P>
<P>1. try syntax ' transaction model optimistic' on the screen statement, </P>
<P>2. add syntax 'transaction query for query process' to the file statement</P>
<P>3. create a read only designer TX ( myrotx ) and associate this with the file 'transaction myrotx for query process'. I think that the update phase will default to using the update TX when you write to the file using the last option.</P>
<P>In case anyone is wondering where I got alll of this great info, I found it in the notes I made from the two Relational Interface manuals which accompany the Cognos courses on this subject - I made notes of anything interesting. The problem is that I managed to throw the manuals away ( I kept them in a bin liner in the boot of my car for easy access - doh!). If anyone could supply me with copies, I would be more than happy to pay for them.<BR><BR>regards Joe</P></DIV>
<DIV></DIV>>From: "Peter Bateman" <shediac92@hotmail.com>
<DIV></DIV>>To: powerh-l@sowder.com
<DIV></DIV>>Subject: MS SQL Server blocked.
<DIV></DIV>>Date: Tue, 20 Apr 2004 16:07:34 -0300
<DIV></DIV>>
<DIV></DIV>>Brian:
<DIV></DIV>>
<DIV></DIV>> An autocommit on the lookup statement should work. I think the
<DIV></DIV>>lookup is done
<DIV></DIV>> in the UPDATE transaction but I am not sure. DBAUDIT=FULL on the
<DIV></DIV>>run of QUICK
<DIV></DIV>> should tell you. You must have a unique key if your want to update
<DIV></DIV>>an MS SQL Server row.
<DIV></DIV>> This key can defined in MS SQL Server and I also believe it can be
<DIV></DIV>>stated on the FILE or
<DIV></DIV>> CURSOR associated with the table.
<DIV></DIV>>
<DIV></DIV>>Regards,
<DIV></DIV>>Peter Bateman
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>>From: "chuck.reinke" <chuck.reinke@sbcglobal.net>
<DIV></DIV>>>To: "brian_matthewsbrian matthews"
<DIV></DIV>>><brian_matthews_bmw@hotmail.com>, <powerh-l@lists.sowder.com>
<DIV></DIV>>>Subject: Re: problem with 'lookup on' and writing to a SQLserver
<DIV></DIV>>>table
<DIV></DIV>>>Date: Mon, 19 Apr 2004 21:07:02 -0700
<DIV></DIV>>>
<DIV></DIV>>>I'm a little surprised to hear about this problem, but I haven't
<DIV></DIV>>>ever worked
<DIV></DIV>>>with SQLserver.
<DIV></DIV>>>
<DIV></DIV>>>However, Lookups are done be default with a read/write transaction
<DIV></DIV>>>just
<DIV></DIV>>>after Input procedure time, and therefore I would think the
<DIV></DIV>>>transaction
<DIV></DIV>>>should be committed by Process procedure time. You may have some
<DIV></DIV>>>non-default
<DIV></DIV>>>stuff on your File statements. Or the File statement for the lookup
<DIV></DIV>>>is the
<DIV></DIV>>>same as the one you are trying to update in the Process procedure.
<DIV></DIV>>>Reference
<DIV></DIV>>>files are by default opened for read only, which could cause a
<DIV></DIV>>>problem. I
<DIV></DIV>>>the files are the same, try using an alias and some other file type
<DIV></DIV>>>for your
<DIV></DIV>>>update.
<DIV></DIV>>>
<DIV></DIV>>>Finally, if you think there may be some bug, try doing a commit at
<DIV></DIV>>>the start
<DIV></DIV>>>of your Process procedure.
<DIV></DIV>>>
<DIV></DIV>>>Chuck
<DIV></DIV>>>----- Original Message -----
<DIV></DIV>>>From: "brian_matthewsbrian matthews"
<DIV></DIV>>><brian_matthews_bmw@hotmail.com>
<DIV></DIV>>>To: <powerh-l@lists.sowder.com>
<DIV></DIV>>>Sent: Tuesday, April 20, 2004 8:06 AM
<DIV></DIV>>>Subject: problem with 'lookup on' and writing to a SQLserver table
<DIV></DIV>>>
<DIV></DIV>>>
<DIV></DIV>>> > I have never used this list before so am not completely sure
<DIV></DIV>>>what to
<DIV></DIV>>>expect
<DIV></DIV>>> > so here goes : I am trying to write to a SQLserver table from a
<DIV></DIV>>>field
<DIV></DIV>>> > process procedure but the row is blocked if a 'lookup on' is
<DIV></DIV>>>also present
<DIV></DIV>>>on
<DIV></DIV>>> > the field, can anyone explain this, or offer a wokaround ?
<DIV></DIV>>> >
<DIV></DIV>>> > P.S we are from RMS and trying out SQLserver
<DIV></DIV>>> >
<DIV></DIV>>> >
<DIV></DIV>>>_________________________________________________________________
<DIV></DIV>>> > Express yourself with cool new emoticons
<DIV></DIV>>>http://www.msn.co.uk/specials/myemo
<DIV></DIV>>> >
<DIV></DIV>>> > = = = = = = = = = = = = = = = = = = = = = = = = = = = =
<DIV></DIV>>> > Mailing list: powerh-l@lists.sowder.com
<DIV></DIV>>> > Subscribe: "subscribe" in message body to
<DIV></DIV>>>powerh-l-request@lists.sowder.com
<DIV></DIV>>> > Unsubscribe: "unsubscribe <password>" in message body to
<DIV></DIV>>>powerh-l-request@lists.sowder.com
<DIV></DIV>>> > http://lists.sowder.com/mailman/listinfo/powerh-l
<DIV></DIV>>> > This list is closed, thus to post to the list you must be a
<DIV></DIV>>>subscriber.
<DIV></DIV>>>
<DIV></DIV>>>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
<DIV></DIV>>>Mailing list: powerh-l@lists.sowder.com
<DIV></DIV>>>Subscribe: "subscribe" in message body to
<DIV></DIV>>>powerh-l-request@lists.sowder.com
<DIV></DIV>>>Unsubscribe: "unsubscribe <password>" in message body to
<DIV></DIV>>>powerh-l-request@lists.sowder.com
<DIV></DIV>>>http://lists.sowder.com/mailman/listinfo/powerh-l
<DIV></DIV>>>This list is closed, thus to post to the list you must be a
<DIV></DIV>>>subscriber.
<DIV></DIV>>
<DIV></DIV>>_________________________________________________________________
<DIV></DIV>>Add photos to your messages with MSN Premium. Get 2 months FREE*
<DIV></DIV>>http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
<DIV></DIV>>
<DIV></DIV>>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
<DIV></DIV>>Mailing list: powerh-l@lists.sowder.com
<DIV></DIV>>Subscribe: "subscribe" in message body to
<DIV></DIV>>powerh-l-request@lists.sowder.com
<DIV></DIV>>Unsubscribe: "unsubscribe <password>" in message body to
<DIV></DIV>>powerh-l-request@lists.sowder.com
<DIV></DIV>>http://lists.sowder.com/mailman/listinfo/powerh-l
<DIV></DIV>>This list is closed, thus to post to the list you must be a
<DIV></DIV>>subscriber.
<DIV></DIV></div><br clear=all><hr>Stay in touch better and keep protected online with MSN’s NEW all-in-one Premium Services. <a href="http://g.msn.com/8HMAENUK/2740??PS=">Find out more here.</a> </html>