Converting GDB to SQL, drawing error

Question

I'm working on converting the template gdb to our sql server.  I had no problem tranfering the schema and data via the XML conversion, but I do have a problem with the RoadCenterline layer in the template mxd.

The error I get is in the ArcMap Drawing Errors window:
"Underlying DBMS error [Micorsoft SQL Server Native Client 10.0:'CHAR_LENGTH' is not a recognized built-in function name.]
[-feature name-]

Any thoughts?  I've looked at the schema and all of the attributes for the RoadCenterlines layer and cannot find anything that says 'CHAR_LENGTH' anywhere.  I'm assuming it has to do with the difference between the File GDB and the SQL GDB.  The roads are still drawing and labeling, so I do not even know what is really going wrong with this error.

Thanks,
Scott

SeikiAlice · Answer

I'm working on converting the template gdb to our sql server.  I had no problem tranfering the schema and data via the XML conversion, but I do have a problem with the RoadCenterline layer in the template mxd.

The error I get is in the ArcMap Drawing Errors window:
"Underlying DBMS error [Micorsoft SQL Server Native Client 10.0:'CHAR_LENGTH' is not a recognized built-in function name.]
[-feature name-]

Any thoughts?  I've looked at the schema and all of the attributes for the RoadCenterlines layer and cannot find anything that says 'CHAR_LENGTH' anywhere.  I'm assuming it has to do with the difference between the File GDB and the SQL GDB.  The roads are still drawing and labeling, so I do not even know what is really going wrong with this error.

Thanks,
Scott

Thanks you for the post.

ScottSnider · Answer

Perfect, thank you very much!!!!

For documentation's sake:

I went through each map level and checked the SQL Queries for all of the Classes w/ the name "Shield" in them.  I Changed the "CHAR_LENGTH" statement to "LEN" and that fixed the problem.  Now I see that I was missing highway shields!

KateBorland · Answer

CHAR_LENGTH is a SQL Function that is being used in the SQL query for most of the RoadCenterline label classes that include road shields. These label classes and their associated SQL queries are: Interstate_Shield - "RoadClass" IN (1 , 2) AND "HIGHWAYTYPE" = 1 AND CHAR_LENGTH ( "HWY" ) < 3 US Highway_Shield - "RoadClass" IN (1 , 2 ) AND "HIGHWAYTYPE" = 2 AND CHAR_LENGTH ( "HWY" ) < 3 State Highway_Shield - "RoadClass" IN (1 , 2 ) AND "HIGHWAYTYPE" = 3 AND CHAR_LENGTH ( "HWY" ) < 3 Interstate_WideShield - "RoadClass" IN (1 , 2) AND "HIGHWAYTYPE" = 1 AND CHAR_LENGTH ( "HWY" ) >= 3 US Highway_WideShield - "RoadClass" IN (1 , 2) AND "HIGHWAYTYPE" = 2 AND CHAR_LENGTH ( "HWY" ) >= 3 State Highway_WideShield - "RoadClass" IN (1 , 2) AND "HIGHWAYTYPE" = 3 AND CHAR_LENGTH ( "HWY" ) >= 3 These queries control the shield symbol that will be used for various route numbers. Any route numbers that are less than 3 characters will use the smaller shields and any that are 3 or more characters will use the wide shields. You can find the Label classes and SQL queries in the Label properties tab. Let me know if you need any other information. Thanks, -Kate MarkEastwood_1-1634061195998.png