Included function not always found.

[TomB]

I have a function on an "included" page.

For reasons unknown to me I frequently will get an error - to the effect
that the function can't be found.

If I hit refresh/F5 it loads the page just fine.

It doesn't happen often .. maybe once every 50 pages. It also doesn't
happen on any specific page.

I'd show code, but it's not really related to either the function or any
specific page.

Is there any sort of a delay between Asp loading the "includes" and
processing the rest of the page? I'm wondering if the function is getting
called before the include is pulled in.

Thanks
TomB

[Roland Hall]

"TomB" wrote in message news:e3*************@TK2MSFTNGP11.phx.gbl...
: I have a function on an "included" page.
:
: For reasons unknown to me I frequently will get an error - to the effect
: that the function can't be found.
:
: If I hit refresh/F5 it loads the page just fine.
:
: It doesn't happen often .. maybe once every 50 pages. It also doesn't
: happen on any specific page.
:
: I'd show code, but it's not really related to either the function or any
: specific page.
:
: Is there any sort of a delay between Asp loading the "includes" and
: processing the rest of the page? I'm wondering if the function is getting
: called before the include is pulled in.

Sounds like. Are you trying to call for the function before the page is
fully loaded?

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[Dave Anderson]

Roland Hall wrote:

...I'm wondering if the function is getting called before the
include is pulled in.

Sounds like. Are you trying to call for the function before the page
is fully loaded?

If this is truly an ASP issue (rather than client-side), this should not
happen. The #include directive is processed before anything else happens,
and the whole script is parsed, establishing the namespace. Functions get
special treatment -- they can be called from anywhere, even from blocks that
execute first.

I am watching this thread with great interest, as it suggests a real IIS
bug.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Aaron Bertrand - MVP]

> Sounds like. Are you trying to call for the function before the page is

fully loaded?

This would only apply if it's a <script src> on the client.

In ASP, the page doesn't even START "loading" until the #includes are
resolved.

[Aaron Bertrand - MVP]

> I have a function on an "included" page.

Can you explain what you mean by "included"? Do you mean #include, or
something else? Why the quotes?

For reasons unknown to me I frequently will get an error - to the effect
that the function can't be found.
Can you show the exact error, as opposed to "to the effect..."?
I'm wondering if the function is getting
called before the include is pulled in.

As I explained in my other reply, this isn't possible. The rest of the ASP
doesn't exist until the #include file contents are injected into the
processing stream.

A

[Roland Hall]

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:Om***************@tk2msftngp13.phx.gbl...
: > Sounds like. Are you trying to call for the function before the page is
: > fully loaded?
:
: This would only apply if it's a <script src> on the client.
:
: In ASP, the page doesn't even START "loading" until the #includes are
: resolved.

Thanks for including that (no pun intended). (O:= I knew I read somewhere,
but couldn't locate it, that if the include has client script, it still may
not be resolved even though the onload event fired. Perhaps someone can
find that discussion.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[Aaron Bertrand [MVP]]

> Thanks for including that (no pun intended). (O:= I knew I read

somewhere,
but couldn't locate it, that if the include has client script, it still
may
not be resolved even though the onload event fired.

It would be the client-side script that may not be resolved, not an ASP
function you call from other segments of ASP code.

[Roland Hall]

"Aaron Bertrand [MVP]" wrote in message
news:em**************@TK2MSFTNGP11.phx.gbl...
: > Thanks for including that (no pun intended). (O:= I knew I read
: > somewhere,
: > but couldn't locate it, that if the include has client script, it still
: > may
: > not be resolved even though the onload event fired.
:
: It would be the client-side script that may not be resolved, not an ASP
: function you call from other segments of ASP code.

Would that be true even if the ASP included code was client script?

Ex. I have files that are nothing more than client-side javascript code but
need to be wrapped in ASP because I'm mixing values from the server-side,
linking that to a common ASP file that is included in numerous files.

document.shoppingform.action='<%=cartPath%>checkou t.asp';

Which is linked with:
<script type="text/javascript" src="somejsfile.asp"></script>

....into a file that is included in all others.

This is then included into other files with:
<!--#include virtual="/somepath/somefile.asp"-->

Granted I'm not experiencing the issue the OP is but is this a possibility
for that issue?

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[Aaron Bertrand [MVP]]

> Granted I'm not experiencing the issue the OP is but is this a possibility

for that issue?

I really don't think so. ASP is executed this way:

#includes are resolved
ASP is processed
Client-side script can proceed

I don't think there are any scenarios where any of these steps can attempt
to cross paths...

[Roland Hall]

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:Or*************@TK2MSFTNGP10.phx.gbl...
: > Granted I'm not experiencing the issue the OP is but is this a
possibility
: > for that issue?
:
: I really don't think so. ASP is executed this way:
:
: #includes are resolved
: ASP is processed
: Client-side script can proceed
:
: I don't think there are any scenarios where any of these steps can attempt
: to cross paths...

Ok. Thank you.

[TomB]

On all pages I have
<!--#Include Virtual="PathtoFile\File.asp"-->

With in File.asp

Function MyFunction()
'MyCode
End Function
Back in all pages I'll call MyFunction and most times it works but
occassionally it'll say MyFunction undefined.

Thanks
Tom B
P.S. I used the quotes thinking it would be clearer. I guess not.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

I have a function on an "included" page.
Can you explain what you mean by "included"? Do you mean #include, or
something else? Why the quotes?

For reasons unknown to me I frequently will get an error - to the effect
that the function can't be found.

Can you show the exact error, as opposed to "to the effect..."?

I'm wondering if the function is getting
called before the include is pulled in.

As I explained in my other reply, this isn't possible. The rest of the

ASP doesn't exist until the #include file contents are injected into the
processing stream.

A

[TomB]

It happened again.....
Here's the actual errror

Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'ExecuteSQL'

ExecuteSQL is the function on my include page.

It's driving me crazy, because it happens so irregularly.

TomB

"TomB" <sh*****@hotmailXXX.com> wrote in message
news:uC**************@TK2MSFTNGP09.phx.gbl...

On all pages I have
<!--#Include Virtual="PathtoFile\File.asp"-->

With in File.asp

Function MyFunction()
'MyCode
End Function
Back in all pages I'll call MyFunction and most times it works but
occassionally it'll say MyFunction undefined.

Thanks
Tom B
P.S. I used the quotes thinking it would be clearer. I guess not.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

I have a function on an "included" page.

Can you explain what you mean by "included"? Do you mean #include, or
something else? Why the quotes?

For reasons unknown to me I frequently will get an error - to the effect that the function can't be found.

Can you show the exact error, as opposed to "to the effect..."?

I'm wondering if the function is getting
called before the include is pulled in.

As I explained in my other reply, this isn't possible. The rest of the

ASP

doesn't exist until the #include file contents are injected into the
processing stream.

A

[Aaron Bertrand - MVP]

Can you show the line that causes this error? Sounds like you are trying to
use ExecuteSQL in a different context...

One thing you might try doing is turning off option explicit. Is it
possible that you are doing conditional includes or something, and one file
has option explicit but the rest don't?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e4**************@TK2MSFTNGP12.phx.gbl...

It happened again.....
Here's the actual errror

Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'ExecuteSQL'

ExecuteSQL is the function on my include page.

It's driving me crazy, because it happens so irregularly.

TomB

"TomB" <sh*****@hotmailXXX.com> wrote in message
news:uC**************@TK2MSFTNGP09.phx.gbl...

On all pages I have
<!--#Include Virtual="PathtoFile\File.asp"-->

With in File.asp

Function MyFunction()
'MyCode
End Function
Back in all pages I'll call MyFunction and most times it works but
occassionally it'll say MyFunction undefined.

Thanks
Tom B
P.S. I used the quotes thinking it would be clearer. I guess not.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...

> I have a function on an "included" page.

Can you explain what you mean by "included"? Do you mean #include, or
something else? Why the quotes?

> For reasons unknown to me I frequently will get an error - to the effect > that the function can't be found.

Can you show the exact error, as opposed to "to the effect..."?

> I'm wondering if the function is getting
> called before the include is pulled in.

As I explained in my other reply, this isn't possible. The rest of

the ASP

doesn't exist until the #include file contents are injected into the
processing stream.

A

[TomB]

Honestly, I'm not doing conditional includes or anything weird.
If I hit F5 the page refreshes just fine.

I'll try turning off option explicit, but I would expect I'd just get a
different error.

The error says the variable isn't defined, but it's not a variable, it's a
sub.

Here's the contents of my include....
<include file....>
Function ExecuteSQL(sSQL)
Dim CN
Dim RS
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open Session("g_STRCONN")
Set RS=Server.CreateObject("ADODB.Recordset")
RS.CursorLocation=adUseClient
'Response.Write sSQL & ""
'Response.Flush
RS.Open sSQL,CN
set RS.ActiveConnection=nothing
SET ExecuteSQL=RS
Set RS=nothing
CN.Close
Set CN=nothing
End Function
</include file>

In my page, there's just a call like....

Set RS=ExecuteSQL "SELECT FieldName from TableName"

As I mentioned originally, this doesn't happen on any specific page...but on
many.

And it's very infrequent.

Thanks
Tom B

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Can you show the line that causes this error? Sounds like you are trying to use ExecuteSQL in a different context...

One thing you might try doing is turning off option explicit. Is it
possible that you are doing conditional includes or something, and one file has option explicit but the rest don't?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e4**************@TK2MSFTNGP12.phx.gbl...

It happened again.....
Here's the actual errror

Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'ExecuteSQL'

ExecuteSQL is the function on my include page.

It's driving me crazy, because it happens so irregularly.

TomB

"TomB" <sh*****@hotmailXXX.com> wrote in message
news:uC**************@TK2MSFTNGP09.phx.gbl...

On all pages I have
<!--#Include Virtual="PathtoFile\File.asp"-->

With in File.asp

Function MyFunction()
'MyCode
End Function
Back in all pages I'll call MyFunction and most times it works but
occassionally it'll say MyFunction undefined.

Thanks
Tom B
P.S. I used the quotes thinking it would be clearer. I guess not.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...
> > I have a function on an "included" page.
>
> Can you explain what you mean by "included"? Do you mean #include, or > something else? Why the quotes?
>
> > For reasons unknown to me I frequently will get an error - to the

effect

> > that the function can't be found.
>
> Can you show the exact error, as opposed to "to the effect..."?
>
> > I'm wondering if the function is getting
> > called before the include is pulled in.
>
> As I explained in my other reply, this isn't possible. The rest of the ASP
> doesn't exist until the #include file contents are injected into the
> processing stream.
>
> A
>
>

[Aaron Bertrand - MVP]

Well, not directly related to the problem, but this is a terrible waste, to
use a sub to open a connection, create a recordset object, then destroy
everything. If you have a page with 5 resultsets, you wasted four
connections to the database...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:er**************@TK2MSFTNGP12.phx.gbl...

Honestly, I'm not doing conditional includes or anything weird.
If I hit F5 the page refreshes just fine.

I'll try turning off option explicit, but I would expect I'd just get a
different error.

The error says the variable isn't defined, but it's not a variable, it's a
sub.

Here's the contents of my include....
<include file....>
Function ExecuteSQL(sSQL)
Dim CN
Dim RS
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open Session("g_STRCONN")
Set RS=Server.CreateObject("ADODB.Recordset")
RS.CursorLocation=adUseClient
'Response.Write sSQL & ""
'Response.Flush
RS.Open sSQL,CN
set RS.ActiveConnection=nothing
SET ExecuteSQL=RS
Set RS=nothing
CN.Close
Set CN=nothing
End Function
</include file>

In my page, there's just a call like....

Set RS=ExecuteSQL "SELECT FieldName from TableName"

As I mentioned originally, this doesn't happen on any specific page...but on many.

And it's very infrequent.

Thanks
Tom B

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Can you show the line that causes this error? Sounds like you are trying

to

use ExecuteSQL in a different context...

One thing you might try doing is turning off option explicit. Is it
possible that you are doing conditional includes or something, and one file

has option explicit but the rest don't?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e4**************@TK2MSFTNGP12.phx.gbl...

It happened again.....
Here's the actual errror

Error Type:
Microsoft VBScript runtime (0x800A01F4)
Variable is undefined: 'ExecuteSQL'

ExecuteSQL is the function on my include page.

It's driving me crazy, because it happens so irregularly.

TomB

"TomB" <sh*****@hotmailXXX.com> wrote in message
news:uC**************@TK2MSFTNGP09.phx.gbl...
> On all pages I have
>
>
> <!--#Include Virtual="PathtoFile\File.asp"-->
>
> With in File.asp
>
> Function MyFunction()
> 'MyCode
> End Function
>
>
> Back in all pages I'll call MyFunction and most times it works but
> occassionally it'll say MyFunction undefined.
>
> Thanks
> Tom B
> P.S. I used the quotes thinking it would be clearer. I guess not.
>
> "Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
> news:%2******************@TK2MSFTNGP09.phx.gbl...
> > > I have a function on an "included" page.
> >
> > Can you explain what you mean by "included"? Do you mean #include, or > > something else? Why the quotes?
> >
> > > For reasons unknown to me I frequently will get an error - to

the effect
> > > that the function can't be found.
> >
> > Can you show the exact error, as opposed to "to the effect..."?
> >
> > > I'm wondering if the function is getting
> > > called before the include is pulled in.
> >
> > As I explained in my other reply, this isn't possible. The rest of the

> ASP
> > doesn't exist until the #include file contents are injected into

the > > processing stream.
> >
> > A
> >
> >
>
>

[TomB]

I thought that was the whole point of connection pooling?

It quickly grabs a connection, gets my data, then put's it back.
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:e0**************@tk2msftngp13.phx.gbl...

Well, not directly related to the problem, but this is a terrible waste, to use a sub to open a connection, create a recordset object, then destroy
everything. If you have a page with 5 resultsets, you wasted four
connections to the database...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:er**************@TK2MSFTNGP12.phx.gbl...

Honestly, I'm not doing conditional includes or anything weird.
If I hit F5 the page refreshes just fine.

I'll try turning off option explicit, but I would expect I'd just get a
different error.

The error says the variable isn't defined, but it's not a variable, it's a

sub.

Here's the contents of my include....
<include file....>
Function ExecuteSQL(sSQL)
Dim CN
Dim RS
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open Session("g_STRCONN")
Set RS=Server.CreateObject("ADODB.Recordset")
RS.CursorLocation=adUseClient
'Response.Write sSQL & ""
'Response.Flush
RS.Open sSQL,CN
set RS.ActiveConnection=nothing
SET ExecuteSQL=RS
Set RS=nothing
CN.Close
Set CN=nothing
End Function
</include file>

In my page, there's just a call like....

Set RS=ExecuteSQL "SELECT FieldName from TableName"

As I mentioned originally, this doesn't happen on any specific

page...but on

many.

And it's very infrequent.

Thanks
Tom B

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

Can you show the line that causes this error? Sounds like you are

trying

to

use ExecuteSQL in a different context...

One thing you might try doing is turning off option explicit. Is it
possible that you are doing conditional includes or something, and one

file

has option explicit but the rest don't?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e4**************@TK2MSFTNGP12.phx.gbl...
> It happened again.....
> Here's the actual errror
>
> Error Type:
> Microsoft VBScript runtime (0x800A01F4)
> Variable is undefined: 'ExecuteSQL'
>
> ExecuteSQL is the function on my include page.
>
> It's driving me crazy, because it happens so irregularly.
>
> TomB
>
>
>
> "TomB" <sh*****@hotmailXXX.com> wrote in message
> news:uC**************@TK2MSFTNGP09.phx.gbl...
> > On all pages I have
> >
> >
> > <!--#Include Virtual="PathtoFile\File.asp"-->
> >
> > With in File.asp
> >
> > Function MyFunction()
> > 'MyCode
> > End Function
> >
> >
> > Back in all pages I'll call MyFunction and most times it works but
> > occassionally it'll say MyFunction undefined.
> >
> > Thanks
> > Tom B
> > P.S. I used the quotes thinking it would be clearer. I guess not.
> >
> > "Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
> > news:%2******************@TK2MSFTNGP09.phx.gbl...
> > > > I have a function on an "included" page.
> > >
> > > Can you explain what you mean by "included"? Do you mean

#include,

or

> > > something else? Why the quotes?
> > >
> > > > For reasons unknown to me I frequently will get an error - to

the > effect
> > > > that the function can't be found.
> > >
> > > Can you show the exact error, as opposed to "to the effect..."?
> > >
> > > > I'm wondering if the function is getting
> > > > called before the include is pulled in.
> > >
> > > As I explained in my other reply, this isn't possible. The rest of the
> > ASP
> > > doesn't exist until the #include file contents are injected into the > > > processing stream.
> > >
> > > A
> > >
> > >
> >
> >
>
>

[Dave Anderson]

Aaron Bertrand - MVP wrote:

Well, not directly related to the problem, but this is a terrible
waste, to use a sub to open a connection, create a recordset object,
then destroy everything. If you have a page with 5 resultsets, you
wasted four connections to the database...

I used to think that, too.

But then I got the impression (from reading in here) that connection pooling
overrides the explicit Close() calls, making the connections briefly
available to additional processes, should similar requests follow in short
order.

IIRC, it was claimed that closing recordsets and connections immediately
after use was preferable because it allowed IIS more flexibility in
allocating connections.

I have no idea whether this is true, but I have one particular job that
seemed a good candidate for testing this. I have a process that uses a
vendor-provided interface to scrape their proprietary database. It takes
about 15 minutes to get 12,000 records out of their DB (roughly 13 records
per second), which is an awfully long time to keep a SQL Server (the
destination for my data dump) connection open.

About a month ago, I modified the job script (yes, this is ASP) to open a
connection, update the DB, and close the connection for each record
processed.

Fortunately, I have been logging job stats for the last 13 months, so I have
an accurate picture of performance. And now I have a month of new data with
which to compare. In the past month, the job has taken anywhere from 14:59
to 16:45, and over the previous year, from 14:39 to 20:06. In both cases,
those were limts -- probably 90% fall within 30 seconds of 15:45.

I can't really draw any conclusions from this. I have noted no measurable
differences, other than the extra coding required to facilitate an
open/close cycle for every operation.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Aaron Bertrand - MVP]

> I thought that was the whole point of connection pooling?

It quickly grabs a connection, gets my data, then put's it back.

Connection pooling assists you on a page by page basis. This is what makes
it more efficient to open a *single* connection object per page. Connection
pooling cannot do anything for you if you decide to write a page that
creates and destroys 20 connection objects, and really has nothing to do
with the scenario you're describing. Each connection you open will likely
take advantage of connection pooling, but you're going to be causing
inefficiency by other means, e.g. going into the pool six times instead of
once, and the mere work of instantiating ADODB.Connection *and*
ADODB.Recordset so many times.

I take it you don't think there's any difference in resource usage between
SNIPPET A and SNIPPET B? Have you run any benchmarks?

<SNIPPET A>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something else")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET A>

<SNIPPET B>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.execute("something else")
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET B>

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Roland Hall]

"TomB" wrote in message news:er**************@TK2MSFTNGP12.phx.gbl...
: Honestly, I'm not doing conditional includes or anything weird.
: If I hit F5 the page refreshes just fine.
:
: I'll try turning off option explicit, but I would expect I'd just get a
: different error.
:
: The error says the variable isn't defined, but it's not a variable, it's a
: sub.
:
: Here's the contents of my include....
: <include file....>
: Function ExecuteSQL(sSQL)
: Dim CN
: Dim RS
: Set CN=Server.CreateObject("ADODB.Connection")
: CN.Open Session("g_STRCONN")
: Set RS=Server.CreateObject("ADODB.Recordset")
: RS.CursorLocation=adUseClient
: 'Response.Write sSQL & ""
: 'Response.Flush
: RS.Open sSQL,CN
: set RS.ActiveConnection=nothing
: SET ExecuteSQL=RS
: Set RS=nothing
: CN.Close
: Set CN=nothing
: End Function
: </include file>
:
: In my page, there's just a call like....
:
: Set RS=ExecuteSQL "SELECT FieldName from TableName"
:
: As I mentioned originally, this doesn't happen on any specific page...but
on
: many.
:
: And it's very infrequent.

Why is this:

SET ExecuteSQL=RS

instead of just:

ExecuteSQL=RS
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[Bob Barrows [MVP]]

Roland Hall wrote:

Why is this:

SET ExecuteSQL=RS

instead of just:

ExecuteSQL=RS

Ummmm ... because RS is an object, not a scalar variable? And he's
attempting to return the RS object to caller? :-)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Bob Barrows [MVP]" wrote in message
news:ev**************@TK2MSFTNGP11.phx.gbl...
: Roland Hall wrote:
: > Why is this:
: >
: > SET ExecuteSQL=RS
: >
: > instead of just:
: >
: > ExecuteSQL=RS
:
: Ummmm ... because RS is an object, not a scalar variable? And he's
: attempting to return the RS object to caller? :-)

Are you answering my question with a question? two actually? (O:=

[Bob Barrows [MVP]]

Roland Hall wrote:

"Bob Barrows [MVP]" wrote in message
news:ev**************@TK2MSFTNGP11.phx.gbl...

Roland Hall wrote:

Why is this:

SET ExecuteSQL=RS

instead of just:

ExecuteSQL=RS

Ummmm ... because RS is an object, not a scalar variable? And he's
attempting to return the RS object to caller? :-)

Are you answering my question with a question? two actually? (O:=

Why do you say that? :-)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[TomB]

Thanks Aaron and others.

I honestly thought this was the better way to go. I thought that using
disconnected recordsets, would be more efficient.

I do (usually) only call my function once on a page. But, you are right
there are times I don't.

My other goal with this function was that it was in one spot, which offered
me the option of maintaining only one piece of database code.

I gather, Aaron that you'd recommend opening a connection at the top of a
page, and closing it at the end? Assuming of course that you actually need
a connection on the page. What about the recordsets? Do you close and
discard them as soon as possible, or just re-use as needed?

Roland... In asp you have to SET an object, while in .Net you don't
(actually I think you can't). I'm guessing that's where the confusion lie.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...

I thought that was the whole point of connection pooling?

It quickly grabs a connection, gets my data, then put's it back.
Connection pooling assists you on a page by page basis. This is what

makes it more efficient to open a *single* connection object per page. Connection pooling cannot do anything for you if you decide to write a page that
creates and destroys 20 connection objects, and really has nothing to do
with the scenario you're describing. Each connection you open will likely
take advantage of connection pooling, but you're going to be causing
inefficiency by other means, e.g. going into the pool six times instead of
once, and the mere work of instantiating ADODB.Connection *and*
ADODB.Recordset so many times.

I take it you don't think there's any difference in resource usage between
SNIPPET A and SNIPPET B? Have you run any benchmarks?

<SNIPPET A>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something else")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET A>

<SNIPPET B>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.execute("something else")
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET B>

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Bob Barrows [MVP]]

TomB wrote:

Thanks Aaron and others.

I honestly thought this was the better way to go. I thought that
using disconnected recordsets, would be more efficient.
I have used disconnected recordsets, when I've needed to use Filter, Find or
Sort functionality (or any other functionality provided by a recordset
object that can't be achieved in a string or an array). Otherwise, it's
either GetRows or GetString immediately after opening the recordset,
followed by Close.

I do (usually) only call my function once on a page. But, you are
right there are times I don't.

My other goal with this function was that it was in one spot, which
offered me the option of maintaining only one piece of database code.

I gather, Aaron that you'd recommend opening a connection at the top
of a page, and closing it at the end?
That's how all my pages work.
Assuming of course that you
actually need a connection on the page. What about the recordsets?
Do you close
Yes. My normal procedure is to dump the data into an array or string as
soon as possible and close the recordset.
and discard them as soon as possible,

I normally discard/destroy them as soon as I am sure I will not use them
anymore.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[TomB]

Thanks Bob.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

TomB wrote:

Thanks Aaron and others.

I honestly thought this was the better way to go. I thought that
using disconnected recordsets, would be more efficient.
I have used disconnected recordsets, when I've needed to use Filter, Find

or Sort functionality (or any other functionality provided by a recordset
object that can't be achieved in a string or an array). Otherwise, it's
either GetRows or GetString immediately after opening the recordset,
followed by Close.

I do (usually) only call my function once on a page. But, you are
right there are times I don't.

My other goal with this function was that it was in one spot, which
offered me the option of maintaining only one piece of database code.

I gather, Aaron that you'd recommend opening a connection at the top
of a page, and closing it at the end?

That's how all my pages work.

Assuming of course that you
actually need a connection on the page. What about the recordsets?
Do you close

Yes. My normal procedure is to dump the data into an array or string as
soon as possible and close the recordset.

and discard them as soon as possible,

I normally discard/destroy them as soon as I am sure I will not use them
anymore.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Patrice]

Yes, IMO it doesn't defeat that much connection pooling. My understanding is
that :
- you'll get at the pool 5 times to get a connection
- you still benefit IMO from connection poolling in particular you still
have a single connection open at any one time not using up to 5 connections
and even more importantly as they are returned to the pool, another page
served to another user will be able to reuse the connection(s) you used

IMO you just loose the time needed to get/release 5 connection from/to the
pool but you still benefit from the pool...

Patrice

"Dave Anderson" <GT**********@spammotel.com> a écrit dans le message de
news:uL**************@TK2MSFTNGP10.phx.gbl...

Aaron Bertrand - MVP wrote:

Well, not directly related to the problem, but this is a terrible
waste, to use a sub to open a connection, create a recordset object,
then destroy everything. If you have a page with 5 resultsets, you
wasted four connections to the database...
I used to think that, too.

But then I got the impression (from reading in here) that connection

pooling overrides the explicit Close() calls, making the connections briefly
available to additional processes, should similar requests follow in short
order.

IIRC, it was claimed that closing recordsets and connections immediately
after use was preferable because it allowed IIS more flexibility in
allocating connections.

I have no idea whether this is true, but I have one particular job that
seemed a good candidate for testing this. I have a process that uses a
vendor-provided interface to scrape their proprietary database. It takes
about 15 minutes to get 12,000 records out of their DB (roughly 13 records
per second), which is an awfully long time to keep a SQL Server (the
destination for my data dump) connection open.

About a month ago, I modified the job script (yes, this is ASP) to open a
connection, update the DB, and close the connection for each record
processed.

Fortunately, I have been logging job stats for the last 13 months, so I have an accurate picture of performance. And now I have a month of new data with which to compare. In the past month, the job has taken anywhere from 14:59
to 16:45, and over the previous year, from 14:39 to 20:06. In both cases,
those were limts -- probably 90% fall within 30 seconds of 15:45.

I can't really draw any conclusions from this. I have noted no measurable
differences, other than the extra coding required to facilitate an
open/close cycle for every operation.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use of this email address implies consent to these terms. Please do not contact me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Aaron Bertrand - MVP]

I've only recently started to use getRows() more, but other than that, I
follow pretty much the same practice as Bob.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"TomB" <sh*****@hotmailXXX.com> wrote in message
news:uq*************@tk2msftngp13.phx.gbl...

Thanks Aaron and others.

I honestly thought this was the better way to go. I thought that using
disconnected recordsets, would be more efficient.

I do (usually) only call my function once on a page. But, you are right
there are times I don't.

My other goal with this function was that it was in one spot, which offered me the option of maintaining only one piece of database code.

I gather, Aaron that you'd recommend opening a connection at the top of a
page, and closing it at the end? Assuming of course that you actually need a connection on the page. What about the recordsets? Do you close and
discard them as soon as possible, or just re-use as needed?

Roland... In asp you have to SET an object, while in .Net you don't
(actually I think you can't). I'm guessing that's where the confusion lie.
"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...

I thought that was the whole point of connection pooling?

It quickly grabs a connection, gets my data, then put's it back.

Connection pooling assists you on a page by page basis. This is what

makes

it more efficient to open a *single* connection object per page.

Connection

pooling cannot do anything for you if you decide to write a page that
creates and destroys 20 connection objects, and really has nothing to do
with the scenario you're describing. Each connection you open will likely take advantage of connection pooling, but you're going to be causing
inefficiency by other means, e.g. going into the pool six times instead of once, and the mere work of instantiating ADODB.Connection *and*
ADODB.Recordset so many times.

I take it you don't think there's any difference in resource usage between SNIPPET A and SNIPPET B? Have you run any benchmarks?

<SNIPPET A>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something else")
conn.close
set conn = nothing

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET A>

<SNIPPET B>

set conn = createobject("ADODB.Connection")
conn.open "connection string"
conn.execute("something")
conn.execute("something else")
conn.execute("another thing")
conn.close
set conn = nothing

</SNIPPET B>

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/