Connecting to MSSQL Using Classic ASP: Compatibility Issues and Solutions

I was recently notified that my site was taking up too many resources and that I had to upgrade my account. I agreed to move from a PersonalClass to BusinessClass plan and my new account was quickly set up by Arvixe, along with the content and the vast majority of my configurations.

One of my planned changes was to modify the connection string I was using. I was directed to this article with 2 options:

  1. “Provider=SQLOLEDB;Data Source=myServerAddress;UID=myUsername; PWD=myPassword; database=myDataBase;”
  2. “Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;”

The second option offers better performance, with a catch. If you are using classic ASP, you will have compatibility problems with nText and nVarchar2(max) datatypes, if your database uses them. Specifically, I found out that I was unable to retrieve the values using the straight-forward way response.write(rs.fields(“something”)). The value was empty.

If the field datatype is nText, you should first store the value to a local variable and then use it

Dim temp_description

temp_description = rs.fields(“description”)

response.write(temp_description)

Unfortunately, this means that if are not already doing that, it will require time consuming changes to your code.

Moreover, the values of nVarchar2(max) fields are not available, no matter what.

Using the first option or the connection string below fixed my problem:

Provider=SQLNCLI10;DataTypeCompatibility=80;Server=myServerAddress; Database=myDatabase;Uid=myUsername;Pwd=myPassword;

I hope that someone will find the information above helpful!

Tags: , , , , , , , , | Posted under MSSQL, Programming/Coding, Using Arvixe | RSS 2.0

One Comment on Connecting to MSSQL Using Classic ASP: Compatibility Issues and Solutions

  1. Everest says:

    Thank you so much for your post, please is it good to use phpads in elgg, if yes do you kown how?

Leave a Reply

Your email address will not be published. Required fields are marked *


7 − 6 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>