4 Replies Latest reply on Apr 30, 2012 1:39 PM by Dano1970

    Export data out of SDE in consistent order each time

    Dano1970
      Hello All,

      My problem is that I need a unique, maintained attribute field to perform SQL on later in my workflow in order to maintain database linkages in a computer aided dispatching system between rebuilds.

      Is there a way to have the records, being exported from an SDE using Feature Class to Feature Class, populate the newly created feature class in the same order each time?
      In other words, when exporting data, from the SDE, I would like FID 1 to be the same record each time I export the same feature class. Currently, each time I export data FID 1 ends up being a different record.

      Thanks,
      Dan Sereno
      GIS DBA
        • Re: Export data out of SDE in consistent order each time
          vangelo-esristaff
          The only way to be certain of a consistent order is to apply an ORDER BY on the query.

          It's probably not particularly safe to rely on the objectid for this purpose.

          - V
          • Re: Export data out of SDE in consistent order each time
            Dano1970
            Thank you for the reply Vangelo.
            However, unless I am doing something wrong, placing "order by OBJECTID" into the "Expression" portion of the Feature Class to Feature Class tool produces the message: Invalid SQL syntx[Microsoft SQL Native Client: Deferred prepare could not be completed.]

            Thank you,
            Dan Sereno
            GIS DBA
            • Re: Export data out of SDE in consistent order each time
              vangelo-esristaff
              The order_by clause is a separate component in the ArcSDE SE_QUERY_INFO object used
              to query the stream. I haven't looked to see if it's available from ArcObjects, but it's unlikely
              to be available though the where clause in that tool.  You might be able to use an ORDER BY
              in a subquery, but there's still no guarantee that a query (especially a spatial query) would
              honor the subquery order.  It is very likely that the resulting query would be much slower.

              Even if the export order is reliable, there's still no guarantee that the objectid insert order
              will  always be increasing (generally, it is, but it's not requred), so you could still have subtle
              errors in alignment.  I suggest you use a column other than the registered rowid column for
              this purpose.  You'll most probably need to code your own export tool to achieve this goal.

              - V
              • Re: Export data out of SDE in consistent order each time
                Dano1970
                Vangelo,
                I have no write permissions to the SDE from which I am looking to extract data.
                So, unless there is some type of "order", on which any of the many tools that can extract a data set from an SDE operate, it seems I have no way of even writing my own tool to extract records in the same order each time from an SDE.

                Thanks,
                Dan Sereno
                GIS DBA