stored precedure does not return all rows

[Mark]

I am using a stored predecure from an Access project file to create data for a MSSQL table. But the problem is that it only return the first 10,000 records for me. Here is the code, how can I get it to return all the records?

Alter Procedure stoChange

/*

This stored precedure is for produce a table for growing stock changes such as growth, mortality and removal

*/

As



TRUNCATE TABLE Change

INSERT Change

SELECT TOP 100 PERCENT


Growth, Mortality, Removal,

STATUSCD, TREECLCD, DIA,

COUNTYCD, PLOT, TREE, CONDID,

COUNTY,

Region,

LANDCLCD, RESERVCD, SITECLCD,

oLANDCLCD, oRESERVCD, oSITECLCD,

SPCD, SPGRPCD,

/* ownership information from the new condition table.

OWNCD,

OWNGRPCD,

FORINDCD,

ownership information from the old condition table.

oOWNCD,

oOWNGRPCD,

oFORINDCD,

*/

/* the following three cases combined ownership information from new and old condition tables,

if a land has been cut and turned into non-forest land, current ownership information will not be available,

the ownership information in the old condition table will be used.

*/

CASE

WHEN [OWNCD] =-1 THEN [oOWNCD] ELSE OWNCD END AS nOWNCD,

CASE

WHEN [OWNGRPCD] =-1 THEN [oOWNGRPCD] ELSE OWNGRPCD END AS nOWNGRPCD,

CASE

WHEN [FORINDCD] =-1 THEN [oFORINDCD] ELSE FORINDCD END AS nFORINDCD,

CASE

WHEN [SPGRPCD] <=24 THEN 'SW' ELSE 'HW' END AS MJSPGRP,

CASE

WHEN [DIA]<9 And [DIA]>=5 THEN 'Pulpwood'

WHEN [DIA]<11 And [DIA]<=9 THEN 'ChipnSaw'

WHEN [DIA]>=11 THEN 'Sawlog'

ELSE 'Saplings' END AS WoodType,

CASE

WHEN [FORTYPCD]<400 THEN 'Pine'

WHEN [FORTYPCD]<500 THEN 'Mixed'

ELSE 'Hwd' END AS ForestType

FROM vChange

/* set nocount on */

return

 

[Eva Etxebeste]

Hello Mark

If you open Tools->Options->Advanced, you see the maximun number of records your adp will return to you. When you open a table exceding 10,000 records, or execute a stored procedure, your adp will only return 10,000 records. You can change this value, can set it to 0 to enforce to return all records, or can use "SET NOCOUNT ON" as the first instruction in your stored proc, wich will return all records to you.

Regards
--
Eva Etxebeste [MS MVP Access]

"Mark" <[email protected]> escribi¨® en el mensaje I am using a stored predecure from an Access project file to create data for a MSSQL table. But the problem is that it only return the first 10,000 records for me. Here is the code, how can I get it to return all the records?

Alter Procedure stoChange

/*

This stored precedure is for produce a table for growing stock changes such as growth, mortality and removal

*/

As



TRUNCATE TABLE Change

INSERT Change

SELECT TOP 100 PERCENT


Growth, Mortality, Removal,

STATUSCD, TREECLCD, DIA,

COUNTYCD, PLOT, TREE, CONDID,

COUNTY,

Region,

LANDCLCD, RESERVCD, SITECLCD,

oLANDCLCD, oRESERVCD, oSITECLCD,

SPCD, SPGRPCD,

/* ownership information from the new condition table.

OWNCD,

OWNGRPCD,

FORINDCD,

ownership information from the old condition table.

oOWNCD,

oOWNGRPCD,

oFORINDCD,

*/

/* the following three cases combined ownership information from new and old condition tables,

if a land has been cut and turned into non-forest land, current ownership information will not be available,

the ownership information in the old condition table will be used.

*/

CASE

WHEN [OWNCD] =-1 THEN [oOWNCD] ELSE OWNCD END AS nOWNCD,

CASE

WHEN [OWNGRPCD] =-1 THEN [oOWNGRPCD] ELSE OWNGRPCD END AS nOWNGRPCD,

CASE

WHEN [FORINDCD] =-1 THEN [oFORINDCD] ELSE FORINDCD END AS nFORINDCD,

CASE

WHEN [SPGRPCD] <=24 THEN 'SW' ELSE 'HW' END AS MJSPGRP,

CASE

WHEN [DIA]<9 And [DIA]>=5 THEN 'Pulpwood'

WHEN [DIA]<11 And [DIA]<=9 THEN 'ChipnSaw'

WHEN [DIA]>=11 THEN 'Sawlog'

ELSE 'Saplings' END AS WoodType,

CASE

WHEN [FORTYPCD]<400 THEN 'Pine'

WHEN [FORTYPCD]<500 THEN 'Mixed'

ELSE 'Hwd' END AS ForestType

FROM vChange

/* set nocount on */

return

 

[Uwe Ricken]

Hi Mark,

the problem does not come from the sql-server but from within access.
Set the options in access to return more than 10.000 records
(default).

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
APP: http://www.AccessProfiPool.de
dbdev: http://www.dbdev.org
FAQ: http://www.donkarl.com/AccessFAQ.htm

 

[Mark]

The set to "0" in Access did the trick but the "SET NOCOUNT ON" in the stored proc didn't.

Thanks a lot.
"Eva Etxebeste" <eetxebesteARROBAhotmail.com> wrote in message Hello Mark

If you open Tools->Options->Advanced, you see the maximun number of records your adp will return to you. When you open a table exceding 10,000 records, or execute a stored procedure, your adp will only return 10,000 records. You can change this value, can set it to 0 to enforce to return all records, or can use "SET NOCOUNT ON" as the first instruction in your stored proc, wich will return all records to you.

Regards
--
Eva Etxebeste [MS MVP Access]

"Mark" <[email protected]> escribi¨® en el mensaje I am using a stored predecure from an Access project file to create data for a MSSQL table. But the problem is that it only return the first 10,000 records for me. Here is the code, how can I get it to return all the records?

Alter Procedure stoChange

/*

This stored precedure is for produce a table for growing stock changes such as growth, mortality and removal

*/

As



TRUNCATE TABLE Change

INSERT Change

SELECT TOP 100 PERCENT


Growth, Mortality, Removal,

STATUSCD, TREECLCD, DIA,

COUNTYCD, PLOT, TREE, CONDID,

COUNTY,

Region,

LANDCLCD, RESERVCD, SITECLCD,

oLANDCLCD, oRESERVCD, oSITECLCD,

SPCD, SPGRPCD,

/* ownership information from the new condition table.

OWNCD,

OWNGRPCD,

FORINDCD,

ownership information from the old condition table.

oOWNCD,

oOWNGRPCD,

oFORINDCD,

*/

/* the following three cases combined ownership information from new and old condition tables,

if a land has been cut and turned into non-forest land, current ownership information will not be available,

the ownership information in the old condition table will be used.

*/

CASE

WHEN [OWNCD] =-1 THEN [oOWNCD] ELSE OWNCD END AS nOWNCD,

CASE

WHEN [OWNGRPCD] =-1 THEN [oOWNGRPCD] ELSE OWNGRPCD END AS nOWNGRPCD,

CASE

WHEN [FORINDCD] =-1 THEN [oFORINDCD] ELSE FORINDCD END AS nFORINDCD,

CASE

WHEN [SPGRPCD] <=24 THEN 'SW' ELSE 'HW' END AS MJSPGRP,

CASE

WHEN [DIA]<9 And [DIA]>=5 THEN 'Pulpwood'

WHEN [DIA]<11 And [DIA]<=9 THEN 'ChipnSaw'

WHEN [DIA]>=11 THEN 'Sawlog'

ELSE 'Saplings' END AS WoodType,

CASE

WHEN [FORTYPCD]<400 THEN 'Pine'

WHEN [FORTYPCD]<500 THEN 'Mixed'

ELSE 'Hwd' END AS ForestType

FROM vChange

/* set nocount on */

return