topic Converting GDB to SQL, drawing error in Mapping Questions

Converting GDB to SQL, drawing error

ScottSnider — Mon, 25 Jul 2011 11:08:52 GMT

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

Re: Converting GDB to SQL, drawing error

KateBorland — Tue, 26 Jul 2011 20:07:22 GMT

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

Re: Converting GDB to SQL, drawing error

ScottSnider — Tue, 09 Aug 2011 12:40:58 GMT

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!

Re: Converting GDB to SQL, drawing error

SeikiAlice — Mon, 28 Nov 2011 02:12:22 GMT

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.