vbscript adodb connection to mssql 2008

Posted on 2011-05-11
Last Modified: 2012-05-11
We just upgraded to mssql 2008 from 2000 and have some legacy code that's blowing up.  Here's the function:

Any ideas?

' First I tried using the sql text as-is
strSql = "update tablename set value = 'value' where key=key;"
executeSql strsql
' Then I tried wrapping it in an sp
strSql = "spUpdate_table 'value','key';"
executeSql strsql
'neither works - I get timout expired no matter which I use

Function executeSql(strsql)

Dim con
dim oRsReturn
Dim cmd
Dim retval
dim adExecuteNoRecords

Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set oRsReturn = CreateObject("ADODB.recordset")
adExecuteNoRecords = "&H00000080"

con.Open strDbConnect

With cmd
.CommandType = 1
.CommandTimeout = 60
.ActiveConnection = con
.CommandText = strSQl
.execute ,,adExecuteNoRecords
End With

Set cmd = Nothing
Set con = Nothing
end function
Question by:enjama
    LVL 25

    Expert Comment

    by:Ron M
    What is the value of this ?  >> strDbConnect

    Cover the password before posting.
    LVL 39

    Expert Comment

    Did you tried to execute the SQL statement directly in SQL by using SSMS - query? Maybe the CommandTimeout = 60 is not enough if the actual

     "update tablename set value = 'value' where key=key;" is taking too long.
    What about the state of your database STATISTICS and INDEXES after the upgrade?

    LVL 51

    Expert Comment

    adCmdText = 1
    adCmdStoredProc  = 4
    LVL 6

    Expert Comment

    You could also try this peace of code

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    sqlQuery = "Your sql query here"
    sqlServer = "yur sql server here"
    sqlDb = "your database here"
    sqlUser = "your sql user here"
    sqlPw = "your password here"
    'Use this connection string for SQL login to the sql server
    strConnection = "Provider=SQLOLEDB;Data Source=" & SqlServer & ";" & "Trusted_Connection=Yes;Initial Catalog=" & sqlDb & ";" & "User ID=" & sqlUser & ";Password=" & sqlPw & ";"
    'Use this connection string for windows integrated login to the sql server
    'strConnection = "Provider=SQLOLEDB;Data Source=" & SqlServer & ";" & "Trusted_Connection=Yes;Initial Catalog=" & sqlDb & ";"
    Function fnSQLQuery(ByVal SQLquery,ByVal database)	
    	Set objConnection = CreateObject("ADODB.Connection")
    	Set objRecordSet = CreateObject("ADODB.Recordset")
    	objConnection.Open strConnection
    	objRecordSet.Open sqlQuery,objConnection, adOpenStatic, adLockOptimistic 	   
    	Set objRecordSet = Nothing
    	Set objConnection = Nothing
    End Function

    Open in new window


    Author Comment

    xuserx2000 -

    strDbConnect = "DSN=NAMEDSYSTEMDSNFORTHISDATABASE;UID=auserwhoisdbowner;PWD=thatusersspassword"

    Accepted Solution

    fyi, this is working in the following configuration - if I set a recordset equal to the results, I don't get the timeout.

    Function executeSql(strsql)

    Dim con
    Dim oRsReturn

    Set con = CreateObject("ADODB.Connection")
    Set oRsReturn = CreateObject("ADODB.recordset")

    con.Open strDbConnect

    set oRsReturn = con.execute(strSql)

    Set con = Nothing
    set oRsReturn = Nothing
    end function

    Author Comment

    lcohan -

    I was able to run the query in ssms in < 1 sec.

    Author Closing Comment

    My answer works

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now