How to access SQL Server in Active Server Page
Suitable
summary
This article describes how to use "ActiveX Data Object" (ADO) to create a connection to SQL Server in the ASP script while utilizing the connection pool function of ODBC 3.0.
More information
The connection pool enables the ODBC connection pool.
For other information about connecting pools, click on how to enable connection pool features, click the article number below to view the article in the Microsoft Knowledge Base:
16422 How to enable connection pool in the ODBC application
ODBC DSN creates a "System" Data Source Name (DSN) on a computer installed in Microsoft Internet Information Server (IIS). Specify the connection attribute at a time, then repeat this property on each page. For example, in the global.asa file
In the session_onstart event, the connection property is defined as:
Session ("Connectionstring") = "DSN = SQLSYSDSN; UID =
The trusted connection box is not selected in the System DSN definition. SQL Server security mode is not "Windows NT Integration". In the connection properties, the UID is not empty. Otherwise, the connection to SQL Server may fail and 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 is optional using the Global.asa file. At its location, items typically created by this file can be placed on the first page of the application call. Assume that the ASP script is located in a folder, which is not defined as a virtual directory in "Internet Service Manager", but below another virtual directory, then the Global.asa file containing the session variable and DSN definition must Keep in this virtual directory. Otherwise, the following error message will appear:
Microsoft OLE DB Provider for ODBC Drivers Error '80004005'
[Microsoft] [ODBC Driver Manager] Data Source Name Not Found and NO
Default Driver Specified
The connection in the ASP script utilizes the connection pool by opening and closing the connection of the database on each Active Server page. To open this connection, type the following statement in the
section of the page:<% Set objdbconnection = server.createObject ("adodb.connection" ObjdbConnection.open session ("Connectionstring")%> To close this connection, put the following after the body> tag:
<% ObjdbConnection.close Set ObjdbConnection = NOTHING%> If you do not correctly define connection settings as you present in the article, you may receive the following two error messages: Microsoft Ole DB Provider for ODBC Drivers Error '80004005'
[Microsoft] [ODBC SQL Server Driver] [DBNMPNTW] Connection Broken.
- or -
Microsoft OLE DB Provider for ODBC Drivers Error '80004005'
[Microsoft] [ODBC SQL Server Driver] Communication Link Failure
Here is an example application that contains Global.asa and Authors.asp files. This sample application will return named
Authors
Four columns and all records in the PUBS table.
Note: You must first change the UID
Global.asa