Two Ways To Connect To SQL Server 2000 Via ASP - Using system DSN's

[ganeshbala]

Using system DSN's

Click on the next button to continue with the wizard. If you have already designed a new database or just prefer not to use the master database once you're actually connected to SQL Server (highly recommended: the master database contains several important extended stored procedures and tables that should be treated with care), then change the default database name from "master" to "Northwind", for example.

The "Attach Database Filename" text box allows you to attach a database file to your DSN. We won't use this method in our example. Make sure the "Use ANSI quoted identifiers" and "Use ANSI nulls, paddings and warnings" check boxes are checked. They tell SQL server to make sure that in any SQL queries, identifiers containing special characters or match keywords must be enclosed in identifier quote characters. Click the next button to move onto the last tab of the system DSN wizard.

All of the options on this tab can be left as is, accept one. If you have specialized currency symbols, dates or regional settings, then make sure that the "Use regional settings when outputting currency, numbers, dates and times" check box is checked. This will save you the effort of having to convert dates and other fields to your countries local format every time they are used.

That's all we need to do to create a system DSN. Click on the finish button. The "Verify DSN Settings" dialog appears. If you'd like to test our newly created system DSN, then click on the "Test Data Source..." button. If not, simply click OK.

To connect to your database using a system DSN, it's simply a matter of instantiating a new ADO connection object from within your ASP script and calling its open method with a reference to your system DSN, like this:

<%

Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "DSN=TestDSN"

If conn.errors.count = 0 Then

Response.write "Connected OK"

End If

%>

There are both advantages and disadvantages that result from using system DSN's to connect to a database. They are shown below:

Advantages:

    * System DSN's are available to any authenticated windows user.
    * Details of the database connection are not stored in any physical file or ASP script, and are therefore more secure.
    * Because a system DSN is stored in the registry, it can be modified through the "Data Sources (ODBC)" tab just as easily as it can be created.

Disadvantages:

    * Because system DSN's are stored in the registry, every time we use them to connect to our database from within ASP, the ASP engine has to do a registry lookup to get the DSN's connection and authentication credentials.
    * System DSN's are slower than using an OLEDB connection string directly. This is only slightly noticeable (approximately a 15% speed differential) when more than 10 users are concurrently connected.