Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

VBS - ADODB 3709 error Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 12:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
Well if I'm understanding this -- and to be honest, I'm on very thin ice -- I can't check the state of the connection in my original code as suggested by batesview


conStr="Provider=sqloledb;Data Source=<SQLServer2008>;Initial Catalog=<mydb>;Integrated Security=SSPI"
Set cmdObj=createobject("adodb.command")

On Error Resume Next
' if cmdObj.State = 1 then
With cmdObj
.activeconnection=conStr
--blah
End with

because I haven't actually opened a connection -- only instantiated a command object.

Are you suggesting that, from your example, I could check the state of the oConn object (not the command object), since that one is established before the command is executed?

I'll follow up on that if I see more 3709's.

Thanks folks.



Post #1546075
Posted Thursday, February 27, 2014 12:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 30, Visits: 387
Oops on my part, this is the code I've been 'working' on am I looking at the wrong thing?

[code ="vb"]
strInput = <a string that is the parameter for my stored proc dbo.AddRecord>

conStr="Provider=sqloledb;Data Source=<SQLServer2008>;Initial Catalog=<mydb>;Integrated Security=SSPI"
Set cmdObj=createobject("adodb.command")

On Error Resume Next

With cmdObj
.activeconnection=conStr
.commandtimeout = 0
.commandtype=4
.commandtext="dbo.AddRecord"
.parameters.refresh
.parameters(1).value=strInput
.execute
End with

If Err.Number <> 0 Then
objLogFile.WriteLine "---" & Err.Number & " Srce: " & Err.Source & " Desc: " & Err.Description
objLogFile.WriteLine "---EXECUTE dbo.AddRecord " & strInput
Err.Clear
else
cmdObj.close
end if
[/code]
What should I be looking at ?
Post #1546092
Posted Thursday, February 27, 2014 1:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
schleep (2/27/2014)
Well if I'm understanding this -- and to be honest, I'm on very thin ice -- I can't check the state of the connection in my original code as suggested by batesview


conStr="Provider=sqloledb;Data Source=<SQLServer2008>;Initial Catalog=<mydb>;Integrated Security=SSPI"
Set cmdObj=createobject("adodb.command")

On Error Resume Next
' if cmdObj.State = 1 then
With cmdObj
.activeconnection=conStr
--blah
End with

because I haven't actually opened a connection -- only instantiated a command object.

Are you suggesting that, from your example, I could check the state of the oConn object (not the command object), since that one is established before the command is executed?

I'll follow up on that if I see more 3709's.

Thanks folks.


Yes exactly. Using my code you would have an explicit connection which would let you check of the connection object.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1546111
Posted Friday, February 28, 2014 7:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
9:15 and all's well with the explicit connection object...

We'll see what happens next week.



Post #1546330
Posted Saturday, March 8, 2014 11:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
No joy, still getting between 0 and 2 3709's / day, out of 30-40 executions.

I've now added a loop to check the state of the connection object, it cycles 10 times looking for an open state, otherwise sets object to nothing and creates a new one.

We'll see what happens next week.




Post #1548982
Posted Wednesday, March 12, 2014 7:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 5:21 AM
Points: 428, Visits: 928
Some improvement, but still got one 3709.

I'd *love* to understand what's causing the error.



Post #1550227
Posted Thursday, March 13, 2014 5:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 48, Visits: 191
My 2 penn'orth; I think the error you're getting is from an attempt to execute the stored proc with a connection that isn't open so you're not seeing the message associated with failing to open the connection in the first place. You need to replace "On Error Resume Next" with "On Error GoTo <some local error handler>. In the error handler, you can check the actual connection failure message which may give you a clue as to what's going on. You could still allow a certain number of failure iterations before giving up entirely.
Post #1550627
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse