SQL deadlock problem

I am currently having a big issue with sql deadlocking on the 
PrincipalObjectAccess table. 

The last few months I have been working on a synchronization process using a 
Biztalk orchestration. The sync uses the crm webservices to create and update 
account and contact records in CRM. But now deployment to the production 
environment gives me some problems. It seems that when trying to update 
account records (which is one of the first actions in the sync process) the 
webservice gives me Generic SQL errors and SQL timeouts. After extensive 
profiling and tracing in SQL I found that there are two sql statements that 
are been called several times involving the PrincipalObjectAccess table 
(something to do with getting the account.owner and checking for inherited 
access). After going through this cycle several times the webservices throws 
the sql exception.  Tracing determined that there are deadlocks occuring on 
the PrincipalObjectAccess table. When continuing the syncing process 
eventually the worker thread crashes.

Some more info:
The CRM webclient seems to be working fine with updating and creating. 
Rollup 2 was installed but did not solve the problem. 
Development and test environment does not have this problem, but also does 
not have security roles in place. The account used for synchronizing has a 
administrator role. 
When skipping account updates, the next entity synced is contact. The same 
problem occurs here.. Other entities seem to sync ok ( when the worker thread 
did not crash before..)

Any ideas on where to look for solutions?
0
Daisy (15)
9/10/2007 6:46:01 AM
crm 35858 articles. 1 followers. Follow

3 Replies
655 Views

Similar Articles

[PageSpeed] 21

Just wondering if you have any callouts registered with the account
that actually use SQL to change the account permission directly from
the PrincipalObjectAccess table. If so, remove the callouts to see if
you are getting the same issue.

Darren Liu
Crowe
http://www.crowecrm.com

On Sep 10, 1:46 am, Daisy <Da...@discussions.microsoft.com> wrote:
> I am currently having a big issue with sql deadlocking on the
> PrincipalObjectAccess table.
>
> The last few months I have been working on a synchronization process using a
> Biztalk orchestration. The sync uses the crm webservices to create and update
> account and contact records in CRM. But now deployment to the production
> environment gives me some problems. It seems that when trying to update
> account records (which is one of the first actions in the sync process) the
> webservice gives me Generic SQL errors and SQL timeouts. After extensive
> profiling and tracing in SQL I found that there are two sql statements that
> are been called several times involving the PrincipalObjectAccess table
> (something to do with getting the account.owner and checking for inherited
> access). After going through this cycle several times the webservices throws
> the sql exception.  Tracing determined that there are deadlocks occuring on
> the PrincipalObjectAccess table. When continuing the syncing process
> eventually the worker thread crashes.
>
> Some more info:
> The CRM webclient seems to be working fine with updating and creating.
> Rollup 2 was installed but did not solve the problem.
> Development and test environment does not have this problem, but also does
> not have security roles in place. The account used for synchronizing has a
> administrator role.
> When skipping account updates, the next entity synced is contact. The same
> problem occurs here.. Other entities seem to sync ok ( when the worker thread
> did not crash before..)
>
> Any ideas on where to look for solutions?


0
Darren
9/10/2007 3:47:48 PM
no, I do not have such callouts. 
I do have callouts registered but if that was the problem I would expect the 
webclient to have issues as well. But in any case I tried synchronizing with 
the callouts turned off and this did nog help either. 

Any other ideas?

"Darren Liu" wrote:

> Just wondering if you have any callouts registered with the account
> that actually use SQL to change the account permission directly from
> the PrincipalObjectAccess table. If so, remove the callouts to see if
> you are getting the same issue.
> 
> Darren Liu
> Crowe
> http://www.crowecrm.com

0
Utf
9/12/2007 6:48:00 AM
I looked into my problem further.

It seems I am having issues with the crm webservice only. When I try to use 
the webservice to set the parentaccount of an account to null I am getting 
sql time outs exceptions. It seems sql deadlocks on the principalobjectaccess 
table and gets into a loop requesting and updateing values..

Using the crm web client I can set and unset the parentaccount without any 
problems although it also seems a bit slow. 



Any help is appreciated!
0
Utf
9/17/2007 2:34:05 PM
Reply:

Similar Artilces:

problem with query in ASP using date()
Hi. Hi, I am novice to ASP,I am using vb script and MSacceess for database.When ever I use the following code it gives the error.I am sure the problem is with entry_datetime='#"data"#' but not sure how to resolve it.It would be great if some one helps me in resolving the following query. . <% data=date() dim rs1,sql1 Set rs1 = Server.CreateObject("Adodb.RecordSet") sql1="select * from web where format(entry_datetime,'"mm/dd/yyyy hh:min:ss"') =format('#"data"#','"mm/dd/yyyy hh:min:ss"')...

Publisher 2007 Print Preview problem
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C848A1.9D928960 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I use a custom template 8.5x44, fill it with text and pictures, but = print preview show 5 pages, with the text and pictures centered over the = 5 pages, meaning that the first and last half pages are blank. That is, = the text and pictures begin mid page, not at the top as my document has = them. And the bottom of the fifth page is blank. What am I doing wrong? Al ------=_NextPart_000_0006_01C848A1.9D...

C++ pure virtual member function problem
Hi, Today I've faced a problem in C++ that I never thought could be a problem!! It's all about pure virtual member functions, consider class Base has two member functions. A pure virtual member function named: virtual void pvmf() = 0; Now the second function, it's a normal function named: void normal(); What if I need to call the pvmf function in normal one, is it possible?!? I mean I always thought there should be no problem, but VC++ 7.1 gives me a linker error that the pvmf's definition, which is called within normal function, can not be found!! Sure when I call the pvmf, it...

Problems converting from Publisher 97 to Publisher 2003
I have hundreds of Publisher 97 files that need converted to Publisher 2003 files. However, in the conversion process many of the files converted badly: tables are now much bigger and don't fit on the existing pages as before. Anyone have any ideas on how this can be fixed? Wendy there is nothing you can do about this. The only way it can be fixed is the armstrong method - manually. -- "If you don't know where you are going, any road will take you there!" >-----Original Message----- >Wendy there is nothing you can do about this. > >The only way it ...

Need to solve problem w/ RSS feeds
Hi -- I have a Windows XP computer running Outlook 2003 with an Exchange 2003 server in the background. Outlook is running RSS Popper to retrieve 15 RSS feeds, each of which is saved in a separate folder in the user's Exchange mail store. Now I have a Windows Vista laptop running Outlook 2007 with that same Exchange 2003 server in the background. Outlook here also (natively) retrieves these 15 RSS feeds and saves them into the same folders in the user's Exchange mailbox. The problem I'm having is that neither RSS aggregator knows what the other is doing. I wind up with to...

Date formatting problem
I have an unusual problem and would appreciate any help. I have a spreadsheet that has date values in it, such as "2/3/1972". In the formula bar it is correct (includes an = sign at the beginning), but it displays "1/1/1900" in the cell and uses that value in calculations. When I apply a different date format, it still shows some variation of "January 1, 1900" in the cell and I can't get it to change. Any ideas? Thanks. I think that excel is seeing your =2/3/1972 as a giant division problem. =(2/3)/1972 which evaluates to: 0.000338066 which is 01/00/...

IE8 problem 12-07-09
I keep getting the following message when I try to use IE8 on my desktop:- "Internet Explorer has encountered a problem and needs to close. We are sorry for the inconvenience." I have used CCleaner to get rid of cookies, temporary files etc with no joy. I have also uninstalled IE8 and returned to IE7 and everything worked fine. I then reinstalled IE8 and and was able to use Internet Explorer until I rebooted the computer. Back to square one, no internet.. I know that I should now return to IE7 but would first like to know whether there is a fix for this problem. As ...

Multiple inheritance problem
Hi, I want to make a class, JNDlg, which is derived from two MFC-classes (CDialog and CStringArray). The class declaration (a bit simplified) looks as follows: class JNDlg : public CDialog, public CStringArray { // Construction public: JNDlg(CWnd* pParent = NULL); // standard constructor // Dialog Data //{{AFX_DATA(JNDlg) enum { IDD = IDD_JNW }; //}}AFX_DATA //{{AFX_VIRTUAL(JNDlg) protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support //}}AFX_VIRTUAL protect...

Sumproduct in VBA Type Mismatch problem
Just wanting to count multiple criteria and plan to expand this function once I know it is error free. I think I have some format off or something, please help I get the message "type mismatch". mquestion1range has numerical data which should sum if the mTimeCriteria is found to match. Dim mTimeCriteria, mPositionCriteria As String Dim mQuestion1Range, mTimeRange, mPositionRange As Range Dim mFormula As String Dim mCount As Long mTimeCriteria = "First day of employment (Time 1)" mPositionCriteria = "Registered Nurse" Set mPositionRange = Works...

Problem getting Excel formula to Calculate
Hello, I'm building a spreadsheet to analyze how coupons are being redeemed within a range of postal codes and how revenue is affected between new and repeat customers. One of the formulas I'm using is: {=SUM(IF('ENTER INFO HERE'!$F$4:$F$15000="T9E",IF('ENTER INFO HERE'!$G$4:$G$15000="F125",'ENTER INFO HERE'!$C$4:$C$15000,0),0))} Column A= Store # Column C= New Customers Column D = Repeat Customers Column E= Revenue Generated Column F= Postal Code Column G= Coupon Code Column I= True or false value dependant on the value of Co...

Foreign key problem
Ok, I have a situation with an add on. If my table in my add on contains an item id, I want that record to not allow deletion in the RMS manager program. So I added a foreign key which prevents this. Unfortunately, this causes the RMS manager program to interpret this as a fatal error and crashes the program. Next try: Remove the foreign key. Add a delete trigger that checks for my condition and use RAISEERROR and roll back the transaction if the item is not allowed to be deleted. This has the benefit of me controlling what is in the message box, but the rollback in the trigger is also being ...

Problem with modal dialogs
Hello! I have a problem that I would appreciate help with. The scenario is this: - My program makes a synchronous DDE transaction. - While this transaction is in progress, my program shows a modal dialog box for some reason (user interaction, a timer message, etc). When this happens the DDE transaction hangs. The timeout specified in the DDE transaction isn't cared about either, nothing happens. After the modal dialog box is closed the DDE transaction call releases after some time (usually just takes a few seconds) and the function can continue with it's task since the synchrono...

Problems recovering deleted public folder
Hi! One of our public folders in Exchange 2000 was deleted. I have the Item Recovery configured for the public folder store in question. When the user that deleted the folder tries to recover it using Outlook 2002 (Recover Deleted Items) he gets an error message saying that he does not have permission to recover the folder. When I try to recover as administrator, I cannot even see the deleted folder in Outlook. I tried to make the user Enterprise admin, and domain admin, but that did not help. Is it only the owner of the folder that can recover it, or am I doing something wrong? Best Regards...

Front End problem
We have an Exchange 2003 (Win2003) cluster that supports roughly 2500 users. I want to add a front end server so that the OWA burden is no longer on the cluster and, hopefully, to improve security a bit. All three servers are behind a firewall. I installed Exchange on a brand new server with no problem. If it's not listed as a front end server, and you try to log into it via OWA, it passes you immediately to the OWA instance we have been using on the cluster. Once you hit the checkmark for 'this is a front end server', it no longer works. Whenever I try to log onto OWA o...

Problem with domain.local
Some users in the domain doesn't have a valid external e-mail adresse. When they send a external mail they use send as with a valid user adresse (user define in AD with a primary valid email adresse). The primary adresse for that user without valid adress is something like domain.local. If the user forget to change the From in the email the mail get stuck in the queue because the ISP block that kind of email and any other good email send after that mail will stay in queue. There is a way to restrict mail send from *@domain.local. I don't want to restrict user from sending but on...

reply text color problem
Outlook 2003. When I go to reply to emails, the text is black. I want it to be blue. It's usually blue automatically, but nothing I do seems to get it that way. I've tried various options from the tools menu but nothing seems to be working. Any ideas? Thanks. Ken what message format are you replying to? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

Problems in reading XML file into dataset in vb.net
Hi, I am facing some problems in reading XML file into dataset in vb.net. The XML is having some duplicate tabels. I am getting the error message saying "The same table(imei_details) cannot be the child table in two nested relations" Here is the XML structure i am trying to read... <?xml version="1.0"?> <despatch_reply version="1.1"> <date>4 Aug 2004</date> <time>09:03:19</time> <system_name>abc</system_name> <error> </error> <orders> <order> <order_number>111<...

Problems with Undo-ing a deleted message
While cleaning up my inbox and deleting entries, if I change my mind and issue the Undo command, about 75% of the time, Outlook does not restore the most recently deleted item, but one from several deletions past. What causes this?? Rick A. Pleasanton, CA ...

Problem sending an email to a particular domain
In sending an email to a United Kingdom domain and the mail is sitting in the Que with the message "Host Unreachable". I did a DIG to the domain and was able to telnet to their mail server with no problem. Any ideas as to what else I can check? Have you dig it about the UK MX RECORD? On the exchange server, make the dig and nslookup. Enable tracking message on exchange server. Have you try sending an email with Hotmail or other email account? Have you checked for any Email filter, etc >-----Original Message----- >In sending an email to a United Kingdom domain and the &g...

Having problems accessing external network SMTP servers on port 25
Hi Guys, Just installed Windows Small Business Server 2008 Standard, with Service Pack 2. Now trying to get Exchange Server 2007 to send external emails to my ISPs smart host. The SBS machine has IP:192.168.1.220, I have a ADSL router at IP:192.168.1.1 and a test client running XP Pro at IP:192.168.1.89 I have temporarily disabled the Firewall in SBS 2008. So; if I do; telnet 80.168.45.10 25 from the XP machine I get this; 220 relay.clara.net ESMTP [relay05] telnet 192.168.1.220 25 from the XP machine I get this; 220 FACTORYSRV.nsl.local Microsoft ESMTP MAIL Service re...

Problems with address book
I'm new to using MS outlook. I usually work on *nix servers but this time I am working with Active Directory and facing some problems. I want to see only the people whose account are active in my address book. Currently I see all the people's e-mail addresses when I click on 'To' button. Many of those accounts are disabled, but sitll I see there addresses. Also if I want to send e-mail to everyone, it also sends mails to those people whose accounts are disabled and ultimately creates a lot of confusion. Is there a way that I can see people whose accounts are currently active,...

excel problem #5
hi i am using excel 97 english to write four files and then copied it to hp 3715 ppc which is using pocket excel 2002 chinese. i read it through hp 3715. i do not change it anything. the question is when i copied it to my computer and then i am using excel 97 to open, i cannot open it. i found that the file extension is pxl so that i change it to xls and then open it again, the sheet is empty. please help me thanks jimmy Is this a translation problem? - in that the Pocket Excel 2002 Chinese can read English but writes Chinese, and that the .pxl file (written in Chinese) is not unde...

export chart
I'm converting a chart to gif with: Activechart.export filename:=chartfname, filtername:="GIF" It used to work fine but now the chart size is incorrect. The charts looks exactly the same on the spreadsheet. Initially the gifs were 846 by 625, now they are 643 by 625. What controls the size of the exported image (other than the screen resolution)? thanks, -- Art Art - Embedded charts? Same zoom (in fact, I suggest that you keep it at 100% and adjust fonts and stuff accordingly)? Funny that the width is the same.... Are the charts created in code? Are the Exce...

Problem opening an.xls or xlsx file
Excel 2007 (Office 2007), WinXPSP2, 1.56GigRam, 60Gbt free, 2.1GHz processor. When I select an .xls or .xlsx file from the file manager (Windows Explorer), Excel immediately starts, instead of a blank spread sheet the screen below the menu bars is pale blue. For about three seconds the hourglass displays, then the regular mouse pointer appears. Thirty seconds to 90 seconds later the file opens. If I open a previously unopened file directly from Excel using its file management function, it opens immediately. Same is true if I open a previously opened file from the Excel file manager, it...

Problem trying to print "from page --- TO PAGE___"".
Problem trying to print "from page --- TO PAGE___"". When I try to do this,, the preview shows nothing and the page numbers go to 0. Only if I start printing from page 1 does it all print,, but that is a waste.. Why is this happening?? I can select individual pages in PDF files?? Thanks.. I have an intel mac,, 10..4.9,, and have upgraded Office to 11..3.5.. Thanks. Are there section breaks in your file? (columns, places where the numbering restarts or the page orientation changes?) If so, you need to tell Word both the s...