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?
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:
Good luck, let me know how it turns out.
Micah
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.
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