Query Layer Won't Retain Attribute Values When Coverted to Table

Question

So I'm starting to work with ArcGIS and SQL Server. I've successfully made a DB connection in ArcMap and created a query layer. I'm trying to automate this process with Python and I'm aware of the bugs involved and I found this workaround involving arcpy.Describe. arcpy.MakeQueryLayer_management(DB_Conn, query_layer, "SELECT * FROM CMORPH_Test.dbo.cmorph_data WHERE precipmm > 1", "OBJECTID", "", "", spatial_reference) desc = arcpy.Describe(query_layer) query_table = arcpy.mapping.TableView(desc.name) arcpy.CopyRows_management(query_table,interim_table)‍‍‍‍ This creates an info table. The problem is, each one of the records in the SQL server contains a date field (called "day") formatted like this: 19980101. When I open the info table in ArcMap or convert it to a dbf, the date field is now zero. Any ideas why this would happen?

LloydBronn · Answer

I set up an OLE DB connection to the SQl server and set the workspace to a geodatabase. I ran the make query table tool and I got this error:

000383 : Issue with a table; cannot find the workspace

LloydBronn · Answer

Thanks for the response. The database is SQL Express 2012. Eventually I'll be working with a full SQL Server database and complex queries after testing. I've also tried writing to a dbf, but the date field also becomes zero. I'll try creating a query table instead.

My end result is to make a map service that will be hosted on our ArcGIS Server. Users will be able to click on features and return results of queries directly from the SQL database. This sample data I'm working with will be points. The data in SQL has lat, lon, precipitation in mm, and observation date fields. I've been creating map services with the Javascript API and Python, but now I'm trying to figure out how to integrate SQL into the mix.

MicahBabinski · Answer

Hi Lloyd,

What is the database type of the date field in your RDBMS (the one from which you make the query layer)?

A couple thoughts:

* Since you are working with tabular data, try using the Make Query Table tool instead of Make Query Layer
* Try writing your output interim_table to a geodatabase table or directly to .dbf table (just add the .dbf extension)
* You should be good to copy your Query Table directly to a proper table using CopyRows (no need to make an arcpy mapping table view object first). If there's a bug related to this, please send me some info on it! I'd like to know about that as I teach people about making query layers and query tables from time to time.

Good luck, let me know how it turns out.

Micah