Post reply

8,000 charachter limit

  • July 26, 2010 at 10:58 am

    #221801

    We use the dynamic query process to extract data from a source (Intersystems Cache) to SQL Server. We are currently using SQL Server 2005 and planning to move to 2008 soon. One process attempts to extract a text field potentially containing well over 8,000 charachters and SQL server blocks the transfer if over 8,000 charachters.

    We have other processes where the SQL code for the dynamic Query is over 8,000 charachters and the dynamic query will not compile.

    I have developed work arounds to limit the data fields and code to less than 8,000 charachters, so it currently works.

    Can anyone tell me if 2008 may address this issue or where I could find out.

    Thanks.

  • July 26, 2010 at 11:23 am

    #1198323

    code .... code .... code please.

    - is this native tsql

    - is this another kind of prog.code that performs the exec dynamic sql

    - are you using SQLNCLI ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 26, 2010 at 11:24 am

    #1198325

    have you tried to use varchar(max) instead of 8000?

  • July 26, 2010 at 11:32 am

    #1198331

    i think there's an issue where if you concatenate a varchar(max) with a varchar(somethingless) it will concat to a varchar(8000)

    i think you have to explicitly use all (max) variables, or convert/cast to (max) to avoid that concat problem, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 26, 2010 at 11:34 am

    #1198332

    8000 characters has never been a limit for code. ie... EXEC (@a+@b+@c.... etc).

    What are you using to import your data with because there's no 8k limit on imports either. I will admit that SSMS doesn't readily display more than 8 k bytes, though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 26, 2010 at 1:31 pm

    #1198415

    Thanks for the replies. The data is in memo (word processing) format on the source and can carry up to 32,727 charachters. I chose one single record that has 8,001 charachters for this example.

    The following is the script I execute (TSQL):

    /* Begin Cache Extract */

    Declare @DateSelect varchar(7)

    Set @DateSelect = Vista.dbo.VistA_Internal_DT(Dateadd(d,-1,Getdate()))

    Declare @Sqltext as varchar(8000)

    --Drop table #MinDate

    --Truncate table Vista1.mde.GMRV_VITAL_MEASUREMENT_120#5

    --

    Set @Sqltext = '

    Select

    IEN,

    ReportText,

    ReportText1

    From OPENQUERY(PGH_CACHE,

    ''Select

    IEN,

    ReportText,

    Cast(left(ReportText,8000) as varchar(8000)) as ReportText1

    From PTHLDR.tiuDocument_8925

    Where IEN =15902742.000'')'

    exec (@Sqltext)

    --Print @Sqltext

    /* End Cache Extract */

    The data extracted in this example is a single row with 8,001 charachters (Attached)

    The result I get is:

    "OLE DB provider "MSDASQL" for linked server "PGH_CACHE" returned message "Requested conversion is not supported.".

    Msg 7341, Level 16, State 2, Line 2

    Cannot get the current row value of column "[MSDASQL].ReportText" from OLE DB provider "MSDASQL" for linked server "PGH_CACHE". "

    ----------------------------------------------------------------------------------------

    When I comment out the line of pulling the full set of data and limit it to the first 8,000 charachters, the process works fine pulling all but the last charachter.

    I am not sure how to demonstrate this with the source being a different source structure and not being able to represent if in SQL, so I apologize for the lengthy and perhaps cryptic note.

    -------

    The above issue is based in the subject DATA being over 8,000 charachters. The second issue noted is based in the dynamic query code being over 8,000 charachters. This is perhaps better dealt with in a different thread, but refers to a situation noted in MS KB Article "http://support.microsoft.com/kb/286370". The specific article relates to SQL Server 2000 but I have it in 2005 as well. There may be something missing in our installation, but my question is to find out if the issue remains in 2008.

    Thanks again.

  • July 26, 2010 at 8:49 pm

    #1198514

    You do know about VARCHAR(MAX) in 2k5 and 2k8, right? Will hold 2 million characters just like the old TEXT datatype in 2k without all the pointer headaches.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 27, 2010 at 7:53 am

    #1198709

    I have tried varchar(max) in the subject query and get the same result.

    The problem is not the actual storage (I have built a process to pull 8,000 charachters at a time (e.g. Left(8000) followed by six strings of substring(ReportText,8001,8000) and so on to get all the data. Then put the pieces together in SQL Server with column definition varchar(max).

  • July 27, 2010 at 1:40 pm

    #1198940

    I know this wasn't your main issue, but it is true that OPENQUERY is still limited to a query string that will fit on one page ... so varchar(8000).

    Now, I'm not positive on this, but I don't believe OPENQUERY will return more than 8K characters (*edit: in any one field) no matter what you do. On the flip side, I am pretty sure you can use OPENROWSET to do it. I haven't personally been in a situation where I've needed to do what you're having to do, but I would at least consider SSIS or a VB/C# app since OPENROWSET is less than ideal and there are the character limits with OPENQUERY.

    Good luck.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply