Stored procedure only insertes 10000 records

  • Thread starter Thread starter Hansjörg Zimmermann
  • Start date Start date
H

Hansjörg Zimmermann

Hi,

i have a stored procedure which transfers records from one table to another.
The first table has about 14000 records.
When i run the procedure only 10000 records are tranfered. I have specified
to transfer 'TOP 100 PERCENT'.
Any ideas, what the reason could be ?

Here is the procedure:
----------------------
INSERT INTO dbo.tFlightMovement
(FlightTypeId, AircraftId, DepAirport,
DepServiceAgent, DesAirport, DesServiceAgent, BlockOff, BlockOn, TakeOff,
LandingTime, NightTime, CustomerId,
NoOfPassengers, FuelUnitUplift, FuelUplift,
FuelUnitTank, FuelSupplier, FuelOff, FuelOn, FreightUnit, Remarks)
SELECT TOP 100 PERCENT dbo.tcdFlightType.FlightTypeId,
dbo.tAircraft.AircraftId, dbo.tAirport.ApId, dbo.vmigServiceAgent.PartnerId,
tAirport_1.ApId AS Expr1,
vmigServiceAgent_1.PartnerId AS Expr2,
dbo.tmigMOYYMM.BlockOffTime2, dbo.tmigMOYYMM.BlockOnTime2,
dbo.tmigMOYYMM.TakeOffTime2,
dbo.tmigMOYYMM.LandingTime2, dbo.tmigMOYYMM.NightTime,
dbo.vmigCustomer.PartnerId AS Expr3, dbo.tmigMOYYMM.NoOfPassengers,
dbo.tcdLiquidUnit.LiquidUnitId,
dbo.tmigMOYYMM.FuelUplift, dbo.tAircrafttype.LiquidUnitId AS Expr4,
dbo.vmigPostingSupplier.PartnerId AS Expr5,
dbo.tmigMOYYMM.FuelOff, dbo.tmigMOYYMM.FuelOn,
dbo.tcdWeightUnit.WeightUnitId, dbo.tmigMOYYMM.Remarks
FROM dbo.tcdFlightType RIGHT OUTER JOIN
dbo.tcdLiquidUnit RIGHT OUTER JOIN
dbo.tcdWeightUnit RIGHT OUTER JOIN
dbo.tAircrafttype RIGHT OUTER JOIN
dbo.tmigMOYYMM INNER JOIN
dbo.tAircraft ON dbo.tmigMOYYMM.AcCallsignFIS =
dbo.tAircraft.AcCallsignFIS LEFT OUTER JOIN
dbo.vmigPostingSupplier ON dbo.tmigMOYYMM.FuelSupplier
= dbo.vmigPostingSupplier.AlphakeyFIS ON
dbo.tAircrafttype.AcTypeId = dbo.tAircraft.AcTypeId ON
dbo.tcdWeightUnit.WeightUnitCd = dbo.tmigMOYYMM.FreightUnit ON
dbo.tcdLiquidUnit.LiquidUnitCd =
dbo.tmigMOYYMM.FuelUnitUplift LEFT OUTER JOIN
dbo.vmigCustomer ON dbo.tmigMOYYMM.Customer =
dbo.vmigCustomer.AlphakeyFIS ON
dbo.tcdFlightType.FlightTypeCd =
dbo.tmigMOYYMM.FlightType LEFT OUTER JOIN
dbo.tAirport tAirport_1 ON dbo.tmigMOYYMM.DesICAOcd =
tAirport_1.ICAOcd LEFT OUTER JOIN
dbo.vmigServiceAgent vmigServiceAgent_1 ON
dbo.tmigMOYYMM.DesHandling = vmigServiceAgent_1.AlphakeyFIS LEFT OUTER JOIN
dbo.tAirport ON dbo.tmigMOYYMM.DepICAOcd =
dbo.tAirport.ICAOcd LEFT OUTER JOIN
dbo.vmigServiceAgent ON dbo.tmigMOYYMM.DepHandling =
dbo.vmigServiceAgent.AlphakeyFIS
 
Hansjörg Zimmermann said:
Hi,

i have a stored procedure which transfers records from one table to another.
The first table has about 14000 records.
When i run the procedure only 10000 records are tranfered. I have specified
to transfer 'TOP 100 PERCENT'.
Any ideas, what the reason could be ?

Here is the procedure:
----------------------
INSERT INTO dbo.tFlightMovement
(FlightTypeId, AircraftId, DepAirport,
DepServiceAgent, DesAirport, DesServiceAgent, BlockOff, BlockOn, TakeOff,
LandingTime, NightTime, CustomerId,
NoOfPassengers, FuelUnitUplift, FuelUplift,
FuelUnitTank, FuelSupplier, FuelOff, FuelOn, FreightUnit, Remarks)
SELECT TOP 100 PERCENT dbo.tcdFlightType.FlightTypeId,
dbo.tAircraft.AircraftId, dbo.tAirport.ApId, dbo.vmigServiceAgent.PartnerId,
tAirport_1.ApId AS Expr1,
vmigServiceAgent_1.PartnerId AS Expr2,
dbo.tmigMOYYMM.BlockOffTime2, dbo.tmigMOYYMM.BlockOnTime2,
dbo.tmigMOYYMM.TakeOffTime2,
dbo.tmigMOYYMM.LandingTime2, dbo.tmigMOYYMM.NightTime,
dbo.vmigCustomer.PartnerId AS Expr3, dbo.tmigMOYYMM.NoOfPassengers,
dbo.tcdLiquidUnit.LiquidUnitId,
dbo.tmigMOYYMM.FuelUplift, dbo.tAircrafttype.LiquidUnitId AS Expr4,
dbo.vmigPostingSupplier.PartnerId AS Expr5,
dbo.tmigMOYYMM.FuelOff, dbo.tmigMOYYMM.FuelOn,
dbo.tcdWeightUnit.WeightUnitId, dbo.tmigMOYYMM.Remarks
FROM dbo.tcdFlightType RIGHT OUTER JOIN
dbo.tcdLiquidUnit RIGHT OUTER JOIN
dbo.tcdWeightUnit RIGHT OUTER JOIN
dbo.tAircrafttype RIGHT OUTER JOIN
dbo.tmigMOYYMM INNER JOIN
dbo.tAircraft ON dbo.tmigMOYYMM.AcCallsignFIS =
dbo.tAircraft.AcCallsignFIS LEFT OUTER JOIN
dbo.vmigPostingSupplier ON dbo.tmigMOYYMM.FuelSupplier
= dbo.vmigPostingSupplier.AlphakeyFIS ON
dbo.tAircrafttype.AcTypeId = dbo.tAircraft.AcTypeId ON
dbo.tcdWeightUnit.WeightUnitCd = dbo.tmigMOYYMM.FreightUnit ON
dbo.tcdLiquidUnit.LiquidUnitCd =
dbo.tmigMOYYMM.FuelUnitUplift LEFT OUTER JOIN
dbo.vmigCustomer ON dbo.tmigMOYYMM.Customer =
dbo.vmigCustomer.AlphakeyFIS ON
dbo.tcdFlightType.FlightTypeCd =
dbo.tmigMOYYMM.FlightType LEFT OUTER JOIN
dbo.tAirport tAirport_1 ON dbo.tmigMOYYMM.DesICAOcd =
tAirport_1.ICAOcd LEFT OUTER JOIN
dbo.vmigServiceAgent vmigServiceAgent_1 ON
dbo.tmigMOYYMM.DesHandling = vmigServiceAgent_1.AlphakeyFIS LEFT OUTER JOIN
dbo.tAirport ON dbo.tmigMOYYMM.DepICAOcd =
dbo.tAirport.ICAOcd LEFT OUTER JOIN
dbo.vmigServiceAgent ON dbo.tmigMOYYMM.DepHandling =
dbo.vmigServiceAgent.AlphakeyFIS
-----------------------

Thanks for your help

Hansjörg Zimmermann
Click to expand...

By default, an ADP will only show you 10,000 records. This applies to any
view: table, form or whatever. So, my guess is that your query is working
OK but Access is only showing you the first 10000 records in the table. To
change this, click the extreme right-hand button in the navigation buttons
at the bottom.
 
Baz said:
By default, an ADP will only show you 10,000 records. This applies to any
view: table, form or whatever. So, my guess is that your query is working
OK but Access is only showing you the first 10000 records in the table. To
change this, click the extreme right-hand button in the navigation buttons
at the bottom.
Click to expand...
I also had the same problem:
I had a delete query which was not doing anything else but delete * from
tablename.
It was deleting only 10000 records. Then I also added in the sql
statement ROWCOUNT 0 and the result was that I just waited long time not
to have any row deleted. I thought it was a timeout issue, then I wrote
a VBA code in the ADP to set the timeout, but then I simply gave up... I
wrote other code to run a dts which does the job...
I also would be curious to understand better this ...
 
Paolo said:
Baz wrote:
Click to expand...
I had a delete query which was not doing anything else but delete * from
tablename.
It was deleting only 10000 records. Then I also added in the sql
statement ROWCOUNT 0 and the result was that I just waited long time not
to have any row deleted. I thought it was a timeout issue, then I wrote
a VBA code in the ADP to set the timeout, but then I simply gave up...
Click to expand...

Well it may be possible that this behavior was a simple timeout issue,
but I guess you tried to increase the timeout to an insanely high value
and nothing changed.
If this is true I rather think that this was an issue with recordlocking
instead. At least one of the records to be deleted was locked by another
user/connection and your delete-query was waiting for the lock to be
released, wich for some reason didn't happen within appropriate time.

You can investigate such issues with the system procedures sp_lock,
sp_who and the object_name()-function.

Cheers
Phil
 
Hansjörg Zimmermann said:
i have a stored procedure which transfers records from one table to another.
The first table has about 14000 records.
When i run the procedure only 10000 records are tranfered. I have specified
to transfer 'TOP 100 PERCENT'.
Any ideas, what the reason could be ?
Click to expand...

Add

SET ROWCOUNT 0

as first line in your stored procedure to diable the rowcount limit.

Access does limit any operation to 10000 rows by default. You can change
this via the menu "Tools"-"Options"-"Advanced"-"Default-max-records". But
nevertheless I recommend to set the rowcount explicitely in every stored
procedure.

Cheers
Phil
 
Philipp said:
Well it may be possible that this behavior was a simple timeout issue,
but I guess you tried to increase the timeout to an insanely high value
and nothing changed.
If this is true I rather think that this was an issue with recordlocking
instead. At least one of the records to be deleted was locked by another
user/connection and your delete-query was waiting for the lock to be
released, wich for some reason didn't happen within appropriate time.

You can investigate such issues with the system procedures sp_lock,
sp_who and the object_name()-function.

Cheers
Phil
Click to expand...

Actually the time out was set to 300 secs, which to me it does not seem
incredibly hi, though it might be...
The lock is not an issue, as I am running on a dev server, on a dev
database..so no table or record is touched by anything/anybody else
except what I am running.

Paolo
 
Actually the time out was set to 300 secs, which to me it does not seem
incredibly hi, though it might be...
Click to expand...

As long as you haven't got any very expensive delete-trigger that
should be quite enough.
The lock is not an issue, as I am running on a dev server, on a dev
database..so no table or record is touched by anything/anybody else
except what I am running.
Click to expand...

Well, that's quite like my dev-environment but nevertheless I ran
into locking issues quite a couple of times. You just might have
some messed up code, wich does not commit a transaction under some
circumstances or you stopped excution of code during debugging or
something like that. S**t happens! Unless you are perfectly
satisfied with your current solution, it may be worth your while to
have another look into that issue.

Cheers
Phil
 
Back
Top