Latest Contributions by SherrieKubis

‎10-17-2017

Oracle 12.1 SDE 10.4.2 and MS SQL 2016 SP1 SDE 10.5.0 As we migrate from Oracle to SQL Server I see that MS SQL takes more storage, but I don't see a pattern. In one geodatabase Oracle consumed 10GB, SQL Server 15GB. In our largest geodatabase, Oracle is consuming about 100GB, Sql Server is almost at 200GB and the data is less than half migrated. That would make it about 5x more in SQL Server. What am I missing? I'm not as well versed in SQL Server, but it seems pretty straightforward. In either Oracle or SQL Server we don't change DBTUNE and take the defaults. Any insights or suggestions are appreciated. Sherrie

‎09-15-2017

Looking for clarification and advise on this ESRI documentation: Database clients—Help | ArcGIS Desktop It talks about ArcMap or ArcGIS Engine being 32-bit applications and requires a 32-bit client when using clients other than SQL Server. Then, it looks like if you use SQL Server client on 64-bit machine, it will use that, and if you try and use 32-bit, it will not install. So will the ArcMap application, that is 32-bit, work connecting to SQL Server on a 64-bit machine? I might be reading too much into this, but want to be prepared. Sherrie 32- vs 64-bit clients Some ArcGIS clients, such as ArcMap and ArcGIS Engine, are 32-bit applications. If you connect from one of these clients, you must use a 32-bit Informix, ALTIBASE, SAP HANA, Netezza, Oracle, Teradata 14.x, or Dameng database management system client to connect to the database. This is true even if you install the ArcGIS client application on a computer with a 64-bit operating system and the database and server operating system are 64 bit. To connect from 64-bit ArcGIS clients (such as ArcGIS Server or ArcGIS Pro), you must install 64-bit database management system clients. When installing the SQL Server client, use the 64-bit executable to install on a 64-bit operating system; the executable detects the type of operating system and installs the correct database management system client. If you run the 32-bit executable on a 64-bit operating system, it will fail.

‎09-15-2017

Yes, thank you for the post. We are planning to upgrade to Oracle 12.2 and 10.5.1 from Oracle 12.1 and 10.4.1 soon, and I was hoping for no problems Sherrie

‎09-15-2017

Hi Mark. I'm curious to see how this works in the long term. I'm coming from Oracle, where updating the objectid is easier because the next_rowid is a function. We've been using SDE there for a very long time, and from experience, my biggest rule is to never mess with SDE from SQL, always use SDE to do the work. There are so many inter-dependencies in the GDB and metadata tables that you may think you're okay, and then all of a sudden realize an issue is cropping up. I used to try and go around SDE for performance and ease of use, but vowed to abandon that idea after we had to recover the database back to a point-in-time before I touched it. I don't have enough experience with SDE and MS SQL yet to comment, but I'm interested in what others have to say. Sherrie

‎09-15-2017

Thanks, Alex, I'll keep this handy. I've been pulled away from this issue and haven't been able to get back to it, but I'd like to try it. I'll update when I get back to that point.

‎07-24-2017

Randy, Thank you so much. I was missing the distinction between an error and a warning, once I got that concept and then added some code around this, it's what I need. I appreciate the help. Sherrie

‎07-24-2017

Thanks Randy. That indeed does work. Most of the examples that I see, especially in ArcGIS Help, uses a try block. I was assuming that if an exception was received inside of the try:, it would cascade into the Except:, but it doesn't. The error checking has to be done inside of the try and handled there, which kind of makes Try and Except not what I want to use. In my example below, the exception returns on the first statement in the try block, but in the except block, it returns success. So why use try and except, perhaps not what I need in this case. I'll rework to use conditional statements instead. What does the "#" represent? def DeleteNonspatialTables(inTable): print ('TableName ', inTable) FullTable = gdb + sys.argv[1] print ('Full Table ', FullTable) try: arcpy.Delete_management(FullTable,"#") for i in range(0,arcpy.GetMessageCount()): print arcpy.GetMessage(i) except arcpy.ExecuteError: for i in range(0,arcpy.GetMessageCount()): print arcpy.GetMessage(i) if __name__ == '__main__': DeleteNonspatialTables(sys.argv[1])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

‎07-24-2017

Sorry about the indentation representation. I couldn't find the Code Formatting syntax highlighter to post this, I'll look into it. I'm using PyScripter for coding, it checks indentation as well. Going to try some other things and will update.

‎07-21-2017

Simple code to delete a registered table. By mistake I passed a table name that doesn't exist, but this condition must not be considered an exception because I get the print statement about it being deleted. import arcpy, sys # Connection definitions DatabaseConnectionFolder = r'C:/DatabaseConnections/sde105/' sdecreator_sdeoltp = r'sdecreator_sdeoltp.sde/SDECREATOR.' gdb = DatabaseConnectionFolder+sdecreator_sdeoltp arcpy.env.workspace = DatabaseConnectionFolder+gdb def main(): print ('TableName ', sys.argv[1] ) inTable = gdb + sys.argv[1] try: arcpy.Delete_management(inTable) print inTable + ' deleted.' except: print 'Table not deleted ' + inTable if __name__ == '__main__': main() However, if I put an if statement to see if the table exists, it works. import arcpy, sys # Connection definitions DatabaseConnectionFolder = r'C:/DatabaseConnections/sde105/' sdecreator_sdeoltp = r'sdecreator_sdeoltp.sde/SDECREATOR.' gdb = DatabaseConnectionFolder+sdecreator_sdeoltp arcpy.env.workspace = DatabaseConnectionFolder+gdb def main(): print ('TableName ', sys.argv[1] ) inTable = gdb + sys.argv[1] if arcpy.Exists(inTable): arcpy.Delete_management(inTable) print inTable + ' deleted.' else: print 'Table does not exist: ' + inTable if __name__ == '__main__': main() What I'd really like to do is try the Delete_management, and if it is unsuccessful for any reason, trap the reason and be able to see that. I'm learning python and arcpy, I'm sure this must be something obvious that i'm missing. Does anyone have an example? Sherrie

‎06-27-2017

No, it is not versioned. I've simplified it to taking a non-registered table and inserting into a registered table with an objectid. Same problem, but trying to make it clearer. Goal: Populate a target table of 4 columns. 3 columns come from another table, 1 column is derived from a (third-party) stored procedure. For performance reasons I want to use set processing, not cursors or iterative processing. S‌ource Table: county_source ‌ county varchar(3) ‌ countyname varchar(12) ‌‌ T‌arget Table: county_target ‌ county va‌rchar(3) ‌ countyname varchar(12) ‌ objectid int T‌he objectid for each row comes from a stored procedure, like so: EXEC sde.next_rowid 'OWNER', 'COUNTY_TARGET', @objectid OUTPUT;‌ I need to insert into county_target with an objectid, where one of the columns come from the stored procedure. The insert will be a select from county_source. ------------------------------ The code that I am using is wrong as the stored procedure doesn't fire. DECLARE @objectid as INT declare @sql as varchar(100) declare @owner as varchar(10) = 'SDECREATOR' declare @table as varchar(25) = 'County_Target' set @sql = 'exec sde.next_rowid, @owner, @table, ' + convert(varchar(5),@objectid) + 'OUTPUT'; INSERT INTO [SDECREATOR].[County_Target] (OBJECTID, COUNTY, COUNTY_NAME) SELECT @sql, COUNTY, COUNTY_NAME FROM [SDECREATOR].[County_Source];

‎06-14-2017

Joshua, You example worked great for the first row. For example, the sde.next_rowid returns as 30279. The first row entered has an object id of 30279. The insert statement has 33,000 rows to add, but this doesn't get the next row id, it uses objectid. So it would have to be in a loop, as shown in this article: How To: Insert geometry from XY coordinates using SQL But I don't want a loop, I want set processing as opposed to iterative. For example, in Oracle the statement INSERT INTO MYTABLE (OBJECTID, SITE_ID) SELECT SDE.GDB_UTIL.NEXT(ROWID ('OWNER','MYTABLE'), SITE_ID FROM MYVIEW; Inserts into MYTABLE the uniqueid from next_rowid for all 33,000 rows.

‎06-14-2017

Hi Joshua, thanks for the answer. I've been out of the office and am now getting back to this, I'll give this a try and let you know. This is the 10.5 documentation that I was referring to, but your example may help for sure.

‎05-30-2017

SQL Server 2016 ArcGIS 10.5 I want to insert into a registered table using set processing using sde.next_rowid. I do this in Oracle as: INSERT INTO MYTABLE (OBJECTID, SITE_ID) SELECT SDE.GDB_UTIL.NEXT(ROWID ('OWNER','MYTABLE'), SITE_ID FROM MYVIEW; In SQL Server ESRI documentation examples I see selecting the objectid, writing down the number, and then hard coding it into the insert statement. This will be a nightly job that will insert thousands of rows, so set processing is required. I try INSERT INTO MYTABLE SELECT SDE.NEXT_ROWID ('OWNER','MYTABLE'), SITE_ID FROM OWNER.MYVIEW; and get Cannot find either column "SDE" or the user-defined function or aggregate "SDE.NEXT_ROWID", or the name is ambiguous. I can use SDE.NEXT_ROWID to query like so: DECLARE @id as integer EXEC sde.next_rowid 'OWNER', 'MYTABLE', @id OUTPUT; SELECT @id "Next ObjectID"; and this works just fine. I'm new to SQL Server, I'm sure it's a syntax issue. Any insights are appreciated.

‎12-02-2016

Thanks Vince. Old habits die hard! We've been using 'SDE' since 3.2 and I still refer to it as that, but have been trying to use the correct terminology. This is good info, we have a lot test and learn. We have a MS SQL presence here, but for 3rd party applications. The thought from those guys is that cross-database, or even cross-instance queries are eazy-peazy. I was a concerned about cross-instance queries for performance reasons, but it's clear we have a lot to learn. You're right, our old environment has all of our geodatabases and tabular data for relationships in the same Oracle instance, so every though ownership was different, joins were possible. However, the user-schema GDB that was versioned was isolated enough that we could version and compress there, but the other user-schemas GDBs were all treated different. In SQL Server my consideration is if I were to migrate these UGDBs over, would they each be a database? If there were different schemas in a database I think that versioning isolation would be an issue. But if cross-database joins aren't a good idea, that would definitely be an issue. I want to get the architecture and possibilities straight in my head, but it's looking like we've got to just install it and play around, and see what we can do. Your insights are appreciated. Sherrie

‎11-30-2016

I need to update that we aren't planning on trying SDE 10.5 as I'm hearing that there are 'changes'.

Online Status	Offline
Date Last Visited	‎11-11-2020 02:24 AM

My Ideas

Latest Contributions by SherrieKubis

Storage consumed in Oracle and SQL Server for geodatabase

32-bit and 64-bit SQL Server clients for ArcGIS

Re: SDE 10.5.1 Permissions Issue

Re: Using sde.next_rowid in T-SQL

Re: Using sde.next_rowid in T-SQL

Re: arcpy delete_management error trapping when table de

Re: arcpy delete_management error trapping when table de

Re: arcpy delete_management error trapping when table de

arcpy delete_management error trapping when table de

Re: Using sde.next_rowid in T-SQL

Re: Using sde.next_rowid in T-SQL

Re: Using sde.next_rowid in T-SQL

Using sde.next_rowid in T-SQL

Re: SDE with SQL Server

Re: SDE with SQL Server

Re: Storage consumed in Oracle and SQL Server for ...

Re: arcpy delete_management error trapping when ta...