SQL database (Non Spatial) name changed to include a dash(-), now I cannot work with it using python.

1369
6
12-02-2020 02:53 PM
MichaelTorbett
Occasional Contributor II

My agency recently migrated to the Microsoft Azure Cloud (sql server) and with it came a database name change. The name of the new database is "Migrate-Forestry".  I've gone through and updated my sde connection file and made sure my python scripts are pointing to it correctly. However all of them are now failing when trying reach a table through the sde connection. It's the generic error "Table does not exist or is not supported." I've done a lot of research but cannot find a solid answer to why my scripts are failing. I think it's because my database name has a dash in it, but just wanted clarification on this. Any help would be appreciated. 

Thanks,

Michael

0 Kudos
6 Replies
George_Thompson
Esri Frequent Contributor

It may have to do with the renaming of the geodatabase.

https://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-sql-server/move-geodatabase-sqlserver....

If you want to move a geodatabase to a new SQL Server instance, you can use SQL Server tools to move the entire database. Options to move a SQL Server database include detaching the database file from the SQL Server instance and attaching it to a new instance, creating a backup of the database and restoring it to a new SQL Server instance, or using the Copy Database wizard.

No matter which of the methods you use to move your SQL Server database, you cannot rename the database. When you restore a database, for example, you are given the opportunity to restore it with a different name. Don't do this with a geodatabase; you won't be able to connect to it.

All object names in the geodatabase system tables are fully qualified with the database name. In addition, many stored procedures use a three-part naming syntax in their code, which follows the format <database>.<owner>.<object>. If the database name changes, you will not be able to execute these procedures


Can you connect to the DB outside of Python via ArcGIS Pro in the same Azure region?

Also be sure to review these docs: https://enterprise.arcgis.com/en/server/latest/cloud/azure/functionality-geodatabase-azure-sql-datab...

https://desktop.arcgis.com/en/arcmap/10.3/manage-data/databases/azure-sql-database-and-arcgis.htm

 

 

--- George T.
MichaelTorbett
Occasional Contributor II

Hey George, 

Thank you for your reply.  I apologize, I should have worded my question better. The enterprise database that I am trying to connect to is not a Geodatabase, but just a standard non spatial sql database. I have scripts that pull data from it and update feature classes and feature services elsewhere. 

Yes I can connect to it in ArcGIS Pro. However, I cannot run Geoprocessing tools on any of the tables unless I first add them to my map first. I get the same message (table does not exist or is not supported).

Referring to what you said about the object naming convention in Geodatabases. When I make an sde connection to this database, it sees all of the tables with the database name in front of it. Example: Migrate-Forestry.dbo.gacounties. I'm thinking the dash/hyphen is the problem. If so, do you  by chance know of a work around without renaming the database?

0 Kudos
George_Thompson
Esri Frequent Contributor

I appreciate the updated information. I am not aware if the "dash / hyphen" will cause any issues.

This may be best to work with technical support if no other users respond.

--- George T.
0 Kudos
MichaelTorbett
Occasional Contributor II

Thanks George! I have created a case with ESRI Support. Maybe  there's workaround using python or something else. 

AndrewRitchie_CoL
New Contributor III

Hi Michael,

Wondering what the resolution was in your case?

A few months back I was trying to connect on prem ArcGIS Pro, to a non-spatial AzureSQL database (with recommended Microsoft naming, which includes hyphens). I was able to make the connection, browse the list of tables, but unable to add the tables to the map, or browse the data.

We are about to pull the trigger on renaming the server/database because of this.

Today when I decided to test it again, I am now able to add tables to the map, and can browse data for smaller tables. I cannot view data for larger tables, so my issue now may be a timeout, rather than anything to do with the naming. In the interim - we have bumped up the AzureSQL server resources - so that may also have been part of my issue.

Were the hyphens ever the problem?

0 Kudos
MichaelTorbett
Occasional Contributor II

Yes, the hyphens seemed to be the problem. My db admin ended up renaming the database without a hyphen and that solved my issues.