Solved

Sql server timeout error when updating user profile

Posted on 2010-09-10
6
666 Views
Last Modified: 2012-05-10
Hello,

I have a LINQ recordset (of users and their username) that I want to loop through and update a user's profile value.  The profile information is based on the default asp.net membership tables.

The following is my code and the timeout error is happening on the userProfile.save() line
Dim dbRGT As New RegentSignsDataContext

        Dim myResults = From myTable In dbRGT.vw_USERS_GroupEmails

        For Each myRow In myResults
            ''The following line writes out to the OUTPUT window during Debug
            ''System.Diagnostics.Debug.Write(myRow.chrFullName.ToString + " = " + myRow.UserId.ToString)
            Dim userProfile As ProfileCommon = ProfileCommon.Create(myRow.UserName, True)
            userProfile.groupEmail = 1
            userProfile.Save()            
        Next

Open in new window


The specific error I am getting is:
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. I should add that if I take out the profile.save I can loop through the recordset and perform other functions with no problem.

Can anyone help me figure out why this error is happening.  All other connections to the SQL server are working and have never thrown a timeout error.  

I even tried adding this code into the function in desperation:
System.Data.SqlClient.SqlConnection.ClearAllPools()
but it did not help at all
0
Comment
Question by:jordanking
  • 3
  • 3
6 Comments
 
LVL 9

Accepted Solution

by:
wellhole earned 500 total points
ID: 33650520
Wherever your profilecommon executes the sql, you should set the command timeout to a value higher than the default.
0
 
LVL 1

Author Comment

by:jordanking
ID: 33650825
Thanks for the quick reply,

I changed my connection string in the web.config file which references a development database on a sql server 2008 express edition on the local computer where visual studio is running

the connection string is now:
   

This did not solve the problem.  I believe this changed the timeout to 30 seconds.  
0
 
LVL 9

Expert Comment

by:wellhole
ID: 33651104
I expect that the default timeout is 30 seconds. You should increase it to 900 and see if that fixes it.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:jordanking
ID: 33651187
I made that change but it did not work.  It did not wait 900 seconds and it threw the error same as before
0
 
LVL 9

Assisted Solution

by:wellhole
wellhole earned 500 total points
ID: 33651201
Then, I hope your sql is formatted correctly such that it's not trying to wipe out the whole database in 1 shot.

Besides that, see if you can change this timeout for the COMMAND and not the connection.
0
 
LVL 1

Author Comment

by:jordanking
ID: 33651785
okay,

so i took out LINQ data altogether and tested my code with just one record and it worked.  So then I tried adding the following code right below the datacontext declaration:
dbRGT.CommandTimeout = 9000

however that did not extend the time at all, so i thought it must be something with how i am getting the users profile.  So i changed the way I get all the users and it works.  The commandtimeout was a valueable lesson for using LINQ and SQL datacontext commands, so thanks for pointing me in that direction.

My working code is now:
Dim chrUsername As String

        Dim muc As MembershipUserCollection
        muc = Membership.GetAllUsers()
        Dim mu As MembershipUser
        For Each mu In muc
            chrUsername = mu.UserName
            Dim userProfile As ProfileCommon = ProfileCommon.Create(chrUsername, True)
            userProfile.groupEmail = 1
            userProfile.Save()
            ''iRet = myEmail.GroupEmail(myRow.Email, myRow.chrFullName, myRow.UserId.ToString, guidEmail)
        Next

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.net capture  result of a SQL query in variables 3 21
"Emulate" TAB key when press Enter Key 3 48
Very Large data in MYSQL 7 73
IEnumerable<T> to a List<T> 8 40
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

827 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