Update Stored Procedure Only Updates First 10000 Rows Of Table

[DomDom3]

Hi there,

I'm working with an adp front end to an SQL Server backend. I have a simple update procedure:-

UPDATE dbo.tblRawOSP
SET date_created = { fn NOW() }

But only the first 10000 records of the table update, I can't find a way of updating the rest.

Am I missing something really simple?

Thanks

 

[jose74]

Hi,
I think this will be an MS Access issue. As I remember Access by default only shows the first 10000 records of a large table. So I can imagine that it has a problem with the update-statement.
Try to set another value in default settings or use a separat connection in VBA to send the statement.

 

[AlexCuse]

If that is the problem, you could also create a SQL Stored Procedure or pass-through query that can be called from VBA and have the server do the work for you

Good Luck,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[SQLBill]

If you create a stored procedure it would be something like:

CREATE PROCEDURE dbo.usp_myproc
 AS
UPDATE dbo.tblRawOSP
SET date_created = GETDATE()

Then you would have your front end run:
EXEC dbo.usp_myproc

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

BTW- I am assuming that {fn NOW()} inputs the current date and time. That is what SQL Server's GETDATE() will provide.

-SQLBill

Posting advice: FAQ481-4875