delete all records from a table?

[jgoodman00]

I have written the following stored procedure, which I thought would delete ALL records from the table:


CREATE PROCEDURE [/color] dbo.DeleteAllRecordsFromtblInspection
AS DELETE FROM dbo.tblInspection
GO


This table had 14070 records in it, but it only deleted 10000. The SQL BOL states that unless the WHERE clause is used, DELETE removes all rows from the table. If I run the stored procedure again it removes the remaining records.

Is this therefore something that Access is doing in limiting the number of records which are being deleted?
James Goodman
j.goodman00@btinternet.com

 

[jgoodman00]

To self answer this question, & hopefully help others, my problem was due to the Default Max Records in Tools-Options-Advanced.

This value denotes the default(maximum) number of records returned (& cached) from a SQL Server database... James Goodman
j.goodman00@btinternet.com

 

[chiph]

The query might be timing out. Try increasing the timeout value in your ADO code.

Chip H.

 

[jjgraf]

just use
truncate table
this deletes all records in a table and is faster sense it is not logged

 

[balves]

I believe that you have to be the table owner to use TRUNCATE.

 

[jjgraf]

balves is correct, you have to be DB owner and the rights are not transferable

That is the reason why i use separte database for tables that are deleted and recreated all the time.

 

[KRISF]

Make sure you do not have a 'set rowcount 10000' or jsut issue a 'set rowcount 0' at the beginning of the stored procedure.