CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurestimeout
Jens
Jens

Reputation: 53

SP TimeOut in .NET code, not in Management Studio

If I run the following in Management Studio (SQL Server 2008) :

exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1'

I get the result back in less than a second

I however I run it in VB.Net like this :

Using aConnection = New System.Data.SqlClient.SqlConnection(*** Some Connection String ***)
  aConnection.Open()
  Dim aCmd = aConnection.CreateCommand()
  aCmd.CommandText = "exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1'"
  aCmd.ExecuteNonQuery()
  aConnection.Close()
End Using

it times out (I know that ExecuteNonQuery does not return data, but I tried to keep the code as small as possible).

I've used the same DB, UserID and password in code as in the Management Studio en the Isolation Level is Read Comitted.

Anybody has any idea ?

Upvotes: 2

Views: 1794

Answers (3)

Jens
Jens

Reputation: 53

Using SET ARITHABORT ON; fixed the problem.

Upvotes: 1

jasonk
jasonk

Reputation: 1570

Can change your CommandText to a simple query "UPDATE TableABC SET Field1 = Field1 WHERE ID = XYZ" to see if it's the proc or the connection string itself. My guess is the proc is fine and there is probably something off in the conn.

Upvotes: -1

MartW
MartW

Reputation: 12538

Try turning on SQL Profiler and comparing what is being processed during the two calls.

Also, in Management Studio run these command prior to testing your procedure :

CHECKPOINT
DBCC DROPCLEANBUFFERS

These commands will make sure that your SSMS testing is getting a fresh start when it runs that procedure. It is likely that, rather than your VB.NET giving an erroneously slow result, your SSMS testing is giving a false quick one due to prior executions.

CHECKPOINT
DROPCLEANBUFFERS
SO post on subject

Upvotes: 1

Related Questions