Setting up an OLE DB connection to a SQL Server database

SQL Server / ASP

OLE DB connections not only offer greater performance than ODBC (DSN) , but also allow some additional functions and have the benefit that you do not need to wait for your web hosting provider to set up a DSN on your server.

First create a new text file, by default windows will name it New Text Document.txt, rename the file NewConnection.udl, or whatever you wish, the important thing is to change the file extension to .udl (Universal Data Link). Windows will give you a warning message about renaming file extensions, click Yes to proceed.

Double click the newly created .udl file, this will open the Data Link Properties box shown below:

Select Microsoft OLE DB Provider for SQL Server from the list shown and click the Next>> button.

To connect to a SQL Server database on your local PC, select or type in the server name in the text box number 1. Next click the relevant radio button and select whether you wish to use Windows NT integrated security or a specific user name and password. Finally select the database on the SQL Server to which you wish to connect.

Click the Test Connection button to confirm that everything is working correctly and click OK. Now right click the .udl file and select Open with Notepad.

If you selected Windows NT Integrated security you should see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDB;_
       Data Source=YOUR_LOCAL_SERVER_NAME

If you selected Use a specific user name and password, you will see something like this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
       Initial Catalog=YourDB;Data Source=YOUR_LOCAL_SERVER_NAME

You can now copy and paste the connection string wherever it's required, for example:

<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;_
    PersistSecurity Info=False;Initial Catalog=_
    YourDB;_Data Source=YOUR_LOCAL_SERVER_NAME
strInsertCommand = "INSERT INTO tableName(TextField, NumericField)_
    VALUES('ABCDE', 12345);"
commInsert.Execute(strInsertCommand) ' Execute the insert command
commInsert.Close()
Set commInsert = Nothing
%>

Connecting to the remote database

Connecting to a remote SQL Server database is almost identical to connecting to a local database.

Create a .udl (Universal Data Link) file as in the first section, select Microsoft OLE DB Provider for SQL Server from the list and click Next, as before.

The details required to make the connection should be given to you by the host provider or your server administrator. Type in the server name in the text box, number 1, this may be an IP address as shown or a domain address like http//YourServer.com. Next enter the user name and password supplied by the host provider or administrator. Finally select your database on the SQL Server to which you wish to connect.

Open the .udl file with notepad, the connection string will appear similar to this:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourUserID;_
Initial Catalog=YourDB;Data Source=YOUR_REMOTE_SERVER_NAME

Notes for Dreamweaver users

Follow the procedure outlined in the first section, then when you define your site select Custom Connection String.

Name your connection and paste the connection string created in the .udl file into the Connection String text box and click OK.

This will define the connection to the database on your local PC. When you upload the site to the remote host server open the remote copy of file YourConn.asp in the Connections directory, it should look something like this:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_YourSQLonn_STRING
MM_YourSQLonn_STRING = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist_
Security Info=False;Initial Catalog=YourDB;_
Data Source=YOUR_LOCAL_SERVER_NAME"
%>

Modify the path stored in the MM_YourConn_STRING variable to the path to your remote SQL Server database. When you modify files locally and upload to the server, take care not to upload the Connections folder and overwrite the remote version of the connection file.