Timeout expired error

[shaminda]

I get timeout errors in the following stored procedure:

CREATE PROCEDURE spGetRackCounter
@fdRackType as int,
@fdUserLoadId as int,
@fdReturn1 int output
AS
IF @fdRackType = 1
--rack is a regular rack
Begin
SELECT Count(fdAssemblyBarcode) as Return1
FROM tbStation5
WHERE fdUserLoadId = @fdUserLoadId
IF @fdReturn1 is null
Select @fdReturn1 = 0
End
IF @fdRackType = 2
-- Rack is a safety stock
Begin
SELECT Count(fdAssemblyBarcode) as Return1
FROM tbSafetyStock
WHERE fdUserLoadId = @fdUserLoadId
IF @fdReturn1 is null
Select @fdReturn1 = 0
End
GO


Here is part of the error message I get:

Date = 5/5/2008 10:26:19 AM
Number = 5
Source = .Net SqlClient Data Provider
Procedure = GetRackCounter
Line Number = 2929
Help Link =
Description = Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is my connection string:

ConnectString=Persist Security Info=false;User ID=SQLUser;Password="SQLPass";Initial Catalog=LotCtrlTE0;Data Source=SQL-v;max pool size=7500

How can I fix this problem? I get the timeout error at least twice a day. And the program runs 24/7 on a production environment.

[dglienna]

Set the ConnectionTimeout = 0 I think.

Check here:

http://connectionstrings.com/

[shaminda]

I changed the timeout to 0 and now the program hangs. It goes to a not responding mode. Now I have a new problem, what should I do? Any help is appreciated.

[GremlinSA]

I get timeout errors in the following stored procedure:

CREATE PROCEDURE spGetRackCounter
@fdRackType as int,
@fdUserLoadId as int,
@fdReturn1 int output
AS
IF @fdRackType = 1
--rack is a regular rack
	Begin
		SELECT Count(fdAssemblyBarcode) as Return1
		FROM tbStation5
		WHERE fdUserLoadId = @fdUserLoadId
		IF @fdReturn1 is null
		Set @fdReturn1 = 0
	End
IF @fdRackType = 2
-- Rack is a safety stock 
	Begin
		SELECT Count(fdAssemblyBarcode) as Return1
		FROM tbSafetyStock
		WHERE fdUserLoadId = @fdUserLoadId
		IF @fdReturn1 is null
		Set @fdReturn1 = 0
	End
GO

Try these changes... I'm suspecting that Select @fdReturn1 = 0 is causing the hang.
i'm no SQL Pro.. but AFAIK Select is used to get data from a table..

Gremmy...

[Shuja Ali]

@Gremmy: Select @fdReturn1 = 0 is another way of storing a value into a variable. This is not the cause of the problem for sure.

Select is not only used to retrieve data from the database, it can be used for number of other things too. Like doing some math Select ((1+9+6)/12)*10 Or executing a function, etc.

What does this stored procedure do? Shouldn't it look like this

CREATE PROCEDURE spGetRackCounter
    @fdRackType as int,
    @fdUserLoadId as int,
    @fdReturn1 int output
AS
    IF @fdRackType = 1
        --rack is a regular rack
        Begin
        SELECT @fdReturn1 = Count(fdAssemblyBarcode)
            FROM tbStation5
            WHERE fdUserLoadId = @fdUserLoadId
        IF @fdReturn1 is null
            Select @fdReturn1 = 0
        End
    IF @fdRackType = 2
        -- Rack is a safety stock
        Begin
        SELECT @fdReturn1 = Count(fdAssemblyBarcode)
            FROM tbSafetyStock
            WHERE fdUserLoadId = @fdUserLoadId
        IF @fdReturn1 is null
            Select @fdReturn1 = 0
        End
GO 

How many records do you have in tbStation5 and tbSafetyStock?

[GremlinSA]

@Gremmy: Select @fdReturn1 = 0 is another way of storing a value into a variable. This is not the cause of the problem for sure.

Select is not only used to retrieve data from the database, it can be used for number of other things too. Like doing some math Select ((1+9+6)/12)*10 Or executing a function, etc.

Ahh ok.. thanks.. I'm new to Sql Stored Procs and was shown to used Set when setting var's..

In My mind what i figure is happening here is that one or other of the datasets been returned is not returning anything and the SP is waiting for a returned dataset. And i figured it might have been the Select @fdReturn1 = 0 ...

Gremmy..

[ppompeii]

Your missing the BEGIN keyword in the following:

IF @fdReturn1 is null
    Select @fdReturn1 = 0
End

Should Be:

IF @fdReturn1 is null
Begin
    Select @fdReturn1 = 0
End

[Shuja Ali]

@ppompeii: If you look carefully at the code you will see that the End keyword that you are talking about has a corresponding BEGIN too. There is no need to add Begin, it will just give an error while creating the stored procedure.

[ppompeii]

@Shuja: You're right, my bad.

[ppompeii]

@Shaminda: Can you post the code for your GetRackCounter method.

[shaminda]

Private Function GetRackCounter() As Integer
        Dim Connection As SqlConnection = Nothing
        Dim iReturn As Integer
        Dim sTemp As String
        Dim iRackType As Integer
        Dim strRanNumber As String
        Dim iResult As Integer

        Dim arParms() As SqlParameter = New SqlParameter(2) {}
        Try


            Connection = cBaseForm.GetConnection(sConnectStringSQL2)

            oError = New cError(True, True, False, True, True, True, PATH_LOG_FILE, "LotCtrlTA0")

            arParms(0) = New SqlParameter("@fdRackType", SqlDbType.Int, 4)
            arParms(0).Value = 1    'we don't run safety stock wheels anymore so this is always 1

            arParms(1) = New SqlParameter("@fdUserLoadId", SqlDbType.Int, 4)
            arParms(1).Value = cBaseForm.g_iuserLoadId

            arParms(2) = New SqlParameter("@fdReturn1", SqlDbType.Int, iReturn)
            arParms(2).Direction = ParameterDirection.Output

            iReturn = SqlHelper.ExecuteScalar(Connection, CommandType.StoredProcedure, "spGetRackCounter", arParms)

            GetRackCounter = iReturn
        Catch ex As Exception
            Call oError.CheckError("GetRackCounter", ex)
        Finally
            If Not Connection Is Nothing Then
                CType(Connection, IDisposable).Dispose()
            End If

        End Try
    End Function

[shaminda]

As Shuja suggested I tried to change my stored procedure, but it was returning 0. I realized I have to change my code to change the stored procedure, which I have not done yet.

tbStation5 has 266,759 records right now. But since I specify say fdUserLoad = 4002 it should not matter should it? Because I don’t select more than 60 records at a time.