February 26, 2014 at 7:26 am
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!
February 27, 2014 at 8:54 am
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?
February 27, 2014 at 9:06 am
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....
February 27, 2014 at 9:30 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2014 at 11:10 am
Sean,
That didn't work at all: got the 3709 error each time.
February 27, 2014 at 11:26 am
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?
February 27, 2014 at 11:31 am
That's not it, Auto-Close is set to false. (One of my default settings for all DBs.)
February 27, 2014 at 11:50 am
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
February 27, 2014 at 12:21 pm
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
February 27, 2014 at 12:26 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2014 at 12:34 pm
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.
February 27, 2014 at 12:58 pm
Oops on my part, this is the code I've been 'working' on am I looking at the wrong thing?
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
What should I be looking at ?
February 27, 2014 at 1:40 pm
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 batesviewconStr="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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2014 at 7:14 am
9:15 and all's well with the explicit connection object... 🙂
We'll see what happens next week.
March 8, 2014 at 11:28 am
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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply