I was working on a quite straight forward ESRI SDE (MSSQL) to MSSQL Spatial ETL workspace today when I ran into an issue which I couldn't find a solution for anywhere else.
Writing to a MSSQL table with a number of columns using various data types would fail and give me the following error:
Failed to write a feature of type `dbo.MyTable' to the database. Provider error `(-2147217887) Invalid character value for cast specification'. SQL Command `DECLARE @WKB0 varbinary(max); SET @WKB0 = ?; DECLARE @SRID0 INTEGER; SET @SRID0 = ?; INSERT INTO dbo.[MyTable] ([data_id], [localId], [namespace], [version], [originalRef], [program], [project], [projectdesc], [purpose], [assignee_id], [owner_id], [startDate], [endDate], [scale], [res], [method], [prec], [visibility], [methodAlt], [precAlt], [maxDev], [restrictions], [totQuality], [geometry]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE @WKB0 WHEN '' THEN NULL ELSE geometry::STGeomFromWKB(@WKB0,@SRID0) END)'
Not much else in terms of useful information. I checked all the table columns and made sure all "not null"-columns had data, and that data passed to the writer matched the correct data type. The culprit ended up being the localId-column, who is a uniqueidentifier. It turns out that even though I read the uniqueidentifier value from a ESRI SDE it can't be written back to a MSSQL (spatial) writer without adding squiggly brackets.
So if your SDE reader gives you: "f69dceae-3bc6-43e2-8279-78b25a23564c" you'll need to alter it to "{f69dceae-3bc6-43e2-8279-78b25a23564c}" before writing.
I suppose it makes perfect sense because when writing to a uuid-field using Python (arcpy) you will also encounter problem if you forget {}. This works in arcpy:
with arcpy.da.InsertCursor(myFeatureClass,("myField") as cursor:
cursor.insertRow(("{f69dceae-3bc6-43e2-8279-78b25a23564c}")
This has happened to me before, but next time it happens I'll just check my own blogpost ;-)
