Solved

SharePoint 2013 update external list with CSOM timeout

Posted on 2015-01-12
14
556 Views
Last Modified: 2015-02-02
I am trying to update records in an external list (In MSSQL) using the client side object model. The function times out after about 90 seconds with only 20 records. What's weird is I can insert 100 records in a few seconds without any problems but when it comes to updating it times out. Below is the code I'm using. Any help would be greatly appreciated...

function updateListItems() {
	myShowWaitScreenWithNoClose();
    
    	var itemArray = [];
    	var clientContext = SP.ClientContext.get_current();
    	var oList = clientContext.get_web().get_lists().getByTitle('wst_Inventory');
        
        var mDate = new Date();
		var user = sp_GetUser();
    	var ID;
    	var oListItem;
    	
    	$(".c_ShortNo").each(function(i) {
    	    ID = parseInt($(this).val());
    		//alert(ID);
    	    oListItem = oList.getItemById(ID);  
    	    oListItem.set_item('Active', 'FALSE');
            oListItem.set_item('Modified', mDate);
            oListItem.set_item('ModifiedBy', user);  
    	    oListItem.update();
    	    itemArray[i] = oListItem;
    	    clientContext.load(itemArray[i]);
    	});
    	    
    	clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed);
	}

Open in new window

0
Comment
Question by:snieves
  • 6
  • 5
14 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40548580
How many records do you have in that table?
And the table has a primary key?
0
 
LVL 1

Author Comment

by:snieves
ID: 40548810
There are currently 13747 rows in the table and yes it does have a primary key (ID). I can push and pull from the table just fine using SPServices and CSOM but updating times out when updating more than 5 records.
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40548845
I can only help you from the database side but will need to know the UPDATE statement that is being executed.
0
 
LVL 1

Author Comment

by:snieves
ID: 40550109
Not sure really. The table is in SQL and is imported as an external content type into a SharePoint 2013 Foundation site. Where can I find the update statement?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40550831
I'm not a SharePoint expert. I wouldn't be surprised if the update statement is building in the background by SharePoint. If so, the only way to know the update command is to run a SQL Profiler in the database side to capture the command sent by SharePoint.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:snieves
ID: 40551143
Vitor, is it possible that my MSSQL table is locking? I apologize I am not as well versed with MSSQL as I should be but is it possible it's timing out because of table locking?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40551189
It must possible that locking are occurring in the database side. That's why I asked for the Update command.
If you can run a SQL Profiler would help.
0
 
LVL 1

Author Comment

by:snieves
ID: 40551425
Again please excuse my ignorance. How should I configure the profiler to make sure I'm looking at the update commands?
0
 
LVL 1

Author Comment

by:snieves
ID: 40557796
Here is the SQL command that is running for each update call:

exec sp_executesql N'UPDATE [dbo].[wst_Inventory] SET [WID] = @WID , [Descriptor] = @Descriptor , [Weight] = @Weight , [Tare] = @Tare , [Net] = @Net , [ContainerType] = @ContainerType , [ContainerSize] = @ContainerSize , [FID] = @FID , [LID] = @LID , [OneYear] = @OneYear , [Comments] = @Comments , [Active] = @Active , [Created] = @Created , [CreatedBy] = @CreatedBy , [Modified] = @Modified , [ModifiedBy] = @ModifiedBy , [FormType] = @FormType , [FormID] = @FormID , [OldInv] = @OldInv WHERE [ID] = @ID',N'@ID int,@WID int,@Descriptor nvarchar(13),@Weight int,@Tare int,@Net int,@ContainerType nvarchar(2),@ContainerSize nvarchar(2),@FID int,@LID int,@OneYear datetime,@Comments nvarchar(5),@Active bit,@Created datetime,@CreatedBy nvarchar(12),@Modified datetime,@ModifiedBy nvarchar(12),@FormType nvarchar(9),@FormID int,@OldInv int',@ID=5001,@WID=8,@Descriptor=N'For Recycling',@Weight=544,@Tare=20,@Net=524,@ContainerType=N'DM',@ContainerSize=N'55',@FID=2,@LID=7,@OneYear='2015-09-16 00:00:00',@Comments=N'Test1',@Active=1,@Created='2015-01-05 00:00:00',@CreatedBy=N'Shaun Nieves',@Modified='2015-01-05 00:00:00',@ModifiedBy=N'Shaun Nieves',@FormType=N'Container',@FormID=0,@OldInv=357875

Open in new window

0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40559264
Hi snieves.

Here is a link to show you how to create and run a SQL Profiler: http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step.

You can also find tutorials in the internet by searching for "SQL Profiler tutorial" or "SQL Profiler step by step".

Try to capture queries and locks and then return here with your findings.
Good luck.
0
 
LVL 1

Author Closing Comment

by:snieves
ID: 40584415
I ended up using a stored procedure in the external content type and that got around the timeout issue. Thanks for pointing me in the right direction!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now