Timeout Expired when Updating SQL Server 2000 Record.

Question

Post reply

Timeout Expired when Updating SQL Server 2000 Record.

Kevin Barrie

SSChasing Mays

Points: 659
More actions
January 11, 2005 at 8:39 am

#102344

I am trying to update a number of records in a table in SQL Server 2000. I am doing this via VB6 using ADO. But when the program is running, I get a Timeout Expired error. It appears after approx. 70 records have been updated.
the VB Code is
[Code]
    Dim strUpdSQL As String
    Dim cmd As ADODB.Command

    If madCN Is Nothing Then
        g_strMsg = "There is no database connection."
        g_strTitle = "Connection Error"
        g_MsgCrit g_strMsg, g_strTitle
    Else
        strUpdSQL = "{?=call sp_uLabelAppsUpdateContainerInfo ( ?, ?, ? )}"
        Set cmd = CreateObject("ADODB.Command")
        With cmd
            .ActiveConnection = madCN
            .CommandType = adCmdText
            .CommandText = strUpdSQL
            .Parameters.Refresh
            .Parameters(0).Direction = adParamReturnValue
            .Parameters(1).Direction = adParamInput
            .Parameters(1).Value = CStr(intContnrID)
            .Parameters(2).Direction = adParamInput
            .Parameters(2).Value = CStr(dblWeight)
            .Parameters(3).Direction = adParamInput
            .Parameters(3).Value = CStr(lngRecID)
            .Execute
        End With
    End If
[/code]

The Stored Procedure is
CREATE PROCEDURE sp_uLabelAppsUpdateContainerInfo
@ContainerID nvarchar(5),
@WeightOfContainer nvarchar(20),
@RecordID nvarchar(15)
AS
DECLARE @UpdSQL nvarchar(1000)
BEGIN TRAN UpdLabelApp
SET @UpdSQL = 'UPDATE tblLabelApplications SET '
SET @UpdSQL = @UpdSQL + 'ContainerID = ' + @ContainerID + ', '
SET @UpdSQL = @UpdSQL + 'WeightOfContainer = '   + @WeightOfContainer + ' '
SET @UpdSQL = @UpdSQL + 'WHERE RecordID = '   + @RecordID

EXEC (@UpdSQL)
COMMIT TRAN UpdLabelApp
GO

Can anyone help as I am stuck!

Kev.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Peter Grimberg Valued Member Points: 58 More actions · Answer 1

hi kevin,

at first try to switch of any compression on the file system.

compression on data or log-files often causes timeouts.

that may help.

cheers, peter

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 2

Just checked, the compression option is switched off.

Peter Grimberg Valued Member Points: 58 More actions · Answer 3

ok.

with query analyzer, run sp_configure and check the general option-values. check the wait- and timeout-options.

peter

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 4

Ok, done that

I get

query wait (s) -1 2147483647 -1 -1

remote login timeout (s) 0 2147483647 20 20

remote query timeout (s) 0 2147483647 600 600

Peter Grimberg Valued Member Points: 58 More actions · Answer 5

ok, your timeouts are not caused by compression and not caused by option-parameters.

my timeouts in the past always resulted in unavailable ressources on the machine running sql-server.

hmmmm... i'm running out of answers.

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 6

Kevin Barrie

SSChasing Mays

Points: 659

January 12, 2005 at 2:17 am

#536965

Thanks for your help.

Kev.

Jeet SSCommitted Points: 1536 More actions · Answer 7

Have you tried setting the command timeout property on the command object (CommandTimeout) to zero. i.e.

oCmd.CommandTimeout = 0

This tells ADO to wait for the command to complete. You may still get a timeout if sql server decides to terminate the command. We found this helped and we did not have the problem any more.

Thanks Jeet

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 8

Tried that.

All that achieves is that the program doesn't do anything...it just hangs.

Jeet SSCommitted Points: 1536 More actions · Answer 9

Are you sure the program is hanging??, and it's not that the query is being blocked??

Thanks Jeet

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 10

I'm a bit if a Newbie when it comes to SQL Server. How can you tell if the query is being blocked?

Kev.

Jeet SSCommitted Points: 1536 More actions · Answer 11

there is a system sproc called 'sp_who2 active' and in the row for your process if the column blk has a value it tells you the processid of the blocking process.

so if you run this at the point when you think your app has hung it can tell you if it is being blocked or just taking a long time.

Thanks Jeet

Kevin Barrie SSChasing Mays Points: 659 More actions · Answer 12

Thanks for your help.

I got it to work, I went trough my VB Code and took out any code that I really didn't need ( that's what you get when you cut and paste!). Cut down my record set to the bare minimum and now it works. Maybe it was running out of memory or something....who knows, but at least it works.

Thanks again.

Kev.