none
sql server varchar(max) data type returns empty data via ODBC Driver 11 from VFP9 SP2

    Question

  • I've seen references to this issue from SQL Server Native Client as well, but no definitive answers. I have found successful return values using a much older version of the ODBC driver, which has date related errors unfortunately. The correct return is also available from the Data Direct ODBC Driver so other than environmental suggestions I am at a loss.

    I find it hard to justify paying the money for a commercial ODBC product for this basic functionality.

    Code to reproduce is as follows:

    * environment
    * windows7, 32 bit
    * foxpro 9 SP2
    * sql server 2008 R2

    LOCAL ARRAY laServer(1), laUser(1), laPass(1)
    LOCAL lcConnectionString, lcSQL
    USE data\prefs && yes, I could have simplified the example by ignoring parameters...

        SELECT uvalue FROM prefs WHERE type = 'FIRM' AND id = 'RTSERVER' INTO ARRAY laServer
        SELECT uvalue FROM prefs WHERE type = 'FIRM' AND id = 'RTUSER' INTO ARRAY laUser
        SELECT uvalue FROM prefs WHERE type = 'FIRM' AND id = 'RTLOGINPW' INTO ARRAY laPass
            
    *        lcConnectionString = "Driver={SQL Server Native Client 10.0};Server="+ALLTRIM(laServer)+
    *        lcConnectionString = "Driver={ODBC Driver 11 for SQL Server};Server="+ALLTRIM(laServer)+
    *        lcConnectionString = "Driver={DataDirect 7.1 SQL Server Wire Protocol};Server="+ALLTRIM(laServer)+
            lcConnectionString = "Driver={ODBC Driver 11 for SQL Server};Server="+ALLTRIM(laServer)+;
            ";Database=QLAW;UId="+ALLTRIM(laUser)+";Pwd="+ALLTRIM(laPass)+";"&&EnableQuotedIdentifiers=Yes"            
                            
        lnHandle = SQLSTRINGCONNECT(lcConnectionString)
        * remote field types are varchar(10), varchar(20), varchar(max)
        lcSQL = "select ourfile, fname, note from dname where OURFILE = '261139'"
        rtnCount =  SQLEXEC(lnHandle, lcSQL, 't' )
        IF rtnCount > 0
            SELECT t
            * returns C, C, M for DataDirect driver; C, C, C for native client & driver 11
            ? TYPE('t.ourfile')+", "+TYPE('t.fname')+", "+TYPE('t.note')
            * displays value, value, memo with Datadirect; value, value, empty with others
            BROWSE normal
        ELSE
            LOCAL ARRAY laerr[10]
            LOCAL lcErrorMessage
            AERROR('laerr')
            lcErrorMessage = "Error in SQL execution => "+laErr(2)+CHR(13)+CHR(10)+" Command: "+lcSQL
            STRTOFILE(lcErrorMessage, 'aSQLError.txt')    && dev debug
        ENDIF

    Thanks for any assistance.

    Doug Hughes

    Tuesday, July 23, 2013 1:44 PM

All replies

  • There is a issue with varchar(max) use varchar(8000), there is known issue around this

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Prajesh Wednesday, July 24, 2013 1:05 PM
    Wednesday, July 24, 2013 8:51 AM
  • While it is possible to use a fixed varchar value this introduces an issue with the import wizard

    Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
     (SQL Server Import and Export Wizard)
     
    Error 0xc004701a: Data Flow Task 1: component "Destination - actlist" (520) failed the pre-execute phase and returned error code 0xC0202025.
     (SQL Server Import and Export Wizard)

    I do have a process that will result in data being migrated through copying data to a temporary table varchar(max) or even text, then populating the varchar(n) fields from that table. But the project includes 62 tables and thousands of fields, a significant effort. The alternative is to use a third party product like Progress DataDirect that just works correctly (at significant cost). Why would just fixing the ODBC driver or offering an environmental solution, if one is available, be so difficult?

    Wednesday, July 24, 2013 1:02 PM
  • After evaluating current production data, it's rare but two of the fields will contain data larger than 8000 bytes.
    Wednesday, July 24, 2013 6:43 PM
  • If that is 8000+ then you can not use char or varchar

    char [ ( n ) ]

    Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

    varchar [ ( n | max ) ]

    Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar arechar varying or character varying.


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 6:52 PM