Setting up an OLE DB connection to a SQL Server database

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