MORE INFORMATION
Connection Pooling
Enable ODBC connection pooling.
For additional information about connection pooling and for instructions about how to enable this feature, click the following article number to view the article in the Microsoft Knowledge Base:
164221
How to enable connection pooling in an ODBC application
ODBC DSN
Using the ODBC Administrator, create a System data source name
(DSN) on the computer where Microsoft Internet Information Server (IIS) is
installed. Specify the connection attribute one time, and then reuse it on
every page. For example, in the
Session_OnStart event in Global.asa file, define the connection attribute as:
Session("ConnectionString") =
"dsn=SQLSysDSN;uid=<username>;pwd=<strong password>;DATABASE=pubs;APP=ASP Script"
Make sure that all the following conditions are true:
- The Trusted Connection box is not selected in the System DSN definition.
- The SQL Server security mode is not Windows NT Integrated.
- In the connection attribute, the uid is not blank.
Otherwise, a connection to SQL Server might fail, and you
receive the following error message:
Microsoft
OLE DB provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL
Server Driver][SQL Server]Login failed- User: _
Reason: Not defined as a
valid user of a trusted SQL Server connection.
Global.asa
Use of the Global.asa file is optional. In its place, entries
typically made in this file can be put on the first page called by the
application. Assuming the ASP scripts are located in a folder that is not
defined as a virtual directory in the Internet Service Manager, but below
another virtual directory, the Global.asa file containing Session variables and
DSN definitions must be kept in the virtual directory. Otherwise, you receive
the following error message:
Microsoft OLE DB
Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver
Manager] Data source name not found and no
default driver specified
Connections in the ASP Script
Take advantage of connection pooling by opening and closing the
connection to the database on every active server page. To open the connection,
type the following statements in the <Body> section of the page:
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open Session("ConnectionString") %>
To close the connection, put the following immediately after the
</Body> tag:
<%
OBJdbConnection.Close
Set OBJdbConnection = Nothing
%>
You may receive the following two error messages if the connection
settings are not correctly defined as outlined earlier in this article:
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Connection
broken.
Microsoft OLE DB Provider for ODBC
Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Communication
link failure
Here is a sample application that consists of the
Global.asa and the Authors.asp files. This sample application will return four
columns and all the records in the
pubs table named
authors.
Note You must change UID <username> and
pwd =<strong password> to the correct values before you run this code. Make
sure that UID has the appropriate permissions to perform this operation on the
database.
Global.asa
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
Session("ConnectionString") =
"DSN=SQLSysDSN;UID=<username>;PWD=<strong password>;DATABASE=pubs;APP=ASP script"
Session("ConnectionTimeout") = 15
Session("CommandTimeout") = 30
End Sub
Sub Session_OnEnd
End Sub
</SCRIPT>
Authors.asp
<HTML>
<HEAD>
<TITLE>All Authors</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<% Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject ("ADODB.Recordset")
%>
<p>
<table border="0" bordercolor="#000000">
<%
SQLStmt.CommandText = "select * from authors"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
Do While Not RS.EOF
%>
<TR>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_id") %>
</FONT></TD>
<TD></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_lname") %>
</FONT></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_fname") %>
</FONT></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("phone") %>
</FONT></TD>
</TR>
<%
RS.MoveNext
Loop
%>
</table>
<hr>
<p>
</BODY>
<% OBJdbConnection.Close
Set OBJdbConnection = Nothing
%>
</HTML>
For more information about Active Server Pages, see the Roadmap
provided by the ASP setup program on the IIS server.