Data Source = problem

[James]

I've been having this recurring General Network Error problem and I've been
looking to solve it. Reboot fixes the problem for several hours, but
eventually it happens again. Nothing seems to specifically trigger it.
HTML loads fine, ASP pages that connect to SQL Server, no dice.

Long story short, it happened about 15 minutes ago and one of the pages that
connects to SQL Server worked, but none of the other ones would. The page
in question is also the only page that has Data Source = IP Address. All of
the others refer to a machine name. Could this be the cause of the problem?
Before I change it system-wide, I want to be relatively certain it could be
the cause.

Any ideas?

Thanks!
James

 

[Tom Kaminski [MVP]]

I've been having this recurring General Network Error problem and I've been
looking to solve it. Reboot fixes the problem for several hours, but
eventually it happens again. Nothing seems to specifically trigger it.
HTML loads fine, ASP pages that connect to SQL Server, no dice.

Long story short, it happened about 15 minutes ago and one of the pages that
connects to SQL Server worked, but none of the other ones would. The page
in question is also the only page that has Data Source = IP Address. All of
the others refer to a machine name. Could this be the cause of the problem?
Before I change it system-wide, I want to be relatively certain it could be
the cause.

Any ideas?

Hard to offer any ideas without knowing the exact error message ...

 

[Bob Barrows [MVP]]

I've been having this recurring General Network Error problem and
I've been looking to solve it. Reboot fixes the problem for several
hours, but eventually it happens again. Nothing seems to
specifically trigger it. HTML loads fine, ASP pages that connect to
SQL Server, no dice.

Long story short, it happened about 15 minutes ago and one of the
pages that connects to SQL Server worked, but none of the other ones
would. The page in question is also the only page that has Data
Source = IP Address. All of the others refer to a machine name.
Could this be the cause of the problem? Before I change it
system-wide, I want to be relatively certain it could be the cause.

Any ideas?

Thanks!
James

It sounds as if you may be disabling session/connection pooling:
http://support.microsoft.com/?kbid=328476

You should be striving to make all your connection strings the same. Also,
avoid situations that cause implicit connections to be used:
http://support.microsoft.com/?kbid=271128

That means always using explicit connection objects, never doing things
like:

strconn="provider=..."
rs.open sqlstring,strconn
instead, use
set conn=createobject("adodb.connection")
rs.open sqlstring,conn,,,1
or
set rs=conn.execute(sqlstring,,1)

or

cmd.activeconnection = conn (this causes a new implicit connection)
use this to cause an existing connection object to be used:
set cmd.activeconnection = conn

Bob Barrows

 

[James]

Tell me about it! That was the exact error message. "General Network
Error".

 

[James]

I'm not really a SQL guru. Any idea how I can check to see if Connection
Pooling is enabled/disabled? I read that article and some of it makes
sense, but it just seems to say "here's the possible problem" without a
solution. I am 99% certain that there isn't any code that explictly
disables connection pooling. I'm not sure if this is a setting I need to
explicitly enable within code or it's something I need to handle on the
server.

Thanks

 

[Patrice]

It looks like a problem with name resolution as the page with the IP works
while those with the name fails....

Though it could be a workaround (they don't share all the same connection
string ?), it could worth to see if the server that resolves the name works
always fine or had a problem...

Patrice

 

[Bob Barrows [MVP]]

It's not really a SQL issue: it's an ADO/OLE DB issue.

With ASP, pooling is on by default. You pretty much have to jump through
hoops to turn it off (as long as you avoid the bad programming practices I
described in my first reply).

With SQL Server, you can use SQL Profiler to determine whether pooling is
being used. The thing to look for is calls to sp_reset_connection. If you
see this call, connections are being reused. (If you don't see any calls to
sp_reset_connection, you really don't have any pooling - or connections
occur less than once a minute.)

Bob Barrows

 

[io]

G'day James,

Looks like name resolution service goes off spontaneously. I think you are
quite safe to do what you already suggested - use IP address for Data
Source. If you feel like fixing it properly you better bug your sys/network
admin.

Cheers

 

[James]

That's certainly what it seems like to me. We never had this problem before
(used to use DSNs). We spent the past several months reorganizing our
database/code to a much more efficient structure. Per several suggestions,
we changed the connections away from DSNs and this problem began.

I've changed all of the connection strings to reference the IP address of
the SQL Server. So far, so good...but it's only been about 12 hours.

Thanks for all the help, hopefully this is resolved now. ::knock on wood::

James