• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

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
0
enjama
Asked:
enjama
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
What is the value of this ?  >> strDbConnect

Cover the password before posting.
0
 
lcohanDatabase AnalystCommented:
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?

0
 
HainKurtSr. System AnalystCommented:
adCmdText = 1
adCmdStoredProc  = 4
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
dax_badCommented:
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

0
 
enjamaAuthor Commented:
xuserx2000 -

strDbConnect = "DSN=NAMEDSYSTEMDSNFORTHISDATABASE;UID=auserwhoisdbowner;PWD=thatusersspassword"
0
 
enjamaAuthor Commented:
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
0
 
enjamaAuthor Commented:
lcohan -

I was able to run the query in ssms in < 1 sec.
0
 
enjamaAuthor Commented:
My answer works
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now