CODEWITHSUNDEEP
asp.netsql-servertimeoutenterprise-libraryconnection-string
Nikola
Nikola

Reputation:

Why does Sql Server connection TimesOut

I am using Enterprise Library Data for my Sql database. I am using version 3.1. I am using this code to execute a long running sp (about 1 min).

Dim db As SqlDatabase = New SqlDatabase(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("portalConnection").ConnectionString)
                    db.ExecuteNonQuery("spnametoexecute")

Connection string looks like this

<add name="portalConnection" connectionString="Server=IP;Database=DBName;uid=User;pwd=PWD; Timeout=180;"
  providerName="System.Data.SqlClient" />

Provlem is that I always get a TimeOut Exception. Why Is that ?

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Upvotes: 0

Views: 996

Answers (4)

Brad_Z
Brad_Z

Reputation: 345

I'm not too familliar with enterprise data library but I'd see if the SqlDatabase object has a timeout property. Try setting the timeout in your code and test the behaviour.

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

Do you ALWAYS get a timeout, or only when the long-running query executes? When you run it manually - that is, in management studio, how long does it take? Your connection is set to time out in 3 minutes, which is an ENORMOUS amount of time.

You need to attack this at a couple of levels:

  1. Profile & Optimize the query
  2. Test with very simple queries to see if there is a connection problem

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063338

Well, there are a few possibilities:

  • your query is too complex and takes too long (try writing better TSQL, adding indexes, or increasing the timeout)
  • you are being blocked by another SPID (perhaps yourself on another connection)

Often, the second is the problem. Look at the blockages etc (sp_who / sp_who2) to see if there are any blocks. And use a trace/profiler to investigate the first option.

Upvotes: 2

Maksim Kondratyuk
Maksim Kondratyuk

Reputation: 1399

I think you set incorrect server ip or your server doesn't configured as well. Check if your allow Pipes and tcp/ip in configuration of your server. It's very often forgetting.

Upvotes: 0

Related Questions