Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 692
  • Last Modified:

SharePoint 2013 update external list with CSOM timeout

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
snieves
Asked:
snieves
  • 6
  • 5
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How many records do you have in that table?
And the table has a primary key?
0
 
snievesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can only help you from the database side but will need to know the UPDATE statement that is being executed.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
snievesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
snievesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
snievesAuthor Commented:
Again please excuse my ignorance. How should I configure the profiler to make sure I'm looking at the update commands?
0
 
snievesAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
snievesAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now