topic Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL in Geodatabase Questions

Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

MeganJohnson — Thu, 29 Jun 2017 14:02:16 GMT

Non spatial table created in SQL to be updated automatically daily. Table will be involved in a join so it is registered with the geodatabase in ArcCatalog, creating an OID in field called ObjectID. When the program is run in SQL to delete the table and recreate it using the same name, 0 records appear in ArcGIS. But it appears to be populated in SQL.

So my question is: If a table is registered with the geodatabase and then deleted in SQL and a new table is created with the same name (essentially overwriting the existing table), does the new one need to be re-registered with the geodatabase? Why else would the table appear to be empty when viewing in ArcMap?

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

SarojThapa1 — Thu, 29 Jun 2017 16:18:53 GMT

Megan,

The reason why the data is being shown on sql but not on geodatabase is: The geodatabase honors objectid as a unique, not null integer field to uniquely identify rows in tables. However, SQL does not, even though a field very identical to OBJECTID is created.

Below is the caution provided by esri.

Caution:

Since most ArcGIS for Desktop functionality requires that the ObjectID be unique, you must be sure that ObjectID values are not duplicated when working directly with the database outside of ArcGIS. For example, when creating views with a one-to-many relationship, there is the possibility that ObjectIDs will be duplicated. This causes inconsistent behavior in ArcGIS for Desktop functionality.

One way to fix the issue is: by creating a GlobalID and treating it as a unique, not null integer.

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

MeganJohnson — Thu, 29 Jun 2017 17:23:07 GMT

Thank you for your reply. To clarify, the OBJECTID is being created within ArcCatalog when I am registering the table with the geodatabase. However, when the nightly program runs in SQL, the table is deleted and then recreated using the same name. I think that when it is recreated the ObjectID is not getting regenerated within SQL.

How can I create a unique ObjectID in SQL?

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

SarojThapa1 — Thu, 29 Jun 2017 17:44:16 GMT

Make the OBJECTID field uniqueidentifier, NOT NULL to create a unique ObjectID in SQL.

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

MeganJohnson — Thu, 29 Jun 2017 19:22:10 GMT

Will that set the field "ObjectID" as data type Object ID when viewing the table properties in ArcCatalog?

Also, would the table still need to be registered with the geodatabase if it already has an objectID?

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

SarojThapa1 — Thu, 29 Jun 2017 19:32:14 GMT

Check this one out.

SQL and enterprise geodatabases—Help | ArcGIS Desktop

Good luck!

Re: Table created in SQL, registered with geodatabase shows no records in ArcGIS but is populated in SQL

JoshuaBixby — Mon, 03 Jul 2017 15:54:34 GMT

As you have already found out, your workflow as currently implemented won't work. If the updates were not very common, you could try truncating the table (instead of deleting it) and using a cursor to repopulate it, but I don't think that will work in this case since you are doing frequent updates.

Is the data completely changing in 24 hours? What percentage of the table is actually getting updates in a day? I ask because the general approach you are trying is inefficient if a small percentage of the records get updated in a day. If there are a large number of updates in a day, then I recommend deleting the table (using ArcGIS because the SDE system tables will get cleaned up) and then re-register the table after it is created again.