Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PassThrough Query Max Length? (2K)

    We have been using QueryDefs for some time to invoke stored procs to update SQL Server (7.0). We have recently implemented our most ambitious use of this technique and are running into a problem that appears to suggest there is a maximum (string) length for a query def. Does any one have a reference that explicitly confirms a maximum length for a passthrough query?

    Background:
    After a user has finished editing the data presented in an unbound form, an event handler builds a SQL Statement to execute a stored procedure. The statement includes all named parameters and the current values as extracted from the forms controls. The most recently added control is a textbox for Notes, the length of which in the database can be up to 255 characters.

    As this UI is scheduled to be refactored and rebuilt using Dot Net, we have elected to take the simplistic approach of submitting all values as part of the update, regardless of whether they have changed, if any value needs to be updated.

    If I can confirm a max length issue with the query def, I will do a workaround that updates the Notes field separately.

    TIA,

    Steven

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: PassThrough Query Max Length? (2K)

    The maximum length for a query that is stored is approximately 64K characters, so if you are hitting that limit you are doing something really complex. You are also limited in terms of the length of any parameter string that you use to 255 characters, but that shouldn't be an issue for a pass-thru query. I assume you are building the query on the fly (so to speak), and then saving the QueryDef, and executing it. Where do you get the error message? Also, what does the error message say?
    Wendell

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PassThrough Query Max Length? (2K)

    You're right that the limitation is on the length of the parameter string, which would explain why I'm only hitting it with Notes (which is a Text data type on the server. Incidentally, the error message claims that my maximum length is 128. The error message occurs after saving the query and then invoking it.

    I think I'm stuck with having to come up with a strategy for passing in the contents of Notes in 128-character blocks. Any suggestions other than brute force?

    Thanks again!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: PassThrough Query Max Length? (2K)

    It may well be that 2000 has a limit of 128 characters - I looked at the specs for 2002. But if you are storing the QueryDef you should be able to do the max in a grid of 255 characters. On the other hand, looking for a specific text string of 255 characters in a text string is quite inefficient, whether it be in Access or SQL Server. I would be surprised if you couldn't get the correct result without putting in more than a few dozen characters, unless you are storing Notes that are very similar. If that's the case, and you are storing stock notes of some type, you might want to normalize the design and store a numeric value for the note number and simply do a lookup to get the appropriate text string. If I've gone completely off the trolley, post back with more details.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •