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 Wednesday, February 26, 2014 7:26 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 439, Visits: 1,015
Hi all,

Here's the VBS code I'm running:

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

I'm getting the following error sporadically:

3709 Srce: ADODB.Command Desc: The connection cannot be used to perform this operation. It is either closed or invalid in this context.

The code above is run ~45 times a day, and we'll go days without getting any error.


I've looked long and hard on the internet for an explanation or at least some insight into the cause, but so far nothing makes sense.

The "objLogFile.WriteLine "---EXECUTE dbo.AddRecord " & strInput" shows that there is nothing wrong with the strInput being passed to the server. I can copy it out of the log file and run it on the server

Can anyone here shine a light on this?

Thanks!




Post #1545385
Posted Thursday, February 27, 2014 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:03 AM
Points: 30, Visits: 404
Not enough information to help. What does your INSERT string look like? Is there just a single TABLE or is some sort of JOIN to another TABLE involved?
Post #1545936
Posted Thursday, February 27, 2014 9:06 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 439, Visits: 1,015
The VBS is calling a stored procedure... There's some logic in there, and finally an insert, and yes, there is a join to another table.

But as I mentioned, it's working 99% of the time, and the error capture section generates calls to the stored proc that I am able to execute successfully by hand.

The error text suggests to me that the call is not actually getting to the SQL Server -- but then if I had a clue what the error was telling me....




Post #1545952
Posted Thursday, February 27, 2014 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
Been quite some time since I worked with this but you don't have an explicit connection. You should declare your connection as an object so you manage it.

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

On Error Resume Next

dim oConn
SET oConn = Server.CreateObject("ADODB.Connection")
oConn.open connString

With cmdObj
.activeconnection=oConn
.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



_______________________________________________________________

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 #1545967
Posted Thursday, February 27, 2014 11:10 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 439, Visits: 1,015
Sean,

That didn't work at all: got the 3709 error each time.



Post #1546034
Posted Thursday, February 27, 2014 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:03 AM
Points: 30, Visits: 404
http://rory.streetfamily.info/PermaLink,guid,11745f49-4608-408b-b112-bef88e857b6f.aspx

Could you be having the same issue as Rory, the connection is being auto closed on you?
Post #1546041
Posted Thursday, February 27, 2014 11:31 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 439, Visits: 1,015
That's not it, Auto-Close is set to false. (One of my default settings for all DBs.)




Post #1546046
Posted Thursday, February 27, 2014 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:03 AM
Points: 30, Visits: 404
See if this helps:
 
If cmdObj.State = 1 Then
With cmdObj
.blah ...
End With
Else
'the connection is not open, try opening a new connection
'also fire off your error logging routine here noting the lost connection
End If

Post #1546055
Posted Thursday, February 27, 2014 12:21 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 439, Visits: 1,015
Sean,

Actually I got your connection object example to work, needed to remove the
Server. in SET oConn = Server.CreateObject("ADODB.Connection")

So, I'll let it run for a couple of days, and see what happens.

Thanks,

Paul



Post #1546066
Posted Thursday, February 27, 2014 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
schleep (2/27/2014)
Sean,

Actually I got your connection object example to work, needed to remove the
Server. in SET oConn = Server.CreateObject("ADODB.Connection")

So, I'll let it run for a couple of days, and see what happens.

Thanks,

Paul


Ahh yes. That was taken from some classic asp that is still hanging around here. Unless you were doing this in classic asp the Server object would be invalid.

The other thing you might want/need to add is to check the status of the connection. Let us know how it goes.


_______________________________________________________________

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 #1546069
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse