Post reply

Query Truncated

  • September 2, 2007 at 11:36 pm

    #120370

    Hi all,

    I have a long dynamic SQL query to execute into SQL2000 server. I will hit the unclosed quotation mark error whenever I tried to run it. The query is 16083 characters long when I see it in the profiler.

    It ends with '308å

    I believe there may still be more behind the query. This value is within a IN() clause.

    Any help will be greatly appreciated.

    Thanks.

    Chee Hwee, Lim

  • September 3, 2007 at 12:38 am

    #730964

    Hi Lim,

     

    I'm pretty sure that when you are using dynamic SQL you are restricted to a varchar variable of 4000 chars.

     

    Try breaking you query up into smaller parts and executing as follows.

     

    EXEC @SQL1 + @SQL2 + @SQL3

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • September 3, 2007 at 12:47 am

    #730965

    Hi Christopher,

    I don't think that was the case. I can control the number of values in the dynamic sql string. Once I reduce the parameter in the IN clause by 1, my SQL query goes through. The query is still 16106 characters long.

    Currently I m not able to do any changes to the query code. I can only troubleshoot and hope it has something to do with configuration.

    Thanks for any suggestions.

    Chee Hwee, Lim.

  • September 3, 2007 at 1:48 am

    #730972

    Hi again 🙂

     

    Would it be possible for you to post the code?

     

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • September 3, 2007 at 1:58 am

    #730973

    The IN clause is 16000 characters?

    May I ask if you also have some performance problems?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • September 3, 2007 at 2:11 am

    #730975

    Yes, performance is also not ideal. However, thats not a concern at this point of time yet. This query is not executed frequently.

    I wish I had the code that construct this query as well... But I do not have the luxury of accessing it. I have to trust that the code is doing its job until I exhaust whatever avenues I could from the DB point of view. The only tools that I can use is whatever that is in MSSQL2000. I'm hoping that it is a MSSQL configuration that I missed out somewhere.

    On a side note, the query analyzer was able to do a query larger than what I state above. But I suppose that is expected.

    Sorry for the constraint.

    Regards,

    Chee Hwee, Lim

Viewing 6 posts - 1 through 6 (of 6 total)

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