Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: Update stmt timesout in VBScript

    I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.

    Any ideas?

    Thavalai

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Please provide more info. Mysteries do not happen by themselves, we are the ones that create them.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    $1000US on Blocking to Win
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    May 2004
    Posts
    4
    Code run from Query Analyzer:

    UPDATE MyTable
    SET date_column = '5/5/2004 9:46:28 AM'
    WHERE
    (id_column = 'something unique')

    Code run from VBScript

    Set GetDBConnection = CreateObject("ADODB.Connection")
    GetDBConnection.Open "database", "user_id", "password"

    str_sql = "UPDATE MyTable " & _
    " SET date_column = '5/5/2004 9:46:28 AM' & _
    " WHERE " & _
    " (id_column = 'something unique') "

    con.Execute str_sql

    Hope this de-mystifies it sufficiently

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but what are you doing before in your vb script...

    And why not use stored procedures instead?

    Start QA

    EXEC sp_who2

    Run your script

    EXEC sp_who2


    Looking for a blocked spid
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2004
    Posts
    4
    Looks like you may be right Brett, need to dig some more.

    Started with SPs, but moved to raw SQL in an attempt to see if that helped.

    There's a
    While Not(rs.EOF) that surrounds this Update statement where rs is a join of a bunch of tables including MyTable. Looks suspicious.

    Thanks.

  7. #7
    Join Date
    May 2004
    Posts
    4
    Brett,
    The check for $1000 is in the mail.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    dbcc opentran('your_database')

    Stored procedure is the way to go. Here I am slowly succeeding in establishing the standard that "every data access has to be done using stored procedures", not even functions, needless to say views. If there is a function, it cannot be invoked (selected from) directly from the FE source code. A stored procedure (wrapper) has to be called, because developers always have a temptation to add a WHERE clause, which may lead to problems in the future...And then you'll get a call at 3AM (that's what happened to me yesterday on the last day of my on-call) saying that "it's been working for the past 6 months!!! What happened? We didn't change anything!!!"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by thavalai
    Brett,
    The check for $1000 is in the mail.


    Margarita for everyone!

    Make sure you keep your transactions as short as possible...

    Looping and sql (like beer and wine..hey who says) don't mix...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •