asked on

vbscript adodb connection to mssql 2008

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

Ron Malmstead

What is the value of this ? >> strDbConnect

Cover the password before posting.

lcohan

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?

HainKurt

adCmdText = 1
adCmdStoredProc = 4

dax_bad

You could also try this peace of code

Cheers
Daniel

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 	   
	objRecordSet.Close
	objConnection.Close
	Set objRecordSet = Nothing
	Set objConnection = Nothing
End Function

Open in new window

enjama

ASKER

xuserx2000 -

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

ASKER CERTIFIED SOLUTION

enjama

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enjama

ASKER

lcohan -

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

enjama

ASKER

My answer works