Unable to publish XY event layer using ODBC connection

[KeithGerhartz1]

ArcGIS Server 10.1 does not support referencing ODBC/OLE connections from within ArcCatalog as of 8/23/2012. This means that that one is unable to publish event layers that are created directly from ODBC/OLE data sources. Rather, one's only option is to automatically copy the data to the server as part of the service sharing procedure.

ESRI support has assigned a bug to this and is working to correct:

[#NIM084080  Allow users to register OLE DB connections with the data store. ]

I did find a workaround for SQLServer. A similar workaround may be possible using other RDBMS but I cannot confirm.

1. Establish ODBC access to remote data by creating a "Linked Server" in SqlServer Management Studio. This can be tricky as the information required by SQLServer is not well documented. We are accessing a DB2 database on an iSeries AS400. Instructions for this task can be found in the attached document.

2. Create a view from within your geodatabase that performs a select against the tables that are referenced by the linked server.

The ODBC connection (i.e., linked server) should now be visible from within ArcCatalog and you should be able to add tables to your map, create event themes and publish them without ArcGIS Server automatically creating a copy the data. Note: you will still get a warning that you are publishing XY data as an event theme.

Word of warning, the performance I experienced with large tables was poor. It appears that all rows are retrieved with no caching.

I hope that ESRI addresses this situation as performance was much better with 10 using a direct ODBC connection to the remote data.

Please share any experiences or information relative to this issue. Thanks.

[TanuHoque]

Since the backend RDBS is SQL Server, I'd ask to create a Database Connection to your database using ArcCatalog @ 10.1. Once you do that you'd see the table listed in the Catalog window, just drag-drop that in ArcMap and create XY Event layer from that table.

[KeithGerhartz1]

You can not create a database connection directly to an AS400 database in 10.1. You can only connect to supported database versions which in the case of DB2 would be through the DB2 9.7 client. The DB2 9.7 client can not connect to an AS400 either unless you purchase DB2 Connect from IBM. In our case, we are publishing AS400 data through a FLEX app and a server version of DB2 Connect would be required to the tune of $2K - $3K. An expenditure that we are not budgeted for.

So, just to recap ...

ESRI eliminates the ability to use OLE/DB connections so if you want to connect to DB2 on an AS400, then you must use the DB2 9.7 client. However, this client can�??t access the AS400 unless you buy DB2 Connect for $2K +. Nice, huh?

Does ESRI understand how many of their clients maintain legacy systems in the AS400 environment and the impact that their unfortunate decision is having on them? What are we to do when accessing the plethora of other data sources via ODBC. Does ESRI appreciate the flexibility inherent in being able to access external data sources using a long accepted connection method in OLE/DB/ODBC?

The workaround that I devised above is unworkable for accessing data live due to poor performance. I am presently developing data replication scripts with the hope that ESRI will realize that generic OLE/DB connectivity should never have been taken away and should be restored ASAP.

[NathanOgden]

Would anyone have any updated information on this topic?  I too am trying to make the same connection.  I followed your instructions and created the linked table in MS SQL Server 2008 R2 Management Studio (Express), however, the connection does not show up in the list of tables when I try to add a database connection in arccatalog.  Must be missing a step.  Your document was good information though.