topic Re: Limit number of observations returned from SQL Server connection in Administration and Deployment

Limit number of observations returned from SQL Server connection

Jamie — Thu, 27 Nov 2014 11:22:10 GMT

Morning all,

Is there a way to limit the number of observations a user can return from a SQL Server table? Perhaps a setting SMC or an option in one of the config files?

We are running SAS 9.2 on linux.

Thanks in advance

Jamie

Re: Limit number of observations returned from SQL Server connection

JuanS_OCS — Thu, 27 Nov 2014 12:01:37 GMT

Maybe this article could be of your interest:

36112 - Managing large SAS® data sets that exceed the maximum number of observations

I don't know if your users are making use of Enterprise Guide or SAS Base. My first guess would go to the first option.

In Enterprise Guide (I am not sure in 9.2 right now) in Tool-Options-Data-Performance, you should be able to set the number of rows to display.

In any code, you can also make use of the OBS paramenter: SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition and

Use the OBS and FIRSTOBS Options to Save Time While Testing - sasCommunity

If there is any specific for SQL Server, sorry, I don;t know. But if any, you should be able to include in the library definition with: library-properties-Options-Advanced Options- Other options.

Re: Limit number of observations returned from SQL Server connection

Jamie — Thu, 27 Nov 2014 12:07:13 GMT

Hi There,

Thanks for your reply. What i'm looking to do is restrict the users, so if someone had to code, say:

data test;

set mylib.data(obs=500000);

run;

then have some sort of configuration or setting to over-ride that to 10,000 for example.

Thanks,

Jamie

Re: Limit number of observations returned from SQL Server connection

Ksharp — Thu, 27 Nov 2014 12:21:13 GMT

Not sure if this could work. Try these two ways.(read 10 records one time)

libname x sqlsrv ......... readbuff=10;

proc sql inobs=10;

Xia Keshan

Re: Limit number of observations returned from SQL Server connection

Kurt_Bremser — Thu, 27 Nov 2014 12:45:23 GMT

Since the OBS= option(s) can be set anywhere in the SAS program, they are no valid tool for reliably keeping users from overloading your SQL server with big requests (I assume this is what you want).

Check if it is possible to set such a limit for certain users in the SQL server. For SAS I don't see one.