Increase buffer size programmatically in SQL 2000 using T-SQL

Question

Increase buffer size programmatically in SQL 2000 using T-SQL

msforumpost

Right there with Babe

Points: 795
More actions
September 4, 2010 at 9:28 am

#144956

Hi All,
I have around 6000 lines of serveral sql query lines that needs to get executed in one query analyser window. I do in 3 sets of 2000 lines each in one set. Now when I paste my first 2000 lines of query and execute it , when I try to delete all the lines from the query analyser window , it gives me the message
"This operation will delete more than 1000 lines. You will not be able to undo it. Are you sure you want to do this?"
Then I click ok on the dialog box and it allows me to delete. So everytime I try to delete more than 1000 lines from the query analyser window , it gives me this message.
I know that , in order to avoid this message, I have to go to Tools --> Options --> click on Editor tab and increase the number 1000 which is dispalyed against the checkbox MAXIMUM UNDO BUFFER SIZE (In Lines) . --> But I want to this operation programmatically via T-SQL code, something like
SET OPTIONS == ??
Thanks

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

It can be done via a registry setting and, yes, that CAN be done via undocumented T-SQL sprocs but let's ask a different question, instead.... why not turn your 6000 lines of code into a proper stored procedure and be done with it?

--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)

msforumpost Right there with Babe Points: 795 More actions · Answer 2

Thanks for the reply . Those are actually 6000 lines of Insert into table statement to insert data... Can u please tell me the name of the undocument stored procedure which increase buffer size.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

It can be done via a registry setting and, yes, that CAN be done via undocumented T-SQL sprocs

Jeff,

Can you quote the how this can be done.. just curious to know ..:-)

msforumpost (9/4/2010)
Hi All,
I have around 6000 lines of serveral sql query lines that needs to get executed in one query analyser window. I do in 3 sets of 2000 lines each in one set. Now when I paste my first 2000 lines of query and execute it , when I try to delete all the lines from the query analyser window , it gives me the message
"This operation will delete more than 1000 lines. You will not be able to undo it. Are you sure you want to do this?"
Then I click ok on the dialog box and it allows me to delete. So everytime I try to delete more than 1000 lines from the query analyser window , it gives me this message.
I know that , in order to avoid this message, I have to go to Tools --> Options --> click on Editor tab and increase the number 1000 which is dispalyed against the checkbox MAXIMUM UNDO BUFFER SIZE (In Lines) . --> But I want to this operation programmatically via T-SQL code, something like
SET OPTIONS == ??
Thanks

For such things, use command line SQLCMD utility .. store your query as .sql file and pass that with -i[inputfileName.sql] -o[outfileName.txt].

HTH,

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

msforumpost (9/5/2010)
Thanks for the reply . Those are actually 6000 lines of Insert into table statement to insert data... Can u please tell me the name of the undocument stored procedure which increase buffer size.

I'd rather help you fix your code so you don't need to.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

SQL_Quest-825434 (9/6/2010)
Jeff,
Can you quote the how this can be done.. just curious to know ..:-)

There's an undocumented stored procedure that you can use to write to the registry for the current user. In this particular case, I'd rather help with the 6000 lines of code to do an import so I'll hold off on that. Right now, I'm sorry I even brought it up to tell the truth. :blush:

--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)