I added DISTINCT to a statement, and because I added DISTINCT I also had to CAST an nTEXT datatype field in the SELECT statement to nVARCHAR. I chose to use nVARCHAR(MAX) in this case, since I have read that nTEXT is being depreciated and nVARCHAR(MAX) is the recommended substitute. However, once I set it to nVARCHAR(MAX), the string that was contained in that field would no longer print using ASP’s Response.Write. It turns out I all I was getting was an empty string. Yet, when I changed the CAST to nVARCHAR(4000) it worked fine.
I am using SQL Server 2005.
In researching this, I found the following source:
This page behind the link above explains that “In order to take advantage of new features introduced with SQL Server 2005 such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or the new xml data type, existing applications that use ActiveX Data Objects (ADO) should use the SQL Native Client OLE DB provider as their data access provider…If you are enhancing an existing application and you need to use the new features of SQL Server 2005, you should use SQL Native Client OLE DB provider.
To enable ADO to use SQL Server 2005 features, some enhancements have been made to the SQL Native Client OLE DB provider which extends the core features of OLE DB. These enhancements primarily serve to allow ADO applications to use new features provided by SQL Server 2005 and to consume two new SQL Server data types: xml and udt, and to also exploit enhancements to the varchar, nvarchar and varbinary data types.”
The page then goes on to say that I needed to change my connection string in order to take advantage of these new SQL Server 2005 features.
So, I changed the string as the page requested, adding the following to the string:
Provider=SQLNCLI
DataTypeCompatibility=80
However, when I did this, I ended up getting an exception saying that neither of these keys are supported.
Does anyone here have any idea what I can try next?
I am using SQL Server 2005.
In researching this, I found the following source:
This page behind the link above explains that “In order to take advantage of new features introduced with SQL Server 2005 such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or the new xml data type, existing applications that use ActiveX Data Objects (ADO) should use the SQL Native Client OLE DB provider as their data access provider…If you are enhancing an existing application and you need to use the new features of SQL Server 2005, you should use SQL Native Client OLE DB provider.
To enable ADO to use SQL Server 2005 features, some enhancements have been made to the SQL Native Client OLE DB provider which extends the core features of OLE DB. These enhancements primarily serve to allow ADO applications to use new features provided by SQL Server 2005 and to consume two new SQL Server data types: xml and udt, and to also exploit enhancements to the varchar, nvarchar and varbinary data types.”
The page then goes on to say that I needed to change my connection string in order to take advantage of these new SQL Server 2005 features.
So, I changed the string as the page requested, adding the following to the string:
Provider=SQLNCLI
DataTypeCompatibility=80
However, when I did this, I ended up getting an exception saying that neither of these keys are supported.
Does anyone here have any idea what I can try next?