Here is a simple SP where I generate 800 new object IDs for my RESOURCES table.
-- Local variables
declare @OBJECTID int -- To handle OBJECTID values generated in a loop
declare @RowCnt int -- Counter for the OBJECTID loop
-- Load the new OBJECTIDs in the temp table IDs
select @RowCnt = 1
while @RowCnt <= 800
begin EXEC dbo.next_rowid 'dbo', 'RESOURCES', @OBJECTID OUTPUT;
Select @RowCnt = @RowCnt + 1
Running it with SQL works fine and always increment the OBJECTIDS with 800 new IDs but running it with arcpy.ArcSDESQLExecute returns random number of new Object IDs. That could be from 200 to 300 new IDs, I wasn’t able to identify a pattern.
However, if I try to generate a limited number of OBJECTID, less than 300 for example, it works fine but I wasn’t able to flag an obvious threshold with arcpy.ArcSDESQLExecute.
I’m stuck there. Why arcpy.ArcSDESQLExecute behave in such an erratic way with that SP ?