Post reply

Help - Time Out Error when Opening/Updating Table

  • July 11, 2002 at 3:10 am

    #158426

    I have a VB6 application which stores data in a SQL7 database. It has been working fine but no will not let me open a specific table to add new records in. This table (documentversions) stores file data in a 'image' type field and so is quite large.

    I can connect to the database and add to other tables. I have checked the table permissions & locks etc but all seems fine.

    I am getting the error '[Microsoft][ODBC Sql Server Driver]Timeout Expired -2147217871(80040e31)' on the .Open method

    --code snipit

    Set RST = New ADODB.Recordset

    RST.Open "documentversions", DBS, adOpenKeyset, adLockOptimistic, adCmdTable

    RST.AddNew

    .....

    RST.Update

    RST.Close

    If I change the .Open method to read

    RST.Open "documentversions", DBS, adOpenDynamic, adLockOptimistic, adCmdTable

    i get the timeut error on the .Update method

    I don't think its the code as its been working fine, but probably the SQL Server.

    Can Anyone help?

    VB6, SQL7, NT4SP6A

    Thanks in advance

    David Chaney

  • July 11, 2002 at 4:01 am

    #432423

    Increase the CommandTimeout property on your ADO connection.

    Regards,

    Andy Jones

    .

  • July 11, 2002 at 4:48 am

    #432425

    Thanks thats fixed it (sort of).

    I had tried increasing the ConnectionTimeout but hadn't tried CommandTimeout.

    I say sort of fixed because it's taking longer to insert new records to the table. The table consists or image type fields which store file data (word docs etc).

    Any ideas on speeding up performance?

    David

  • July 11, 2002 at 8:05 am

    #432471

    Do you need to have the capability to modify the whole table at one time? Could you instead update a single row or insert a single row instead? That would reduce the amount of data that has to come over.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • July 11, 2002 at 8:41 am

    #432478

    Purely insert a single row?

    Am I opening the table in the wrong way?

    I am using the AppendChunk method to read the file data into the image field.

    Thanks

    David

  • July 11, 2002 at 9:35 am

    #432490

    Thanks for the suggestion

    I have changed my application to create a record in the table using the 'Insert Into <table> (a,b,c) Values (1,2,3)' statement. And then used a select statement to retrieve the record and add the data to the image field. This has helped speed things up.

    Something along these lines -

    SET RST = New ADODB.RECORDSET

    RST.OPEN 'Insert Into <table> (a,b,c) Values (1,2,3)',cn, adOpenDynamic, adLockOptimistic

    RST.OPEN 'Select * FROM <table> WHERE (a=1 and b=2 and c=3)", cn, adOpenDynamic, adLockOptimistic

    RST("DataField").APPENDCHUNK bytedate()

    RST.UPDATE

    RST.Close

    Is this the reccommended way of adding BLOB data to tables already containing large amounts of data, or is it possible to include the DataField in the INSERT statement.

    Thanks for your help

    David

  • July 11, 2002 at 9:49 am

    #432497

    I'm checking with a developer friend who has had recent experience doing this. Typically I leave the file on the file system and use a pointer to it which is stored in SQL Server. This is how our intranet app works.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

Viewing 7 posts - 1 through 6 (of 6 total)

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