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?
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.
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.
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