The Problem
I am a big fan of the query layer in ArcGIS 10. I routinely create and manipulate spatial data that lives in the geography data type in SQL Server. The query layer tool has opened up a whole new set of analysis and visualization possibilities for a number of large data sets I work with. Overall, I am a big fan…
But, I’ve run into a problem. Using the query layer tool I couldn’t connect to a number of SQL Server databases. When I would try to connect to certain databases I would get this cryptic error.
What Does this Mean?
What is going on? Is this an actual error? I am missing some font pack therefore displaying the characters I see on screen?
With this useless error I started to trouble shoot. I knew I had SQL Server databases that worked correctly and some that generated this error. The only difference between the working and non-working databases was the length of the database name. As it turns out the length of the database file name that is allowed by the query layer is limited to 31 characters. Anything greater than 31 characters will generate this error. This is not listed in the query layer documentation.
What the Heck!
In my job I work with dozens and dozens of databases that have a prescribed naming convention. Sometimes, these SQL Server database names can get long, but no where near the SQL Server character limit. Within the Esri software family there are described limits to database names. When using ArcSDE the name of the database is limited to 31 character, which is understandable.
However, if a user adds a SQL Server database to the “Add OLE DB Connection” tool in ArcCatalog the database name can be more than 31 characters, but if the user adds a SQL Server database with a name greater than 31 characters to the query layer tool it will error out. What a bummer.
What’s Next
I posted this question in the Esri forums and the response I got from the Esri rep was accurate, but this limit is a pain to those want to use this tool. In the online documentation for the query layer the text states “Query layers allow both spatial and nonspatial information stored in a DBMS to be easily integrated into GIS projects within ArcMap.” I agree with this statement 100%, unless my database name has more than 31 characters…
By limiting the length of the database name Esri is saying that I cannot take full advantage of the tools provided by SQL Server. Also, if I can only use 31 characters, please document it in the query layer documentation. Either this is a bug, an undocumented limitation that needs to be addressed, or it is documented somewhere and I can’t find it (which is a real possibility). I am thinking that this problem is probably a 32 bit issue, in fact a couple of my coworkers are thinking the same thing. I’m using a 32 bit software in a 64 bit world!
As a geospatial database admin (GeoDBA for short), I need to make a decision that will impact the workflow for my organization. I have business requirements that limit me from renaming these business critical databases, but I have new business requirements that are greatly benefited by the query layer functionality. Do I rename databases (and modify the tools that access this information) or do I wait for Esri to fix the problem? Moving forward I’ll try to limit the number of characters in my databases but I’m using a SQL Server database, not an SDE database. Why is Esri limiting what I can do with that piece of software? Unfortunately, it may be faster for me to rebuild everything than it would be to get a solution from Redlands.
I’ll submit this problem to Esri tech support soon.
And…I am running ArcMap 10 with SP 2 using SQL Server 2008 R2 that is fully patched and updated. The error described has been repeated on several machines within my organization.
Until next time GIS geeks!