egdbSQL Extension - Geodatabase Version 11

[PeterStiegler2]

Hello,

we currently have a problem with the EGDBSQL addon.
We have upgraded to ArcGIS Enterprise 11 and then the EGDBSQL addon does not work anymore.

The reason for this:
When upgrading the geodatabase to version 11, the database_name column no longer exists.
Until Geodatabase version 10.9.1 this column still existed.

But the EGDBSQL tool needs the database_name field. Is there any way to run the tool without using this field?

Thank you

Peter

[DannyKrouk]

Hi Peter - I am sorry for the problem.  I've not had the opportunity to test the extension with an eGDB at the 11 release.  I will do that soon and report back.  In the meantime, all of the SQL exists in plain-text form in the installation directory.  You could edit it.  Obviously, that's no fun.  So, I'll look into this as soon as it is practical and report back. - Danny

[DannyKrouk]

I have checked the queries other than those in the Performance category.  I see only one that fails in this circumstance.  It is in the file \Status\Versioning\DeltaRecords.txt.  If you replace the content in that file with this content, it should work:

SELECT
sum(p.rows) as delta_counts
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
inner join
(
select 'A' + cast(registration_id as nvarchar) deltatable , owner
from sde_table_registry
union all
select 'D' + cast(registration_id as nvarchar) deltatable , owner
from sde_table_registry
) s
on s.owner = OBJECT_SCHEMA_NAME(t.object_id) and s.deltatable = t.name

However, I would also note that the extension should have worked even with this query not functioning.  That is, this one query might fail.  And, if it does, you can regenerate the config file to exclude the failing query.  Then, the extension should work.  I'll update the extension with this query (it should execute on earlier releases just fine).  But, perhaps, if you are having a problem that is not solved by this, you can share more information about what you see on the extension's command line when you run the TEST of each kind of collection?

[bernhard]

Hi Danny,

Thank you for your answer but I'm afraid you are only partly correct.

Yes, there is the sql statement called DeltaRecords which uses the term database_name and which is editable.

However there is another sql statement (to be more specific a subselect of it) which uses the term database_name and which is not editable because it isn’t listed in any .txt in the EgdbSQL directories and is only shown in the log file.

This is the sql statement: (row 98, 117 and 119 contain database_name)

TraceLog Information: 4 : 1/11/2023 10:39:03 AM: SQL: select
top_mem.objectOwner owner_name
, top_mem.objectName table_name
, rec_count.btab_record_count total_btab_records
, top_mem.pageCount current_page_count_in_mem
--, top_mem.Buffer_MB current_buffers_MB
--, st_lyrs.layer_id
--, st_lyrs.registration_id
--, st_lyrs.spatial_column
, st_lyrs.geometry_storage_type
, st_lyrs.isVersioned
--, st_lyrs.rowid_column
--, st_lyrs.minx
--, st_lyrs.miny
--, st_lyrs.maxx
--, st_lyrs.maxy
--, st_lyrs.falsex
--, st_lyrs.falsey
--, st_lyrs.xyunits
--, st_lyrs.gsize1
----, st_lyrs.combined_g2_g3
--, st_lyrs.srid
--, st_lyrs.wkid
from
(
SELECT top 20
-- top 20 featureclass base table by in-memory page count of its indices (including clustered, which is the table)
case au.TYPE
when 1 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
when 2 then OBJECT_SCHEMA_NAME(p2.OBJECT_ID,db_id())
when 3 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
end objectOwner,
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END objectName,
count(*) pageCount,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
FROM
sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
LEFT JOIN sys.indexes i
on i.object_id = CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END
AND i.index_id = CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END
WHERE
d.name = db_name()
and
-- owner.table_name
case au.TYPE
when 1 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
when 2 then OBJECT_SCHEMA_NAME(p2.OBJECT_ID,db_id())
when 3 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
end + '.' + CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END
in
-- sde owner.table_name
( select owner + '.' + table_name from sde_table_registry where table_name not like 'GDB_%')
group by
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END ,
case au.TYPE
when 1 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
when 2 then OBJECT_SCHEMA_NAME(p2.OBJECT_ID,db_id())
when 3 then OBJECT_SCHEMA_NAME(p1.OBJECT_ID,db_id())
end
order by pageCount desc
) top_mem
inner join
(
select
l.minx, l.miny, l.maxx, l.maxy, l.srid
, l.database_name, spatial_column, l.owner, l.table_name
, l.layer_id
, case
when eflags & power(2,27) <> 0
then 'SQL_GEOMETRY'
when eflags & power(2,24) <> 0
then 'SDE_BINARY'
else 'OTHER'
end geometry_storage_type
, i.Definition.value('(/DEFeatureClassInfo/Versioned)[1]','bit') isVersioned
, i.Definition.value('(/DEFeatureClassInfo/SpatialReference/WKID)[1]','int') wkid
, tr.registration_id
, tr.rowid_column
, sr.falsex
, sr.falsey
, sr.xyunits
, l.gsize1
, l.gsize2 + l.gsize3 combined_g2_g3
from sde_layers l inner join gdb_items i
on i.PhysicalName = l.database_name + '.' + l.owner + '.' + l.table_name
inner join SDE_table_registry tr
on l.database_name = tr.database_name and l.owner = tr.owner and l.table_name = tr.table_name
inner join GDB_ITEMTYPES it
on i.type = it.UUID
left outer join SDE_spatial_references sr
on l.srid = sr.srid
where it.name = 'Feature Class'
) st_lyrs
on top_mem.objectOwner = st_lyrs.owner and top_mem.objectName = st_lyrs.table_name
inner join
(
SELECT
s.owner,
s.table_name,
sum(p.rows) as btab_record_count
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
inner join
sde_table_registry s
on s.owner = OBJECT_SCHEMA_NAME(t.object_id) and s.table_name = t.name
group by s.owner, s.table_name
) rec_count
on top_mem.objectOwner = rec_count.owner and top_mem.objectName = rec_count.table_name
where
st_lyrs.combined_g2_g3 = 0
order by
top_mem.pageCount desc

 The error message reads:

TraceLog Warning: 400 : 1/11/2023 10:39:03 AM: NOT ABLE TO GENERATE QUERIES FROM MEMORY. ERROR: Invalid column name 'database_name'.
Invalid column name 'database_name'.
Invalid column name 'database_name'.
Invalid column name 'database_name'.
TraceLog Warning: 400 : 1/11/2023 10:39:03 AM: ERROR ON LINE: 1669
TraceLog Warning: 400 : 1/11/2023 10:39:03 AM: STACK TRACE: Invalid column name 'database_name'.
Invalid column name 'database_name'.
Invalid column name 'database_name'.
Invalid column name 'database_name'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at sqlfetch.sqlfetch.FetchToDataTable(String sql) in C:\Projects\Danny\Tools\vs\SM3Extension\sqlfetch\sqlfetch.cs:line 1263
   at sm3egdb.ssPerfQueryGenerator.generateFromMemoryODBC(sqlfetch sf, String sql, String subdir) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\ssPerfQueryGenerator.cs:line 718
   at sm3egdb.ssPerfQueryGenerator.GenerateQueries(String directory, String connString, ConnectionType connType, Int32 connectionHash) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\ssPerfQueryGenerator.cs:line 1669

So if you could make this sql statement available (any maybe the rest hidden somewhere in the source code too)  in the directories as well in the next version then we can correct it by ourself 🙂 

Regards, 
Bernhard 

PS: I attached the complete log for further details. 

[DannyKrouk]

Yes, a good catch.  

This SQL statement is issued when one *generates* performance queries.  So, all *existing* queries (including performance) should work, except the one that I mentioned here (DeltaRecords).  I believe that you came across this problem by generating new queries (i.e. this was not a matter of continuing to work after an eGDB upgrade but starting to work on a new eGDB or re-doing one's work on an eGDB that had been upgraded).  Please let me know if I have misperceived what you are describing.

I may not have stated, but I was working on a fix for the queries involved in the generate process.  They are not exposed in text files and therefore not addressable outside of a new build of the extension.  

In any event, I have just recently completed that work and made the new binaries available for download: https://arcgismonitor.maps.arcgis.com/home/item.html?id=a5a58e38feb1439981594be38ba10063.  Please give them a try and let me know if there are any remaining issues.  Thanks for your patience and your feedback!

[bernhard]

Hi,

I'm afraid it is still not working and yes, I am using the newest version. It is the same error (the not changeeable sql statement with the wrong column name) as you see in the attached log. 

You are correct that this error happens, when new queries are generated. But this in the inital set up phase. We do not have this extension installed or configured yet. I was planing to do so but the error prevents that...

Regards,

Bernhard

[DannyKrouk]

Hi Bernhard,

I am sorry for the continued problems.  And, I am sorry for misperceiving your original email context.  I had thought it was related to the other user's inquiry, which was an upgrade situation.

I have tried to reproduce the problem.  I have not been able to do so this time (whereas I was able to before).

In re-downloading the extension (https://arcgismonitor.maps.arcgis.com/home/item.html?id=a5a58e38feb1439981594be38ba10063) to confirm that the new build is there, I found a component that was not up to date there.  So, I updated that component just now.  But, when executing with that older component, the symptom was different to what you report.

I recognize the SQL statement in the log as the statement from before the fix.  So, this is very troubling.  Obviously, this SQL statement can only come from my tool.  However, I am not able to reproduce in my development environment or in the component downloaded from the link above.

I believe you when you say that you are running the latest from that link above.  However, in this case, I cannot explain the difference in your log file and mine.  At the current release (and for quite some time), the log file begins like this:

TraceLog Information: 0 : ### APPLICATION INITIALIZED ###
TraceLog Information: 100 : 2/21/2023 9:54:54 AM: EXTENSION RELEASE INFO: EGDBHEALTH RELEASE: 1.7.8445.21458 BUILD DATE: 2/14/2023 11:55:16 AM
TraceLog Information: 100 : 2/21/2023 9:54:54 AM: #> OS RELEASE INFO:
TraceLog Information: 100 : 2/21/2023 9:54:54 AM:
OS Version: 6.2.9200.0
OS Platform: Win32NT
OS Service Pack:
OS Version String: Microsoft Windows NT 6.2.9200.0
Major Version: 6
Major Revision: 0
Minor Version: 2
Minor Revision: 0
Build: 9200
Current Culture: en-US (English (United States))
CLR Environment: 4.0.30319.42000
TraceLog Information: 100 : 2/21/2023 9:54:54 AM: #> EMBEDDED COMPONENT INFO:
TraceLog Information: 100 : 2/21/2023 9:54:54 AM: egdb: CLR runtime: v4.0.30319
egdb:Referenced assemblies:
egdb: Name=mscorlib, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89
egdb: Name=trcLogger, Version=1.0.0.0, Culture=, PublicKey token=
egdb: Name=sqlfetch, Version=1.3.0.0, Culture=, PublicKey token=
egdb: Name=System.Data, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89
egdb: Name=System.Windows.Forms, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89
egdb: Name=System, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89
egdb: Name=System.Xml.Linq, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89
egdb: Name=System.Drawing, Version=4.0.0.0, Culture=, PublicKey token=B0-3F-5F-7F-11-D5-0A-3A
egdb: Name=fileEncrypter, Version=1.0.0.0, Culture=, PublicKey token=
egdb: Name=System.Core, Version=4.0.0.0, Culture=, PublicKey token=B7-7A-5C-56-19-34-E0-89

...

The log file that you provided here (that you for that) begins like this:

TraceLog Information: 0 : ### APPLICATION INITIALIZED ###
TraceLog Information: 100 : 2/20/2023 8:24:33 AM: EXPEDITED CONFIGURATION BEGUN; FORCING LOGGING ON
TraceLog Information: 100 : 2/20/2023 8:24:33 AM: About to create configuration file: SQL_GIS_UPDM2.xml ...
...

Is it possible that the log file is an older one (not the one from your current run of the current tool)?

Thanks,

Danny

[DannyKrouk]

Separately, Bernhard, as this is not an upgrade situation, you may wish to consider the new release of ArcGIS Monitor (believe it is called 2023.0) ... if you have not already. 

That release includes this extension's functionality natively.  So, that makes things much, much easier.  You don't have deploy and configure this extension at all with the latest ArcGIS Monitor.

Danny

[bernhard]

Hi Danny, 

we already switch to ArcGIS Monitor 2023, thank you. 

Nevertheless I hate to leave bug/technical questions unanswered or unsolved if I can help it... 

I downloaded the version from the link you posted. This zip has the egdb.exe with a modifcation date from 2/14/23, the sqlfetch.pdb with 2/10/23, and deltarecords.txt with 2/10/23 as well. I hope that is correct.

This version resolves the issue with the wrong table name apprently as I do not get the discussed error message again. Yeah!!
Unfortunatly I now reports a different kind of error. Something about a invaild length of data to decrypt when running the performance queries.

Any ideas abut that? 

TraceLog Information: 4 : 2/27/2023 12:32:01 PM: ABOUT TO EXECUTE: AppgisGEOCP_KKS_INTENSIVMESSWERT
TraceLog Error: 900 : 2/27/2023 12:32:01 PM: ERROR: Length of the data to decrypt is invalid.
TraceLog Error: 900 : 2/27/2023 12:32:01 PM: STACK TRACE: at System.Security.Cryptography.RijndaelManagedTransform.TransformFinalBlock(Byte[] inputBuffer, Int32 inputOffset, Int32 inputCount)
at System.Security.Cryptography.CryptoStream.FlushFinalBlock()
at System.Security.Cryptography.CryptoStream.Dispose(Boolean disposing)
at System.IO.Stream.Close()
at fileEncrypter.encdenc.DecryptFile(String sourceFilename, String destinationFilename, String password, Byte[] salt, Int32 iterations) in C:\Projects\Danny\Tools\vs\SM3Extension\fileEncrypter\encdenc.cs:line 75
at sm3egdb.gdbQueryRunner.ReadTextFile(String fileName) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\gdbQueryRunner.cs:line 101
at sm3egdb.gdbQueryRunner.DoWork(trcLogger lgr, String dbtype, String directory, String subdir, String connString, Boolean test, String[]& excludedQueries, Int32 calculateOverSeconds, Int32 connectionHash, ConnectionType connType, Boolean reThrowErrors) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\gdbQueryRunner.cs:line 596
at sm3egdb.Program.expeditedConfiguration() in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\Program.cs:line 286
at sm3egdb.Program.Main(String[] args) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\Program.cs:line 501
C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\gdbQueryRunner.cs:line 101
at sm3egdb.gdbQueryRunner.DoWork(trcLogger lgr, String dbtype, String directory, String subdir, String connString, Boolean test, String[]& excludedQueries, Int32 calculateOverSeconds, Int32 connectionHash, ConnectionType connType, Boolean reThrowErrors) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\gdbQueryRunner.cs:line 596
at sm3egdb.Program.expeditedConfiguration() in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\Program.cs:line 286
at sm3egdb.Program.Main(String[] args) in C:\Projects\Danny\Tools\vs\SM3Extension\sm3egdb\Program.cs:line 501

Regarding the look of the log file, I don't have an explanation why my logs different then yours. The log from this version looks like the previous ones and starts as follows..

TraceLog Information: 0 : ### APPLICATION INITIALIZED ###
TraceLog Information: 100 : 2/27/2023 12:31:52 PM: EXPEDITED CONFIGURATION BEGUN; FORCING LOGGING ON
TraceLog Information: 100 : 2/27/2023 12:31:52 PM: About to create configuration file: SQL_UPDM.xml ...
TraceLog Information: 4 : 2/27/2023 12:31:52 PM: Creating query runner ...
TraceLog Information: 4 : 2/27/2023 12:31:52 PM: About to process config file for Activity test ...
TraceLog Information: 4 : 2/27/2023 12:31:52 PM: About to run Activity test ...
TraceLog Information: 4 : 2/27/2023 12:31:52 PM: -- CONNECTING --

Log file is attached. 

Regards,
Bernhard