none
Problem with VARCHAR(MAX) datatype when using {SQL Native Client} ODBC Driver RRS feed

  • Question

  • Hi,
    I came across one interesting thing that I got a drastic performance improvement in my classic ASP application, that is using SQL Server 2005 as DBMS, just by changing the ODBC driver.

    Earlier my query string was something like:
    StrConnect="Driver={SQL Server}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"

    I just changed the query string to :
    StrConnect="Driver={SQL Native Client}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"

    And the performance got improved drastically.

    However, I have some problems with {SQL Native Client} driver. It's not returning VARCHAR(MAX ) datatype fields.

    I know I can use : StrConnect="Provider = SQLOLEDB;Data Source = ServerName;Initial Catalog = DBName;User ID = userID;Password = Password ;DataTypeCompatibility=80"

    But with this OLE DB provider the major problem is, it is not returning correct value for Recordset.RecordCount property. It always return -1. And that is not acceptable, as I am using Recordset.Recordcount at so many places in my ASP pages. I can Set my Recordset.CursorLocation = 3 (Client - side) to make it work. But I do not want client-side cursor.

    I just want to use {SQL Native Client} driver.
    StrConnect="Driver={SQL Native Client}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;"
    And that should work properly with VARCHAR(MAX ) datatype.

    Is this drawback has been fixed in {SQL Native client 10.0} ??

    Appreciate your help.
    Thursday, March 5, 2009 6:44 PM

All replies

  • Could you be more specific when you describe your problem with Varchar(MAX) datatype?  I personally use it with {SQL Native Client} quite often and haven't seen the issue you describe.  If you could describe the exact problem you are having, perhaps we can help.  Is it that the length is returned as -1 for the field in OLE DB?  That is the documented length indicator for OLE DB max length types.  People often have problems with it when first working with MAX types.

    Interestingly, SQL Server actually doesn't know about MAX types, the server is sending them back as IMAGE or TEXT because it detects that the client is downlevel.  If you are expecting varchar, varbinary(MAX) to behave like TEXT, IMAGE, this may be the problem you are observing.  If you detail the issues you are observing, we can help to explain what is going on and guide you through the migration.

    Thanks,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, March 5, 2009 9:18 PM
    Moderator
  • Thanks John for your reply.

    Actually, When I use Driver={SQL Native Client}, the field value of the recordset is returned null when the datatype of the field is VARCHAR(MAX). i.e. say I have a database table : MyTable and it has a field called Description whose datatype is VARCHAR(MAX) and another field say ID whose datatype is Integer.

    say my code is as follows:

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open "SELECT Description  FROM  [MyTable]", Conn, 3, 3

    so I get  nothing rs.Fields("Description").Value. I mean null. And for rs.Fields("ID").Value the value is as expected.

    Now I know. I can solve this problem using the following connection string:

    StrConnect="Provider = SQLOLEDB;Data Source = ServerName;Initial Catalog = DBName;User ID = userID;Password = Password ;DataTypeCompatibility=80"

    But now the problem with Provider = SQLOLEDB; is that,  When I use rs.RecordCount, its value is returned as -1. and that is not correct. 
     
    Friday, March 6, 2009 1:40 AM
  • With SQL Native Client, you still have DataTypeCompatability knob set to 80, right?  I just want to make sure we are on the same page.

    Thanks!

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, March 6, 2009 3:20 AM
    Moderator
  • You are correct.
    Friday, March 6, 2009 1:01 PM
  • For better clarity. Here is the example :

    I have SQl server 2005 database table called [Defect]. There are 2 columns ID(Integer) and DefectDescription (VARCHAR(MAX)).

    Below is my code:

    StrConnect="Driver={SQL Native Client}; Server=localhost; Database=DefectDBTrail; Uid=vvuser; Pwd=V&Vgroup1; DataTypeCompatibility=80;"

    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open StrConnect

    set rs = Server.CreateObject("ADODB.Recordset")
    ' Just get 2 records for testing
    query = "SELECT top 2 DefectID, DefectDescription FROM [Defect]"

    rs.Open query,conn,3,3 

    while not rs.EOF

    if IsNull(rs.Fields("DefectDescription").Value) = false then
     Response.Write "<BR>"  &  "DefectDescription = " & Len(rs.Fields("DefectDescription").Value)  & "   "  &  rs.Fields("DefectDescription").Value
    end if

    rs.MoveNext
    wend


    The output
    -------------
    DefectDescription = 1401
    DefectDescription = 225


    Here the noteworthy thing is that, rs.Fields("DefectDescription").Value is not null. Also Len(rs.Fields("DefectDescription").Value) is non-zero value. However, rs.Fields("DefectDescription").Value is empty. It should be Description Field from the database table.

    The expected  output is something like:

    DefectDescription = 1401 <Description Field Value of the record>
    DefectDescription = 225 <Description Field Value of the record>


    I hope this example clarifies your doubt.

    Thanks,
    Mahesh

    Friday, March 6, 2009 2:11 PM
  • Hello Mahesh,
    In order to make sure SQLOLEDB return right recordcount, you must set cursor type as one of the below value.
    Const adOpenKeyset = 1 or
    Const adOpenDynamic = 2 or
    Const adOpenStatic = 3
    , then you will get right recordCount and not using client cursor.


    Please alter your code by adding the below statement before opening recordset.

    rs.CursorType = 1  <<< Set CursorType as adOpenKeyset
    rs.Open query,conn,3,3 


    You are sure to get right recordcount. For detailed information,please refer to http://msdn.microsoft.com/en-us/library/ms676701(VS.85).aspx


    Friday, March 6, 2009 3:09 PM
  • Thanks for your reply.

    Actually, When I use a query say query = "SELECT * FROM [MyTable]" 
    then
    rs.Open query,conn,3,3
    rs.Recordcount is correct

    But if I use "SELECT COUNT(*) AS NumOfRec FROM [MyTable]"
    rs.RecordCount is -1.

    So it looks like when Aggregate functions in SQL Server (AVG, MAX, COUNT etc.) are used in query, the RecordCount returns -1.


    what is the reason?

    Friday, March 6, 2009 4:13 PM
  • Does this reproduce for you if you replace IsNull with IsDBNull?

    Thanks,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, March 6, 2009 6:52 PM
    Moderator
  • when I replaced IsNull with IsDBNull in the above code, I get the following error:

    Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'IsDBNull'


    John, are you able to access VARCHAR(MAX) datatypes using SQL Native Client ODBC driver, i.e. driver = {SQL Native Client} ??

    Friday, March 6, 2009 7:26 PM
  • My repro is a little off of your example because we are working in different base languages.  I am using VB.Net loading ADOX to get SQL Native Client, but it is working (appears to be using OLE DB client).  Here is the code:

    Module Module1

        Sub Main()
            Dim con As New ADODB.Connection
            Dim rst As New ADODB.Recordset

            con.ConnectionString = "Provider=SQLNCLI;" _
               & "Server=(local);" _
               & "Database=master;" _
               & "Integrated Security=SSPI;" _
               & "DataTypeCompatibility=80;"

            con.Open()

            ' Just get 2 records for testing
            rst.Open("select * from testTable", con, 3, 3)

            While Not rst.EOF

                If (IsDBNull(rst.Fields("a2").Value) = False) Then
                    Console.Out.WriteLine("a2     " & Len(rst.Fields("a2").Value) & "   " & rst.Fields("a2").Value)
                Else
                    Console.Out.WriteLine("a2     NULL")
                End If

                rst.MoveNext()

            End While

            con.Close()
            con = Nothing

        End Sub

    End Module


    Not sure if that helps,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, March 6, 2009 7:36 PM
    Moderator
  • If you use Provider=SQLNCLI then definitely it will work with VARCHAR(MAX). That's for sure. I know that. But it does not work with Driver = {SQL Native Client}.

    Now I can use Provider=SQLNCLI, because it's working fine with VARCHAR(MAX). But the problem with Provider=SQLNCLI is that, it does not return correct value of Recordset.RecordCount in cases when Aggregate functions are used in Query, as I have explained in my earlier replies.

    Thanks,
    Mahesh
    Friday, March 6, 2009 8:16 PM
  • You were talking there about SQLOLEDB, earlier.  Is this also happening for SQLNCLI?  SQLNCLI is the OLE DB name for SQL Native Client.  {SQL Native Client} is the ODBC driver specification.  If you are using ADO, you should probably be using the OLEDB name.  In the sample I provided above, rst.RecordCount is returned correctly.

    Thanks,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, March 6, 2009 8:42 PM
    Moderator
  • Hi John,
    Sorry for the confusion.

    I am talking about SQLNCLI (SQL Native Client OLE DB provider). The recordset.RecordCount is returned wrong when you use some aggregate function (MAX, SUM, AVG etc) in query.

    I am not able to understand why this happens and how to solve that problem.

    I have two options:

    1) Use Driver = {SQL Native Client} if I get some solution to deal with VARCHAR(MAX) datatype with this ODBC driver
    2) Use Provider = SQLNCLI  (OLE DB Provide), if I get some solution to get RecordCount value true in all cases (As I have mentioned the value of RecordCount is -1 in case queries having aaggregate functions.)

    So if I get solution for either of the above 2 cases, thats it.

    Thanks for your reply and effort.

    Mahesh

    Friday, March 6, 2009 9:45 PM
  • I'm not exactly sure what the issue is, but I believe it may be related to:

    For ODBC - SIZE UNLIMITED is define as '0'
    For OLEDB - SIZE UNLIMITED is define as '-1'

    In either case, you'll need to deal with these unusual buffer sizes in your own way. Is this relevant to your issue?
    Monday, March 9, 2009 4:02 AM
  • I guess this is not related to my issue.

    My application consists of classic ASP pages. I am using SQL Server 2005 as a back end.

    Now I have 2 options to connect and perform database operations using ADO:

    1) Use SQL Native Client OLEDB Provider. In that case my connection string will look like:

    "Provider=SQLNCLI;Server=ServerName;Database=DBName; Uid=userID; Pwd=Password;DataTypeCompatibility=80;"

    and the second option,

    2) I can use SQL Native Client ODBC Driver. In that case my connection string would be something like :

    "Driver={SQL Native Client}; Server=ServerName; Database=DBName; Uid=UserID; Pwd=Password;DataTypeCompatibility=80;"

    I have problems with both the above options:

    Problem with Option 1 (SQL Native Client OLE DB Provider)
    ---------------------------------------------------------------------------

    It does not return correct value for Recordset.RecordCount especially when there are aggregate functions used in the query. Like "SELECT MAX(ID) FROM someTable Group by SomeField". I can get the correct value of Recordset.Recordcount if I use Client-side cursor. But I do not want to use Client-Side cursor.

    Problem with Option 2 (SQL Native Client ODBC driver)
    ---------------------------------------------------------------------------

    As I have mentioned in my earlier replies that, SQL Native Client ODBC driver is not able to deal with VARCHAR(MAX) datatypes.

    Could someone resolve any of the above 2 problems? And I am done.

    Thanks,
    Mahesh

    Monday, March 9, 2009 5:54 PM
  • In answer to problem #1:
          In ADO, you get -1 recordCount when the cursor is forward only, this is documented ADO behavior.  If you attach SQLProfiler to your target SQL Server, you will see an exception "Could not create an acceptable cursor.".  The server is downgrading the cursor type to forward only, and this is why you are seeing -1 (for the aggregate queries).

          In ODBC, there is an option for cursors "IF_NEEDED" which may be causing the downgraded server cursor to be pushed through client cursors in these particular cases.  I have not yet investigated the ODBC VARCHAR(MAX) issue, but using ADO on top of ODBC is probably not what you would want to do anyway.  It is actually ADO on top of Microsoft OLEDB Provider for ODBC (a generic OLE DB driver) on top of SQL Server Native Client.  If you use the OLE DB side, it is just ADO on top of SQL Server Native Client.

    Here is an interesting thread that seems to include a customer in a similar predicament and some suggested solutions:

    http://visualbasic.ittoolbox.com/groups/technical-functional/visualbasic-l/recordsetrecordcount-returning-1-2048005

    Hope that helps,

    John




    This post is provided 'as is' and confers no express or implied warranties or rights.
    Monday, March 9, 2009 6:23 PM
    Moderator
  • Hi John,

    If SQL Server is downgrading the ADODB Cursor to Forward-Only Cursor, is there any way to prevent this? I mean is there any setting in SQL server or anything by which we can prevent this?

    Because everything is working fine if I use MDAC instead of SQL Native Client to connect to SQL Server 2005. So why SQL Native Client does not support the code that is already working fine with MDAC.

    Thanks,
    Mahesh
    Wednesday, March 11, 2009 12:57 PM
  • Just thought I'd throw in a quick comment that might be related...


    When a 'varchar(max)' column is viewed from a down-level client (pre-SQL Native Client), it apears like a 'text' column. A 'text' column can't have a scrollable cursor, so 'forward only' would be its only option.

    Perhaps your application is unknowingly setting the behavior to act like a pre-SQL Native Client application?

    ~Warren

    Thursday, March 12, 2009 8:23 PM
  • Now I got to know that, when using SQL Native Client, it implicitly converts the CursorType based certain rules. So in my case, eventhough I was requesting different CursorType through application, the returned recordset's CursorType was different. And that id the reason I was not getting expected behavior.

    See this article :http://msdn.microsoft.com/en-us/library/ms190641(SQL.90).aspx

    Thanks,
    Mahesh
    Monday, March 16, 2009 11:05 PM