Microsoft KB Archive/169377: Difference between revisions

From BetaArchive Wiki
m (Text replacement - "<" to "<")
m (Text replacement - ">" to ">")
Line 87: Line 87:
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:
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:
<pre class="codesample">  Session(&quot;ConnectionString&quot;) =
<pre class="codesample">  Session(&quot;ConnectionString&quot;) =
   &quot;dsn=SQLSysDSN;uid=<username&gt;;pwd=<strong password&gt;;DATABASE=pubs;APP=ASP Script&quot;                </pre>
   &quot;dsn=SQLSysDSN;uid=<username>;pwd=<strong password>;DATABASE=pubs;APP=ASP Script&quot;                </pre>
Make sure that all the following conditions are true:
Make sure that all the following conditions are true:
* The '''Trusted Connection''' box is not selected in the System DSN definition.
* The '''Trusted Connection''' box is not selected in the System DSN definition.
Line 120: Line 120:
=== Connections in the ASP Script ===
=== 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&gt; section of the page:
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:
<pre class="codesample">  <%
<pre class="codesample">  <%
   Set OBJdbConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
   Set OBJdbConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
   OBJdbConnection.Open Session(&quot;ConnectionString&quot;)  %&gt;             </pre>
   OBJdbConnection.Open Session(&quot;ConnectionString&quot;)  %>             </pre>
To close the connection, put the following immediately after the </Body&gt; tag:
To close the connection, put the following immediately after the </Body> tag:
<pre class="codesample">  <%
<pre class="codesample">  <%
   OBJdbConnection.Close
   OBJdbConnection.Close
   Set OBJdbConnection = Nothing
   Set OBJdbConnection = Nothing
   %&gt;               </pre>
   %>               </pre>
You may receive the following two error messages if the connection settings are not correctly defined as outlined earlier in this article:<br />
You may receive the following two error messages if the connection settings are not correctly defined as outlined earlier in this article:<br />


Line 154: Line 154:
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'''.<br />
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'''.<br />
<br />
<br />
'''Note''' You must change UID <username&gt; and pwd =<strong password&gt; to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.<br />
'''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.<br />
<br />
<br />
'''Global.asa'''
'''Global.asa'''
<pre class="codesample">  <SCRIPT LANGUAGE=VBScript RUNAT=Server&gt;
<pre class="codesample">  <SCRIPT LANGUAGE=VBScript RUNAT=Server>
   Sub Session_OnStart
   Sub Session_OnStart
   Session(&quot;ConnectionString&quot;) =
   Session(&quot;ConnectionString&quot;) =
   &quot;DSN=SQLSysDSN;UID=<username&gt;;PWD=<strong password&gt;;DATABASE=pubs;APP=ASP script&quot;
   &quot;DSN=SQLSysDSN;UID=<username>;PWD=<strong password>;DATABASE=pubs;APP=ASP script&quot;
       Session(&quot;ConnectionTimeout&quot;) = 15
       Session(&quot;ConnectionTimeout&quot;) = 15
       Session(&quot;CommandTimeout&quot;) = 30
       Session(&quot;CommandTimeout&quot;) = 30
Line 168: Line 168:


   End Sub
   End Sub
   </SCRIPT&gt;             </pre>
   </SCRIPT>             </pre>
'''Authors.asp'''
'''Authors.asp'''
<pre class="codesample">  <HTML&gt;
<pre class="codesample">  <HTML>
   <HEAD&gt;
   <HEAD>
   <TITLE&gt;All Authors</TITLE&gt;
   <TITLE>All Authors</TITLE>
   </HEAD&gt;
   </HEAD>
   <BODY BGCOLOR=&quot;#FFFFFF&quot;&gt;
   <BODY BGCOLOR=&quot;#FFFFFF&quot;>


   <% Set OBJdbConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
   <% Set OBJdbConnection = Server.CreateObject(&quot;ADODB.Connection&quot;)
Line 182: Line 182:
   Set SQLStmt = Server.CreateObject(&quot;ADODB.Command&quot;)
   Set SQLStmt = Server.CreateObject(&quot;ADODB.Command&quot;)
   Set RS = Server.CreateObject (&quot;ADODB.Recordset&quot;)
   Set RS = Server.CreateObject (&quot;ADODB.Recordset&quot;)
   %&gt;
   %>


   <p&gt;
   <p>
   <table border=&quot;0&quot; bordercolor=&quot;#000000&quot;&gt;
   <table border=&quot;0&quot; bordercolor=&quot;#000000&quot;>
   <%
   <%
   SQLStmt.CommandText = &quot;select * from authors&quot;
   SQLStmt.CommandText = &quot;select * from authors&quot;
Line 193: Line 193:


   Do While Not RS.EOF
   Do While Not RS.EOF
   %&gt;
   %>
   <TR&gt;
   <TR>
       <TD Width = 150 ALIGN=LEFT&gt;
       <TD Width = 150 ALIGN=LEFT>
         <FONT SIZE=+1&gt;
         <FONT SIZE=+1>
         <%= RS(&quot;au_id&quot;) %&gt;
         <%= RS(&quot;au_id&quot;) %>
         </FONT&gt;</TD&gt;
         </FONT></TD>
       <TD&gt;</TD&gt;
       <TD></TD>
         <TD Width = 150 ALIGN=LEFT&gt;
         <TD Width = 150 ALIGN=LEFT>
         <FONT SIZE=+1&gt;
         <FONT SIZE=+1>
         <%= RS(&quot;au_lname&quot;)  %&gt;
         <%= RS(&quot;au_lname&quot;)  %>
         </FONT&gt;</TD&gt;
         </FONT></TD>
       <TD Width = 150 ALIGN=LEFT&gt;
       <TD Width = 150 ALIGN=LEFT>
         <FONT SIZE=+1&gt;
         <FONT SIZE=+1>
         <%= RS(&quot;au_fname&quot;)  %&gt;
         <%= RS(&quot;au_fname&quot;)  %>
         </FONT&gt;</TD&gt;
         </FONT></TD>
       <TD Width = 150 ALIGN=LEFT&gt;
       <TD Width = 150 ALIGN=LEFT>
         <FONT SIZE=+1&gt;
         <FONT SIZE=+1>
         <%= RS(&quot;phone&quot;)  %&gt;
         <%= RS(&quot;phone&quot;)  %>
         </FONT&gt;</TD&gt;
         </FONT></TD>
   </TR&gt;
   </TR>
   <%
   <%
   RS.MoveNext
   RS.MoveNext
   Loop
   Loop
   %&gt;
   %>
   </table&gt;
   </table>
   <hr&gt;
   <hr>
   <p&gt;
   <p>
   </BODY&gt;
   </BODY>
   <% OBJdbConnection.Close
   <% OBJdbConnection.Close
   Set OBJdbConnection = Nothing
   Set OBJdbConnection = Nothing
   %&gt;
   %>
   </HTML&gt;               </pre>
   </HTML>               </pre>
For more information about Active Server Pages, see the Roadmap provided by the ASP setup program on the IIS server.
For more information about Active Server Pages, see the Roadmap provided by the ASP setup program on the IIS server.


Revision as of 20:29, 20 July 2020

Article ID: 169377

Article Last Modified on 11/20/2006


APPLIES TO

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft Internet Information Server 4.0
  • Microsoft Internet Information Server 3.0
  • Microsoft Internet Information Services 5.0
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition


This article was previously published under Q169377

SUMMARY

This article describes how to establish connections to SQL Server in an ASP script by using ActiveX Data Objects (ADO), while taking advantage of the connection pooling feature of ODBC 3.0.





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.

-or-



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 = 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.

REFERENCES

Didn't see an answer to your question? Visit the Microsoft SQL Server Newsgroups at:

Microsoft SQL Server Newsgroups


Comments about this or other Microsoft SQL Server Knowledge Base articles? Drop us a note at Q169377


Additional query words: browser explorer web sqlfaqtop

Keywords: kbenv kbhowto kbinterop kbtshoot KB169377



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/169377&oldid=290425’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki