?
Solved

Limit query output size in sql

Posted on 2003-03-30
9
Medium Priority
?
659 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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