I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.
I have tried to get a reply in the GIS SE about switches to apply in OGR2OGR but have not got any options. https://gis.stackexchange.com/questions/314669/ogr2ogr-conversion-failed-when-converting-the-varchar-value-to-data-type-int
How can I change this in the table?
What does NCPR mean -this text doesn’t exist in the table -it it code for something?
The good news: You’re not missing anything obvious or arcane.
The bad news: If you’ve got a proper DBA onsite, it’s time to get them involved.
Dollars to donuts the error is in the
dbo.vw_AUTH_CREFNO. Typically the
vw_ prefix indicates that the object is a
VIEW, meaning that it’s just a wrapper for a larger
SELECT statement which itself can have a series of poorly written
JOINs, malformed expressions, and nested evaluations.
If you have
VIEW DEFINITION permission in the
GIS_Test database on server
zzzz (and the object is not encrypted), it’s time to try extracting the base definition and crossing your fingers the DDL is sensibly written and it’s an obvious error. In that Object Explorer pane you’ve got open, right-click
dbo.vw_AUTH_CREFNO and hit Script View as Create To New Query Editor Window, then Ctrl+f and hope like hell you find
NCPR as a string literal in the code. What’s more likely though is that you’ve got one ore more of the following
- a string column
JOINing to a numeric column somewhere
- an expression somewhere between string and numeric (look for the
- an explicit conversion against a value where you’ve got “dirty data” in the base table (whether by typecasting or coalescing into a bad target)
- something else?
If you’re lucky enough that
NCPR is a string literal somewhere in code, but it’s nested below the first level object AND it’s still in the same
GIS_Test database, you can spelunk pretty broadly for it using the following snippet.
select def = object_definition([object_id]) ,[name] ,schema_name([schema_id) from sys.objects where object_definition([object_id]) like '%NCPR%';
If you’re not so lucky, you need to look for that value in the base data, or try more advanced methods looking for which (of possibly several) problematic expression is causing your current problem.
Good luck! ¯_(ツ)_/¯
The value ‘NCPR’ occurs in Vicmap Property in the property number field. The field is for Victorian councils to populate with their internal property number. When a council doesn’t have a property number for a property, they will sometimes populate it with ‘NCPR’, which stands for non-council property reference.
Any processes you have that rely on this field having only numbers will fail when it runs into the NCPR value.