Using SQL2005 & varchar(max) 1

[Glasgow]

Has anyone tried to use VB6 to access fields defined as varchar(max) within a SQL 2005 database?

This article seems to suggest it is legal:

http://msdn2.microsoft.com/en-us/library/bb264566.aspx

but I don't seen to be able to get sensible values back when populating an ADO recordset using a SQL query. As soon as I change declaration back to varchar(8000), the code behaves as I would expect.

 

[gmmastros]

I just ran a quick test using vb6, ado 2.8, and sql2005. In my test, I created a stored procedure with a table variable. I inserted data in to this table variable (with varchar(max) column) such that there were a couple records with more than 8000 characters. Everything appeared to work properly.

Since I got the expected behavior, I suspect my test was not the same (or even similar) to your code. I think this is one of those times where more information is necessary. Can you show us some code snippets?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

Thanks for the reply. I've just coded up a simplistic example that reads as follows:

  Dim Cnct As New ADODB.Connection, RsTst As New ADODB.Recordset
  Cnct.ConnectionString = "Driver={SQL Native Client};Server=MYPC\SQLEXPRESS;Database=MyDb;Trusted_Connection=yes;"
  Cnct.Open
  RsTst.Open "SELECT WisData FROM Wis", Cnct
  RsTst.Close
  Cnct.Close
  Set RsTst = Nothing
  Set Cnct = Nothing

The WisData field is defined as VarChar(max) and the table has been pre-loaded (using SQL 'INSERT') with one row in which WisData is a six character string 'Data 1'. I am using ADO 2.8.

When I interrogate the value returned in VB, it is six characters long but contains ascii zeros. Any thoughts?

 

[gmmastros]

Try changing your connection string to....

[tt][blue]Cnct.ConnectionString = "[!]Provider=SQLNCLI[/!];Server=MYPC\SQLEXPRESS;Database=MyDb;Trusted_Connection=yes;"
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should point out the difference.

When you use: [blue]Driver={SQL Native Client}[/blue]
You are effectively using ODBC to connect to the database.

When you use: [blue]Provider=SQLNCLI[/blue]
You are effectively using OleDB to connect to the database.

Under most circumstances, you won't notice much of a difference. OleDB is slightly better for performance reasons, and apparently infinitely better for varchar(max) reasons.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

That appears to have done it, thanks so much. Well the test works anyway. Just need to try out the real thing.

Agreed that improvement is infinite. How annoying that the other one doesn't work!