Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unhappy Unanswered: Changing the command buffer size?

    Is it possible to change the command buffer size??

    I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer....

    Thanks!!!

  2. #2
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22

    Re: Changing the command buffer size?

    Originally posted by rmcat
    Is it possible to change the command buffer size??

    I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer....

    Thanks!!!
    Are you sure it's your buffer that is causing the problem?
    An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    Re: Changing the command buffer size?

    Originally posted by kbk
    Are you sure it's your buffer that is causing the problem?
    An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.
    When I run the stored proc with exec output turned on, I get the following error...
    "Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What do you put for the "query hints"? INDEX=xxxxx ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're playing with dynamic sql...right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    5
    Originally posted by rdjabarov
    What do you put for the "query hints"? INDEX=xxxxx ?
    Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...

    bcp "select....." queryout <filename> <sql user id> <sql password> <server> <file format>

    The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger....

  7. #7
    Join Date
    Feb 2004
    Posts
    5
    Originally posted by Brett Kaiser
    You're playing with dynamic sql...right?
    Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Convert your SELECT into a stored procedure, and you're home free!

  10. #10
    Join Date
    Feb 2004
    Posts
    5
    Originally posted by rdjabarov
    Convert your SELECT into a stored procedure, and you're home free!
    Success!!!!

    You guys rock!

    Thank you!!!

Posting Permissions

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