Cannot Read Varchar(max)

[netzorro]

Hi all,
considering the new varchar(max) type I have changed some text columns in
some tables.
The problem is the "SQL Native Client 2005.90.1399.00" odbc driver does not
return any value for this varchar(max) column.
If I use the old odbc driver SQL Server 2000.85.117.00 it works fine.

Any idea why this is not working with the 2005 driver?

Thanks

_______________
Fabio F, Fullin
cel: 15-5479-1821

[Plamen Ratchev]

One thing to consider with the SQL Native Client ODBC driver is that
VARCHAR(MAX) has does not have fixed size and the ODBC driver represents
this by returning a max column size of 0. This can confuse your application
if it doesn't check for 0 as a special case. See the bottom section of this
article:
http://msdn.microsoft.com/en-us/library/ms130896.aspx

But in general I have not seen this happen with any of my .NET applications
as it is handled properly in ADO.NET.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[netzorro]

Thanks, I found this kind of answers before. But it's not about the max
because
the driver does not return any information at all.

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:a6qdnYVrYZawdqvV...@speakeasy.net...

[Plamen Ratchev]

What is your application, .NET or something else? What happens if you change
the column to VARCHAR(8000) or TEXT, do you see any values? What is the
query that you run?

[netzorro]

yes, if the column is text or varchar(n) it works.
My application is C++ in VisualStudio 2005

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:876dnV-psaS_lKrV...@speakeasy.net...

[netzorro]

The code is like this:
oRecordset = getRecordset("select myText from myTable");

while (!oRecordset->IsEOF())
{
oRecordset->GetFieldValue(1,oData);
if (oData.m_dwType == DBVT_ASTRING)
{
sAux = *oData.m_pstringA;
}
oRecordset->MoveNext();
}

If myText is Text or varchar(n) it works fine. But if myText is varchar(max)
*oData.m_pstringA points to an empty string.

My workaround for now is to change the query: "select cast(myText as
varchar(5000)) from myTable"

Thanks,
Diego

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:876dnV-psaS_lKrV...@speakeasy.net...

[Erland Sommarskog]

netzorro (netz...@bluebottle.com) writes:
> considering the new varchar(max) type I have changed some text columns
> in some tables. The problem is the "SQL Native Client 2005.90.1399.00"
> odbc driver does not return any value for this varchar(max) column. If I
> use the old odbc driver SQL Server 2000.85.117.00 it works fine.

Maybe you could post the piece of code that is not working? Not that
I can promise to help, since I have not done much ODBC programming.
But at least it gives some idea what the problem might be.

By the way, 1399 is the RTM version of SQL 2005. Maybe this is a bug
that is fixed in SP1 or SP2. So try installing SP2 and see if it helps.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

netzorro (netz...@bluebottle.com) writes:
> The code is like this:
> oRecordset = getRecordset("select myText from myTable");
>
> while (!oRecordset->IsEOF())
> {
> oRecordset->GetFieldValue(1,oData);
> if (oData.m_dwType == DBVT_ASTRING)
> {
> sAux = *oData.m_pstringA;
> }
> oRecordset->MoveNext();
> }
>
>
> If myText is Text or varchar(n) it works fine. But if myText is
> varchar(max) *oData.m_pstringA points to an empty string.

Wait, didn't you say that you were using ODBC? This looks like ADO
to me.

What does your connection string look like? If you have
PROVIDER=SQLNCLI, try adding DataTypeCompatibility=80 to the
connection string. ADO does not understand the new data types
added to SQL 2005 very well.

[netzorro]

I've tried with SP2 too.
SQL Native Client 2005.90.3042.00 and didn't work.
The code is similar but it's ODBC. I define the connection using the odbc
control panel and all.
The same code works for varchar(n) and for text but it doesn't for
varchar(max)
I've seen this error in the web (I'm not the first one with this problem)
but I could never find solution.
Thanks
Diego

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9AA7F14D4...@127.0.0.1...

[Erland Sommarskog]

netzorro (netz...@bluebottle.com) writes:
> I've tried with SP2 too.
> SQL Native Client 2005.90.3042.00 and didn't work.
> The code is similar but it's ODBC.

oRecordset->MoveNext looks awfully much like ADO to me.

> I define the connection using the odbc control panel and all.

But how does the connection string look like? If you are using ADO,
use the SQLNCLI provider, and add DataTypeCompatibility=80 to the
connection string.

Since you talk about ODBC, I assume that you use MSDASQL, that is
OLE DB over ODBC. I find it difficult to see any particular reason to
do this.

[Plamen Ratchev]

It has been a very long time since I have used C++...

I am really not sure how those MFC classes recognize the new data types in
SQL Server 2005. Probably worth trying what Erland suggested to set
DataTypeCompatibility=80 in the connection string. I know that does the
trick for ADO.

Did you step through the code to see if the return type is really
DBVT_ASTRING? Just a guess here, but what if you add checks for DBVT_STRING
or DBVT_BINARY. Some frameworks recognize the new data types as binary
objects, so maybe reading as binary and then converting to string will do
it.