0

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?enter image description here

What does NCPR mean -this text doesn't exist in the table -it it code for something?

4

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 object_definition() of 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 +-/*= characters)
  • 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! ¯\_(ツ)_/¯

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.