[Microsoft][ODBC SQL Server Driver]Timeout expired error

[Niavlys]

Hi I need to update a SQL table which contains user codes. I need to put a '1' it the user is found in Active Directory and a '0' if not. I have this code and it works for a few but after about 10, I receive this "[Microsoft][ODBC SQL Server Driver]Timeout expired" error code. This is my main loop :

objDomain.Filter = Array("User")
    For Each objUser In objDomain
        set objUserInfo = GetObject("LDAP://"&objUser.Name &",DC=RSSBC,DC=COM")
        
               wscript.Echo objUserInfo.sAMAccountName
               strSQL = "UPDATE E_ActiveDirectory SET ad=1 WHERE Fiche=" &objUserInfo.sAMAccountName & ";"
               Set objRS = objConn.Execute(strSQL)
               set objRS = Nothing

        
    Next

I'm using a DSN less connection to my database.

Can someone help me please!

Thanks.

 

[eramgarden]

Can u run the same update from QA and see if it times out?
If it times out in QA as well, then maybe your table needs to have its indexes rebuilt...

Also, have u set debug step to make sure you have the right values and stuff?

 

[Hablaras]

Set objRS.CommandTimeout to a higher value than the default 30 seconds, e.g. 1000

objRS.CommandTimeout = 1000

It looks like it is not a connection (i.e. slow reverse lookup of the server name) problem. BUT if the previous statement doesn't fix your problem, try the next one:

objRS.ConnectionTimeout = 1000

Does this help?

 

[Niavlys]

I tried a higher timeout value but I'm still having the same error message, the only difference is that it takes longer before before I get it.

I was wondering if running the objConn.Execute command in a long loop like this should fill a buffer or something like this. It works really fast for the first 10 records and then it jams.

eramgarden : I don't think (or don't know) I can run this query in Query Analyser cause it runs from a vbscript and uses a variable

UPDATE E_ActiveDirectory SET ad=1 WHERE Fiche=" &objUserInfo.sAMAccountName & ";"

Thanks.

 

[SimonSellick]

May be a silly question, but are you sure that it is the update statement that is timing out? Try commenting out the connection.Execute and replacing it with a MsgBox showing the update SQL to make sure that it looks as you expect. That will also make sure that the loop continues correctly after 10 records. Are you expecting the update statement to update one row at a time, or a variable number? Is the column used in the WHERE clause numeric? You have no single-quotes around the VB value passed. If all else fails, you can run the update in Query Analyser by breakpointing on the .Execute, printing the update SQL in the immediate window, then copying it from there and pasting it into the query window.

Hope this helps,
Simon

 

[Niavlys]

Thank you all for your answers, when debugging the script, I figured out that the timeout was caused by some records only, so I moved those records somewhere else so the script continue.

That's weird cause I compared the problematic user with another one and didn't find a difference...

Thanks again!