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]

Re: Cannot Read Varchar(max)

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]

Re: Cannot Read Varchar(max)

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" <Plamen@SQLStud io.comwrote in message
news:a6qdnYVrYZ awdqvVnZ2dnUVZ_ uidnZ2d@speakea sy.net...

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

[Plamen Ratchev]

Re: Cannot Read Varchar(max)

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?

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[netzorro]

Re: Cannot Read Varchar(max)

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

"Plamen Ratchev" <Plamen@SQLStud io.comwrote in message
news:876dnV-psaS_lKrVnZ2dnU VZ_hninZ2d@spea keasy.net...

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?
>
HTH,
>
Plamen Ratchev
http://www.SQLStudio.com

[netzorro]

Re: Cannot Read Varchar(max)

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

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


If myText is Text or varchar(n) it works fine. But if myText is varchar(max)
*oData.m_pstrin gA 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" <Plamen@SQLStud io.comwrote in message
news:876dnV-psaS_lKrVnZ2dnU VZ_hninZ2d@spea keasy.net...

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?
>
HTH,
>
Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Re: Cannot Read Varchar(max)

netzorro (netzorro@blueb ottle.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, esquel@sommarsk og.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]

Re: Cannot Read Varchar(max)

netzorro (netzorro@blueb ottle.com) writes:

The code is like this:
oRecordset = getRecordset("s elect myText from myTable");
>
while (!oRecordset->IsEOF())
{
oRecordset->GetFieldValue( 1,oData);
if (oData.m_dwType == DBVT_ASTRING)
{
sAux = *oData.m_pstrin gA;
}
oRecordset->MoveNext();
}
>
>
If myText is Text or varchar(n) it works fine. But if myText is
varchar(max) *oData.m_pstrin gA 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=SQLNCL I, try adding DataTypeCompati bility=80 to the
connection string. ADO does not understand the new data types
added to SQL 2005 very well.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[netzorro]

Re: Cannot Read Varchar(max)

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" <esquel@sommars kog.sewrote in message
news:Xns9AA7F14 D4D629Yazorman@ 127.0.0.1...

netzorro (netzorro@blueb ottle.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, esquel@sommarsk og.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/prodinf...ons/books.mspx

[Erland Sommarskog]

Re: Cannot Read Varchar(max)

netzorro (netzorro@blueb ottle.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 DataTypeCompati bility=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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Plamen Ratchev]

Re: Cannot Read Varchar(max)

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
DataTypeCompati bility=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.

HTH,

Plamen Ratchev

http://www.SQLStudio.com