Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SharePoint 2013 update external list with CSOM timeout

Posted on 2015-01-12
14
Medium Priority
?
638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
14 Comments
 
LVL 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 52

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
 
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 52

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 52

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

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.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

718 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