topic Re: Oracle Database to SQL Server in Geodatabase Questions

Oracle Database to SQL Server

RaymondLi — Thu, 20 May 2021 14:02:49 GMT

Hi I am new to Geodatabase.

We have a database in Oracle for Geodatabase. We need to migrate it to the MS SQL Server. There is one column as data type "ST_GEOMETRY" in Oracle table. What should the data type in MS SQL Server to match it?

Re: Oracle Database to SQL Server

George_Thompson — Thu, 20 May 2021 14:30:20 GMT

How are you migrating the table from the Oracle Enterprise Geodatabase to SQL Server?

Are they at the same version?

That ST_Geometry field is specific to Oracle Geodatabases. What is the geometry that you are using in the SQL Geodatabase?

Is the data versioned?

To be honest, the best way to migrate (safest way) is to copy / paste from Oracle --> SQL Server with ArcGIS Pro client.

Re: Oracle Database to SQL Server

RaymondLi — Thu, 20 May 2021 14:59:41 GMT

Hi George,

Thank you for your reply.

I don't know the version of the Geodatabase, how can I check it.

I know that the Oracle Geodatabase is using ArcMap 10.4 to create. The MS SQL Geodatabase is using ArcGIS Pro 2.8 to create.

The problem for me is that our ArcGIS Pro has issue to connect to our Oracle database, that why we need to switch to MS SQL server.

Re: Oracle Database to SQL Server

George_Thompson — Thu, 20 May 2021 15:09:15 GMT

What does the sde.version table say in Oracle?

Using non-ArcGIS clients / tools to migrate from Oracle --> SQL Server may not work as expected.

What is the error connecting to the Oracle GDB from Pro?

Re: Oracle Database to SQL Server

RaymondLi — Thu, 20 May 2021 15:21:52 GMT

Hi George,

The sde.version in Oracle is "10.6.1 Geodatabase". It is a infrastructure issue that the location of ArcGIS Pro computer cannot access the Oracle database.

Re: Oracle Database to SQL Server

George_Thompson — Thu, 20 May 2021 15:29:07 GMT

Ok, thanks for the update. Are you migrating the records via some sort of Oracle --> SQL Server migration tool?

Re: Oracle Database to SQL Server

RaymondLi — Thu, 20 May 2021 16:21:10 GMT

George,

We export the data into excel format or csv file. We plan to import the file directly to MS SQL Server.

Re: Oracle Database to SQL Server

George_Thompson — Thu, 20 May 2021 17:03:21 GMT

Is there a way to connect to the Oracle GDB via another ArcGIS client and export to a file geodatabase. Then move over and use Pro to load into the SQL Server geodatabase.

The shape conversion process may not work via CSV transfer method.

Re: Oracle Database to SQL Server

RaymondLi — Thu, 20 May 2021 17:36:58 GMT

George,

Thank you for your advise. I am able to export the table to shapefile. Then how can I import to the SQL server?

Re: Oracle Database to SQL Server

George_Thompson — Thu, 20 May 2021 18:14:54 GMT

Once you have the shapefile from Oracle, you can import via the Feature Class to Geodatabase GP Tool.

That will get everything in your SQL Server Geodatabase in the best possible shape. If you had domains / subtypes they would need to be created and configured again

Re: Oracle Database to SQL Server

RaymondLi — Fri, 21 May 2021 00:07:15 GMT

George,

I exported the file into shapefile, then I imported the shapefile into the MS SQL Server via the ArcGIS Pro. However, I have the Shape column in Oracle as T_GEMERTY column, the value in the MS SQL is become something that I don't recognize like "0xE61000......", and additional column call GDB_GEOMATTR_DATA.

Re: Oracle Database to SQL Server

George_Thompson — Fri, 21 May 2021 16:32:16 GMT

Yes, that is correct. In SQL Server the geometry is stored in a different format than ST_Geometry in Oracle.

Can you please send me a DM so that we can connect outside this thread?

Re: Oracle Database to SQL Server

VinceAngelo — Fri, 04 Jun 2021 17:47:17 GMT

No, no, no! Don't use shapefile to transfer data between SQL databases -- It will corrupt any numeric NULLs, datetime or string data exceeding 254 width (not to mention all field names in excess of ten characters).

File geodatabase was designed to support all the types likely to be found in either database. Note that there could be reserved word issues to resolve after export but before import.

- V