Solved

SharePoint 2013 update external list with CSOM timeout

Posted on 2015-01-12
14
545 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 45

Expert Comment

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

Author Comment

by:snieves
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:snieves
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

772 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

9 Experts available now in Live!

Get 1:1 Help Now