(Again) changes on the ArcGIS data are not reflected on the SQL data,

1115
6
01-26-2013 11:29 AM
by Anonymous User
Not applicable
Original User: Jamal432@gmail.com

(Again) changes on the ArcGIS data are not reflected on the SQL data,


I�??ve posted this thread before but couldn�??t find a satisfactory solution.

All what I need  is to transfer the changes that occur on the arcgis data to the the sql data

I have been advised to do the following:

1. To reconcile and post between all parents and Childs

[ATTACH=CONFIG]21093[/ATTACH], [ATTACH=CONFIG]21094[/ATTACH]

2. To stop all editing and to disconnect all users

[ATTACH=CONFIG]21095[/ATTACH]

3. To apply the script below (attached)

[ATTACH=CONFIG]21096[/ATTACH]

------
# Name: GDBMaintenance.py
# Description: Rebuilds indexes and statistics
# on geodatabase feature classes
# using an existing .sde file.
# Author: Esri
# Import system modules
import arcpy, os
import sys, traceback
  
# set the workspace environment using the data owner account  D:\Practical2_Editing\Data\Sa_A.sde
path=arcpy.GetParameterAsText(0)
arcpy.env.workspace = path
                     ###########################################################

#Gather all the FeatureClasses and Tables from the Workspace
datasets = arcpy.ListTables() + arcpy.ListFeatureClasses()

#Save geodatabase path to use for concatonating feature dataset names
path = arcpy.env.workspace

#Determine if any were found
for fd in arcpy.arcpy.ListDatasets("","Feature"):
    arcpy.env.workspace = path + "\\" + fd
    #Gather the FeatureClasses from the Current Dataset
    datasets += arcpy.ListFeatureClasses()
arcpy.env.workspace = path
#Rebuild and analyze all datasets
arcpy.RebuildIndexes_management(arcpy.env.workspace,"NO_SYSTEM",datasets,"ALL")
print arcpy.GetMessages()

arcpy.AnalyzeDatasets_management(arcpy.env.workspace,"NO_SYSTEM",datasets,"ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")
print arcpy.GetMessages()

#Rebuild and analyze all system tables and compress the Geodatabase
# set the workspace environment using the Geodatabase administrative account
arcpy.env.workspace = path
                      ######################################################

arcpy.RebuildIndexes_management(arcpy.env.workspace,"SYSTEM","","ALL")
print arcpy.GetMessages()
arcpy.AnalyzeDatasets_management(arcpy.env.workspace,"SYSTEM","","ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")
print arcpy.GetMessages()
arcpy.Compress_management(arcpy.env.workspace)
print arcpy.GetMessages()

print "Processing complete"

-----------------------------------------------------
4. Unfortunately, it didn�??t work



What might be the issue?

Thank you

Best

Jamal
0 Kudos
6 Replies
MarcoBoeringa
MVP Regular Contributor
Jamal,

Although the first screenshot is not fully displaying your SQL statement, I think I see a "SELECT TOP" statement being used in SQL Server Management Studio. Why do you use the "TOP" keyword, as it is normally used to restrict the number of records returned in case of very large tables and query results?

This option shouldn't be used if you want to return all records of the feature layer's base table after having run the Python script, you should use a plain "SELECT" statement.
0 Kudos
by Anonymous User
Not applicable
Original User: Jamal432@gmail.com

Jamal,

Although the first screenshot is not fully displaying your SQL statement, I think I see a "SELECT TOP" statement being used in SQL Server Management Studio. Why do you use the "TOP" keyword, as it is normally used to restrict the number of records returned in case of very large tables and query results?

This option shouldn't be used if you want to return all records of the feature layer's base table after having run the Python script, you should use a plain "SELECT" statement.


Many thanks Marco for the answer,

Because in all case, in my particular database the number of records is less than 1000! This is why I aimed at using that option.

Best

Jamal
0 Kudos
MarcoBoeringa
MVP Regular Contributor
Because in all case, in my particular database the number of records is less than 1000! This is why I aimed at using that option.


Still don't get it... If you only have a test database with a couple of records like yours, TOP can be left out. I think there is not much use for this option anyway, unless in very specific cases or running test queries against very large datasets. And your target was to determine if all edits of the database were properly reconciled & posted & compressed to the base table, so you want all records.

And if you entered "SELECT TOP 50 PERCENT" in the query that I can't read from your screenshot, that would definitely explain 20 records of 41 being returned from the feature layers base table even with a successful operation... :rolleyes: But I figure I don't have to explain that to you! 😉

Best,

Marco
0 Kudos
by Anonymous User
Not applicable
Original User: Jamal432@gmail.com

Still don't get it... If you only have a test database with a couple of records like yours, TOP can be left out. I think there is not much use for this option anyway, unless in very specific cases or running test queries against very large datasets. And your target was to determine if all edits of the database were properly reconciled & posted & compressed to the base table, so you want all records.

And if you entered "SELECT TOP 50 PERCENT" in the query that I can't read from your screenshot, that would definitely explain 20 records of 41 being returned from the feature layers base table even with a successful operation... :rolleyes: But I figure I don't have to explain that to you! 😉

Best,

Marco


Many thanks Marco,

�?� What is weird here is that when I run the SCRIPT TOOL (attached) twice then most of the time I get the sql data changed.

[ATTACH=CONFIG]21119[/ATTACH]

�?� How to show all the records of the table in the sql?

[ATTACH=CONFIG]21120[/ATTACH]

Best

Jamal
0 Kudos
MarcoBoeringa
MVP Regular Contributor
Jamal,

You probably have, but since you don't state it, I ask it here explicitly anyway: have you run the same SQL statement in SQL Server Management Studio without the redundant "TOP 1000" keyword text? Did you get the same results (it should, but who knows...)? I think you can simply remove the "TOP 1000" text in the edit / SQL window visible in your screenshot, and than hit "Execute" to do this.

I also think it may be time to have a closer look at all the new tools in the Geodatabase Administration toolset, and see if you can figure out if there are any differences in the reported geodatabase state before and after each run of the script. I know you already looked at some of these options, as it is in your screenshots, but have you checked everything?
0 Kudos
by Anonymous User
Not applicable
Original User: Jamal432@gmail.com

Jamal,

You probably have, but since you don't state it, I ask it here explicitly anyway: have you run the same SQL statement in SQL Server Management Studio without the redundant "TOP 1000" keyword text? Did you get the same results (it should, but who knows...)? I think you can simply remove the "TOP 1000" text in the edit / SQL window visible in your screenshot, and than hit "Execute" to do this.

I also think it may be time to have a closer look at all the new tools in the Geodatabase Administration toolset, and see if you can figure out if there are any differences in the reported geodatabase state before and after each run of the script. I know you already looked at some of these options, as it is in your screenshots, but have you checked everything?



Many thanks Marco,

I don�??t think that the issue is how to show the table. Anyway, in the screenshot below, the number of records in the ArcGIS is different from those on the SQL despite the fact that:

1. Reconcile/post is made between Parent/Childs versions

2. The script tool is applied to Rebuild Indexes, Analyze Datasets and Compress after all the users are disconnected

3. The SQL is showing the entire table (refreshed, restarted, etc.)

[ATTACH=CONFIG]21349[/ATTACH]

What other issues might be required to have these two tables identical?

Best

Jamal
0 Kudos