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!!!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.|||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)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||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...|||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.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!

Success!!!!

You guys rock!

Thank you!!!