6 Replies Latest reply on Mar 24, 2006 3:58 PM by Daniel Behrendt

    SQL query <Long Text> in memo fields

    Jim Gillespie

      I am trying to write departmental reports in MS Access using the OOPR file in SAP in B1.

      I am getting truncated results on some memo / remarks fields. This didn’t make any sense at first because I could open the opportunity and see the results.

      I looked into the raw query and only see part of the fields.

      I wondered if the odbc query was the problem, so I checked the Enterprise manager and went into the database files directly.

      I found the files / fields truncated don’t show in the Enterprise manager, or rather, instead of content, they show as “Long Text”.

      I googled Long Text and found this is a common problem, but I don’t know enough about sql to determine what the fix is.

      Can anyone provide some insight as to how I can access the entire field?

        • 1. SQL query <Long Text> in memo fields
          Kathleen Johnson

          I'm not an Access expert, but I seem to recall there being a need to enable LONGTEXT retrieval or something when accessing MSSQL. I'm pretty sure Access is your problem, I just don't know exactly where to fix it.

          • 2. SQL query <Long Text> in memo fields
            Daniel Behrendt

            In my experience Access likes to change the data types of SQL fields.  What you may want to do is make a base View in SQL to run your queries off of (link to access) rather then going from the table itself and maybe CAST the fields you are using as something different and see if your results in Access change, I have had to do this many times to get what I need.

            • 4. SQL query <Long Text> in memo fields
              Jim Gillespie

              I can do this from Query Analyzer, and I created a view named frf_OOPRmemo which has the following:
              SELECT OpprId, CAST(Memo AS nvarchar(4000)) AS LongText
              FROM dbo.OOPR

              This does provide the data, but when I try to bring the data into Access to match up with the rest of the data (I can bring it in), I see the same truncated version.

              Can you advise how I can cause the view to output the entire content to odbc?
              Hope this makes sense..
              If you'd like more info I can produce spreadhseets of the files.. just email me at jim.gilllespie@fast-rite.com

              • 5. SQL query <Long Text> in memo fields
                Jim Gillespie

                Forgot to mention I need to keep it very simple so my users can run it wthout knowing a thing about views or query analyzer.  I can see the output fine in query analyzer but don't know how to set results to text or max characers or how to output query in the view.

                • 6. SQL query <Long Text> in memo fields
                  Daniel Behrendt

                  How many characters is that field truncating at?  Also what data type does access say that this field is and what version of Access are you using?  I have had a ton of experience bringing our Business One Data into Access so I could format reports that are not simple / possible in SAP due to some extenuating circumstances with our implementation.   So I have run into this type of problem time and time again (although I have never tried to use this field in any of my reporting).

                  Since I assume that we are the only business on the planet that is still running B1 version 6.5 and I assume that you are running 2004 or 2005 what is the data type and length of that field (as specified in Enterprise Manager) in SQL Server? When you link it directly into access what does Access say the field size is?

                  If you would like you can email me directly at dbehrendt@ahx1.com and I can see if there is anything I can help you with, I know I hate spending hours on this stuff if it is not necessary and if it is possible for me to help you I would be more then willing to spend a couple of minuets doing that. I know that next time I have a problem that you may have the answer!