by Hi,
a customer of mine has a SQL server 6.5 and an intranet webserver (both on
the same physical machine). They are placed behind a firewall (Firewall One
I think). On the outside of the firewall is the extranet webserver. These
two systems has worked flawlessly over 1 year.
Now the customer wanted to upgrade to 7.0 and so I did. the upgrade went
just fine and the performace was stunning. But, after the upgrade, the
extranet webserver on the outside reports a silly error 3704 that it didn't
report before the upgrade.
What I do to get this error? I use an ODBC-connection, with an "on error":
-----------------
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 5
on error resume next
objConn.Open "ODBCname", "username", ""
-----------------------
Next thing is to open the resultset, which I do using an Execute on the
Command:
-----------------------
SQL = "SELECT bruker_id,passord,brukertype,gruppe FROM Bruker WHERE
bruker_id = " & "'" & UserID & "'"
Set rs = objConn.Execute(SQL)
---------------
and then I have the Err-part:
---------------
If Err.Number > 0 Then
'
'
'
End If
----------------
Use of this results in Error 3704 'The operation requested by the
application is not allowed if the object is closed'.
According to the knowledgebase at microsoft.com, it's when using #TEMP
stored procedures that this occurs. First of all, I'm not using a stored
procedure, but an ordinary SELECT. Ok, so maybe SQL Server does a temporary
stored procedure out of it. But how come it has all worked before!? Well,
once again, according to the knowledgebase, this is a new "feature" of the
ADO2.1 (SP1, SP2). I was running ADO 1.5 before so I didn't have this
"feature" (which is a problem for me, not a feature.
Then the knowledgebase suggests I should use
----------
rs.Open "SET NOCOUNT ON"
----------
before I actually open the resultset. This is a must to solve the "feature"
they say. Ok, so I remove the line
-----------
Set rs = objConn.Execute(SQL)
-----------
from my code and replace it with
-----------
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = objConn
rs.Open "SET NOCOUNT ON"
rs.Open SQL
------------
Now, this doesn't work either! Instead of a 3704-error, I suddenly get a
3709 "The application requested an operation on an object with a reference
to a closed or invalid Connection object."
What's wrong with the ADO?! Or is it the firewall of mine that suddenly has
bailed out!? It doesn't seem so, because I then removed the old DSN-name
from the extranet webserver and made a new one with the same name. It can
communicate with the database on the inside of the firewall.
NOW comes the weird part! The ASP-pages works all fine if I'm sitting at the
extranet webserver executing them! But if I'm on the inside of the firewall,
trying to reach the same server (using the full URL) I get the 3704 or 3709
errors. Ok I thought, this is a firewall error. So I went home and tried it
from home. Nope, I get the same errors from home.
So how come it all works when sitting physically at the extranet webserver,
but not when accessing the ASP-pages from another machine, on the extranet
webserver?!
This is really annoying problem I have and I'd appreciate any inputs,
comments etc. that can help me out!
BEst regards
PS. I'm using Named Pipes, not TCP/IP port 1433.