none
Dealing with text, ntext, image pointer problem from VFP

    Question

  • Hi everybody,

    I had the similar problem before and even documented it in this blog post

    Dealing with the "The text, ntext, or image pointer value conflicts with the column name specified." error from Visual Foxpro

    which came up the first link when I made a Google Search on the problem.

    The situation is slightly different here. We have Items table that has 216 columns. Out of these 216 12 are TEXT columns (deprecated type) and one is varchar(max). 

    We have some big data saved in 2 of the text columns (DataLength is ~400K). When we try to duplicate an existing item, we're getting the error in that blog's title. We're using old SQL Server 2000 ODBC driver to access the data from Visual FoxPro.

    So, I am wondering what is the main factor here for the problem and how exactly should we overcome it. We found that the error doesn't happen if we remove content from that 2 big text files and also if we save that record first without that info in the text files but then apply these big text info back.

    I am wondering if we would have the same error if we change all columns to varchar(max) from text.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 03, 2013 6:15 PM

All replies

  • Hi Naomi, 

    Thank you for your question.  

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

    Thank you for your understanding and support. 

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Friday, July 05, 2013 5:27 PM
  • Thanks Allen. I am just wondering if someone can duplicate this error and what may be the limitations for the size and number of columns. Say, this error happens when we try to duplicate an existing item. It doesn't happen if we duplicate the item without 2 big text columns content and then put that content back in.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 05, 2013 5:30 PM
  • Namoi,

    Can you provide more detail on what you're doing please?

    • Where are you running the T-SQL?
    • What version of SQL Server is this?
    • Is this a linked server?
    • What version of VFP were the data tables created with?
    • Are they free tables or in a DBC?
    • Have your tried the VFP OLE DB Provider (the preferred method of accessing VFP data from 3rd party apps at this point)? If so and you had problems, please detail them (screen shots always help).


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Friday, July 12, 2013 4:19 PM
  • Hi Trevor,

    Nice to have you in this thread. I'll try to answer your questions to the best of my knowledge.

    1. We're using sqlexec and then turn the cursor obtained with sqlexec into updateable remote view using technique similar to described by Vlad Grynchyshyn in this FAQ

    http://www.universalthread.com/ViewPageNewFAQ.aspx?ID=8153

    2. I was able to duplicate this problem with SQL Server 2008 R2, haven't tried with SQL Server 2012 yet.

    3. No, it's not a linked server

    4. We're using the VFP9 SP2 with the latest hotfix

    5. We're not using tables at all, all data is in the SQL Server. But we're using cursors that we get by executing sqlexec and then, as I said above, we turn them into updateable remote views, so we can use replace and update and insert commands and then tableupdate.

    6. We're using DSN to access the data. I believe we can only setup DSN using ODBC and we're using SQL Server driver. See attached screen shot with the pointer on the driver we use (I think).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, July 19, 2013 1:00 AM
  • Naomi,

    When you say you could  "...duplicate this problem with SQL Server 2008 R2..." what do you mean exactly? Were you still using VFP to connect \ run code through the ODBC drive, or were you in TSQL inside SQL Mgmt Studio (no VFP involved)? I'm trying to determine if this happens outside Fox.

    You can create a DSN-less connection using SQLStringConn like this, which uses the 11 native client. I'd try this and get away from the SQL 2000 ODBC drive

    CLEAR
    PUBLIC giConnHand as integer
    giConnHand = SQLSTRINGCONNECT([Driver={SQL Server Native Client 11.0};Server=<SERVER>;Database=<DATABASE>;Trusted_Connection=yes;Integrated Security=SSPI])
    ? giConnHand


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, July 24, 2013 7:56 PM
  • Yes, I meant I am using SQL Server 2008 R2 but I am trying to do this from VFP.

    In other words, I didn't try to simulate this problem manually, I only work with this problem from our application and that application uses certain environment - DSN and made up remote views (SQLExec cursors turned into the views).

    I can potentially try to isolate the problem to exclude VFP, etc., but it would be a lot of work to set it up and right now I have several other complex problems on my plate, so it may take a while.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 24, 2013 8:58 PM
  • I'd suggest changing to the most recent SQL client first, away from the ODBC driver.

    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, July 24, 2013 9:04 PM
  • There are some problems with most recent driver as well as I recall, although I don't recall specifics at the moment - it's either varchar(max) returned incorrectly to VFP or something along the lines.

    Besides, the application is currently using DSN and it would be a major change to switch.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 24, 2013 9:09 PM
  • Are you saying you cannot repro this outside your app's environment, say with a block of code like that in the BLOG post you referenced? You should definitely be trying that in your troubleshooting.

    As for changing code, if you're using SQLCONN() now to utilize the DSN, changing over to test with SQLSTRINGCONN() should be as simple as commenting one line and inserting another.


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, July 24, 2013 9:15 PM
  • I haven't tried yet reproducing it outside the application. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 24, 2013 9:19 PM
  • I think this should be your next step. Please try that a loop back if you need more help.

    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Friday, July 26, 2013 6:03 PM
  • >We have some big data saved in 2 of the text columns (DataLength is ~400K).

    Consider upgrading to SQL Server 2012 and use the FileTable storage:

    BOL: "The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata.

    In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications."

    Link: http://msdn.microsoft.com/en-us/library/ff929144.aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, August 04, 2013 5:33 PM