Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>

Restoring Transaction Logs

I have a customer that needs to restore a database to
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack

restoring sql 2000 backup on sql 7

Hi,

I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?

The database only contains tables and views...

Thanks
Koen

You may have to move the tables and Views and you cannot enable relationship there is none in 7.0 and in Belgium if the 7.0 is not using unicode you cannot use Unicode because to Unicode enable you must reinstall. There are many differences with those versions it is hard to tell you in one post. The best thing to do ask the customer for the personal edition which is free to them so you can develop directly to 7.0 Hope this helps.