Why can't I calculate a date field in my SQL-hosted GDB using ArcGIS Pro 1.4?

2272
7
Jump to solution
03-22-2017 03:54 PM
ScottMcGee3
New Contributor II

Here's the problem I'm having:

  • I'm using SQL Server 2012 that hosts a geodatabase.
  • ArcCatalog 10.5 reports that the SQL-hosted GDB is at version 10.4.0.
  • In ArcGIS Pro 1.4, I am using the following Python calc expression to update a field named MTP_Date: datetime.datetime(2016, 3, 15)
  • In ArcGIS Pro 1.4, the above calc expression works just fine when run on a non-SQL-hosted file geodatabase.
  • In ArcGIS Pro 1.4, the Calculate Field tool does not successfully update the date field in one of the feature classes in my SQL-hosted GDB. The strange thing is, the calc tool runs successfully with no errors, but the date field is not actually updated. It makes no change to the value in the date field.
  • In ArcGIS Pro 1.4, I can successfully edit a date field by manually typing the date in the feature class' attribute table. But my work is such that manually entering date values is not practical. I need to be able to calculate the field values.
  • Using ArcMap 10.5, the Calculate Field tool does successfully update the value for the date field in the SQL-hosted 10.4.0 GDB.

So, using the same exact SQL-hosted 10.4.0 GDB:

  • ArcGIS Pro 1.4 cannot successfully calc a date field value
  • ArcMap 10.5 can successfully calc a date field value

I'm wondering if I need to upgrade the SQL-hosted GDB to version 10.5 in order for ArcGIS Pro 1.4 to be able to successfully calculate date field values. Any ideas?

0 Kudos
1 Solution

Accepted Solutions
ScottMcGee3
New Contributor II

I finally figured out the solution.

On this page: Calculate Field—Data Management toolbox | ArcGIS Desktop 

It states:

"Be aware that this tool honors the Output Extent environment setting. Only those features within the extent will have their field values calculated. The environment setting has no effect on nonspatial data such as tables."

So, in the Geoprocessing > Calculate Field pane, I clicked Environments and set the Extent equal to the feature class that I wanted to calculate field values. That solved the problem. Newly-calculated field values then were shown in the attribute table.

View solution in original post

7 Replies
JayantaPoddar
MVP Esteemed Contributor

What happens if you run DateSerial(2016,3,15) or DateSerial(16,3,15)?

Date fields—ArcGIS Pro | ArcGIS Desktop 

ArcGIS Pro follows the Windows settings for date formats.


Think Location
ScottMcGee3
New Contributor II

DateSerial doesn't work. I get a message saying "module datetime has no attribute dateserial"

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Is the data versioned or not?  Does it happen with all data types in Pro or just dates?  Also, do you see the same with other feature classes or just this one?

0 Kudos
ScottMcGee3
New Contributor II

"Is the data versioned or not?"

Yes, it is versioned.

"Does it happen with all data types in Pro or just dates?"

Yes, it happens with all data types in all of our SQL-hosted geodatabases.and feature classes.

I think I've narrowed down the problem to being specific to ArcGIS Pro 1.4 and our version 10.4.0 SQL-hosted geodatabases. The problem does not occur when using Pro 1.4 to edit file geodatabases or shapefiles. So I suspect I need to upgrade our SQL-hosted GDBs from version 10.4.0 to version 10.5.

Just to give a bit of additional info, I did some testing and found that when using Pro 1.4 to calculate field values in any of our SQL-hosted feature classes, that the calculation did actually insert the new value into the field. However, the attribute table still showed the old value, as if the attribute table was not being refreshed. The only way to see the newly-calculated field value was to close the ArcGIS Pro project and open it again. Then the calculated value was shown in the attribute table.

ScottMcGee3
New Contributor II

This is a follow up on my previous post above.

I upgraded our SQL Server-hosted geodatabase to ArcGIS 10.5. So now everything we have is at version 10.5 (ArcGIS Desktop, ArcGIS Enterprise, SQL-hosted GDB). We're also using ArcGIS Pro 1.4.

Here's a recap of the problem I'm having:

1) I started a brand new, empty Pro 1.4 project. In that project, I added a database connection to our SQL-hosted GDB and added a feature class (PLSS Sections) to the map.

2) Using ArcCatalog, I exported the PLSS Sections from the SQL-hosted GDB into a new, empty file geodatabase. I did this so I could test using the same feature class (PLSS Sections) from two different sources (SQL-hosted GDB and file GDB).

3) I added the PLSS Sections from the file GDB into the map.

4) Calculating the date field in the file GDB PLSS Sections feature class worked fine. I could see the newly-calculated value in the date field of the attribute table.

5) Calculating the date field in the SQL-hosted GDB PLSS Sections feature class sort of worked.The calc operation completed without any errors, but the date field in the attribute table did not show the newly-calculated value. However, when I saved the project, closed it, and opened it up again, the newly-calculated value was shown in the date field of the attribute table.

6) The problem described in #5 above occurs with all fields of all feature classes of all of our SQL-hosted GDBs.

So...

1) Pro 1.4 and a file GDB: calculating a field value works as it should. No problems.

2) Pro 1.4 and a SQL-hosted GDB: calculating a field value works, but the only way to confirm that it worked is to close the ArcGIS Pro project and then re-open it. Upon re-opening the project, I can see newly-calculated field values.

I took a wild guess that the problem might be due to using DirectX vs. OpenGL. So I tried both settings in Pro 1.4. It didn't make any difference. I also downloaded and installed the latest driver for my graphics card. That made no difference either.

Obviously, it's not reasonable to close my Pro 1.4 project(s) and re-open them every time I calculate a field value in our SQL-hosted GDBs. So this pretty much makes Pro 1.4 unusable for me (even though I'm really trying to like it!).

Has anyone else experienced this problem?

0 Kudos
JayantaPoddar
MVP Esteemed Contributor

We can only make guesses here. It is better to raise a ticket with Esri. Let them have a closer look at the issue.



Think Location
0 Kudos
ScottMcGee3
New Contributor II

I finally figured out the solution.

On this page: Calculate Field—Data Management toolbox | ArcGIS Desktop 

It states:

"Be aware that this tool honors the Output Extent environment setting. Only those features within the extent will have their field values calculated. The environment setting has no effect on nonspatial data such as tables."

So, in the Geoprocessing > Calculate Field pane, I clicked Environments and set the Extent equal to the feature class that I wanted to calculate field values. That solved the problem. Newly-calculated field values then were shown in the attribute table.