?
Solved

Limit query output size in sql

Posted on 2003-03-30
9
Medium Priority
?
674 Views
Last Modified: 2008-03-06
Hi,
How do you fix a limit of data may the user get from MSSQL 2000 in a query?
Thanks,

0
Comment
Question by:flaki
8 Comments
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8233873
The simple answer to your question is

select top N *
from yourtable

where N is the maximum number of rows you wish to return.

If you need something different, please be more specific as to exactly how you want to what to limit.

0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8233932
you can also use
set rowcount @i

where @i is the number of rows.
This will terminate the select when the number of rows is reached.

Same effect as select top but allows a variable.
0
 

Author Comment

by:flaki
ID: 8233999
I want a limit in bytes not in rows.
Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Accepted Solution

by:
JimV_ATL earned 200 total points
ID: 8234087
There's no easy way to do what you wish to do.

if (select sum(len(column1) + len(column2) + len(...)) from yourtable) > 10000
begin
     print 'Output limit exceeded'
return
end
else
begin
     print 'Put your sql statement here'
end

This would accomplish what you wish to accomplish.  Unfortunately, there is no setting to throttle the output on a single query.  



0
 

Author Comment

by:flaki
ID: 8234111
thanks
0
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8255585
I came across this and thought of your situation.  If your goal is to prevent a single query from tieing up the server, then you could use

SET QUERY_GOVERNOR_COST_LIMIT value

where value is the number of "ticks" that that connection is allowed to run a particular query.

If you wanted to set the value on a server basis, then you would use

sp_configure 'QUERY_GOVERNOR_COST_LIMIT', value.

 
0
 

Expert Comment

by:CleanupPing
ID: 9275445
flaki:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183850
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: JimV_ATL http:#8233873

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question