stored proc works in Query Profiler, times out in VB

  • Thread starter Thread starter Brian Link
  • Start date Start date
B

Brian Link

I have a stored procedure that is hanging when executed from VB.NET.
If I "exec spName parms" from query profiler, it zips right along.

When calling the DataAdapter.Fill method, it will hang, and errors out
with the error "Timeout has expired".

The stored proc is as follows:

ALTER PROCEDURE dbo.spFindConflict
(
@EntityID int,
@RoleID int,
@Start smalldatetime,
@End smalldatetime
)
AS
SELECT
A_Appointment.Appointment_ID,
AX_Appointment_Entity.Entity_ID
FROM
A_Appointment INNER JOIN
AX_Appointment_Entity
ON
A_Appointment.Appointment_ID =
AX_Appointment_Entity.Appointment_ID
WHERE
AX_Appointment_Entity.Entity_ID = @EntityID AND
Attendee_Role_ID = @RoleID AND
(A_Appointment.End_DateTime BETWEEN @Start AND @End) OR
(A_Appointment.Start_DateTime BETWEEN @Start AND @End)

The code executing it is as follows:

Public Function Find_Collision(ByVal vEntID As Int32, _
ByVal vRoleID As Int32, ByVal vStart As Date, ByVal vEnd As Date) _
As DataView

Dim cn As New SqlConnection(ConnString)
cn.Open()
Try
Dim cm As New SqlCommand("spFindConflict", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add("@EntityID", SqlDbType.Int).Value = vEntID
cm.Parameters.Add("@RoleID", SqlDbType.Int).Value = vRoleID
cm.Parameters.Add("@Start", SqlDbType.SmallDateTime).Value = vStart
cm.Parameters.Add("@End", SqlDbType.SmallDateTime).Value = vEnd
Dim da As New SqlDataAdapter(cm)
Dim dt As New DataTable("conflicts")
Dim dv As New DataView(dt)
da.Fill(dt)

Return dv

Catch ex As Exception
msgbox "weep"
End Try

End Function


Any ideas?

Thanks

Brian Link, Minnesota Countertenor
- - - - - - - - - - - - - - - - -
"Mother, people only invite you to dinner for
three reasons: to sell you vitamins, to drug you
and take unpleasant snapshots, or to convert you
to their hideous farming religion."
 
I have a stored procedure that is hanging when executed from VB.NET.
Click to expand...

Never mind. The problem was that when I called the function, I was
creating a new sqlconnection, when I was in the middle of transaction
with another one. I just re-used the original sqlconnection for this
function and it works just fine.

BLink
 
Hi Brian,

Nice to hear that you have had the problem resolved. Thanks for sharing
your experience with all the people here. If you have any questions, please
feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top