|
nixhex7332
Starting Member
4 Posts |
 Posted - 2009-02-02 : 08:39:00
|
| So I determined frm a Blocked Process report that my Access 2007 database, which is linked to both tables and views of those tables, on my SQL 2005 box, is causing a blocked process on a very important table, essentially locking the table. It only works again when I restart the SQL Server service. What could cause this? Should I not use linked tables/views? Should I force a fresh import when the database initiates, instead?Below, REGISTRATION_LOOKUP is a view on my SQL server. Some other important facts: all the users have read-only access to the Access database, and the user account that the ODBC connection is using (MCDREG_READ) has read-only access to everything in the database. Here is the detail from my blocked process report:<blocked-process-report> <blocked-process> <process id="process6e8e38" taskpriority="0" logused="0" waitresource="OBJECT: 16:2027154267:0 " waittime="19984" ownerId="11146363" transactionname="UPDATE" lasttranstarted="2009-01-30T14:08:30.657" XDES="0x3ef4250" lockMode="IX" schedulerid="1" kpid="2252" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-30T14:08:30.657" lastbatchcompleted="2009-01-30T14:08:30.657" clientapp="Internet Information Services" hostname="MCDAPP02" hostpid="5484" loginname="MCAP" isolationlevel="read committed (2)" xactid="11146363" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame line="1" stmtstart="1262" sqlhandle="0x02000000d305f31975aafd203769cac50dfaed352967b48c"/> <frame line="1" sqlhandle="0x02000000aa34dd34c08b9e7ef12bbbf709422bae8ed54c4e"/> </executionStack> <inputbuf>update MCAP.REGISTRATION set FK_REGISTRATION_TEMPLATE=114, CHECK_NO='',PAID=0, PAYMENT_AMOUNT=0,PAYMENT_TYPE='WAIVED',PAYMENT_DETAIL='Conference only', FK_REG_DATES=0, FNAME='JEN',LNAME='OLSEN', SUFFIX='',CREDENTIALS='',JOB_TITLE='BUSINESS COORDINATOR',ORGANIZATION='KATAHDIN AREA CHAMBER OF COMMERCE',SCHOOL='',SCHOOL_DISTRICT='',ADDRESS='1029 CENTRAL STREET',ADDRESS2='',CITY='MILLINOCKET',STATE='ME',ZIP='04462',HOME_PHONE='207-723-5095',WORK_PHONE='207-723-4443',FAX='',EMAIL='KACC@BEELINE-ONLINE.NET',TEAM_NAME='',TEAM_LEADER='',GRANTS='',SESSION1='',SESSION2='',SESSION3='2. Portobello wrap served with potato salad (vegetarian choice)',SESSION4='',SESSION5='',SESSION6='',SESSION7='',SESSION8='',SESSION9='',SESSION10='' where PK_REGISTRATION=6687; </inputbuf> </process> </blocked-process> <blocking-process> <process status="suspended" waittime="1343" spid="167" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2009-01-30T14:06:14.577" lastbatchcompleted="2009-01-30T14:06:14.577" lastattention="2009-01-30T14:06:03.937" clientapp="2007 Microsoft Office system" hostname="D9P4RJ81" hostpid="3240" loginname="MCDREG_READ" isolationlevel="read committed (2)" xactid="11144255" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame line="1" sqlhandle="0x020000004337922d13680c610e4f3df5e86ac73f0ece5084"/> </executionStack> <inputbuf>SELECT "MCAP"."REGISTRATION_LOOKUP"."PK_REGISTRATION" ,"MCAP"."REGISTRATION_LOOKUP"."FK_REGISTRATION_TEMPLATE" ,"MCAP"."REGISTRATION_LOOKUP"."EVENT_TITLE" ,"MCAP"."REGISTRATION_LOOKUP"."LOCATION" ,"MCAP"."REG_DATES"."REG_DATE" ,"MCAP"."REGISTRATION_LOOKUP"."START_DATE" ,"MCAP"."REGISTRATION_LOOKUP"."END_DATE" ,"MCAP"."REG_DATES"."LOCATION" ,"MCAP"."REG_DATES"."REG_DATE" ,"MCAP"."REGISTRATION_LOOKUP"."FNAME" ,"MCAP"."REGISTRATION_LOOKUP"."LNAME" ,"MCAP"."REGISTRATION_LOOKUP"."SUFFIX" ,"MCAP"."REGISTRATION_LOOKUP"."CREDENTIALS" ,"MCAP"."REGISTRATION_LOOKUP"."JOB_TITLE" ,"MCAP"."REGISTRATION_LOOKUP"."CREDIT_DATE" ,"MCAP"."REGISTRATION_LOOKUP"."ORGANIZATION" ,"MCAP"."REGISTRATION_LOOKUP"."SCHOOL" ,"MCAP"."REGISTRATION_LOOKUP"."SCHOOL_DISTRICT" ,"MCAP"."REGISTRATION_LOOKUP"."ADDRESS" ,"MCAP"."REGISTRATION_LOOKUP"."ADDRESS2" ,"MCAP"."REGISTRATION_LOOKUP"."CITY" ,"MCAP"."REGISTRATION_LOOKUP"."STATE" ,"MCAP"."REGISTRATION_LOOKUP"."ZIP" ,"MCAP"."REGISTRATION_LOOKUP"."WORK_PHONE" ,"MCAP"."REGISTRATION_LOOKUP"."HOME_PH </inputbuf> </process> </blocking-process></blocked-process-report> |
|
Blocked Process Happening With Access 2007 links?