Data import: native Navision DB ---> SQL Server
-
Dear all,
i'm trying to import some very simple data from native Navision-DB to
SQL Server for further analysis. This for I created a little DTS:
- Query:
SELECT Nr_, weight FROM Artikel
- Preview of result-set (works fine):
Nr_ | weight
50000067 | ,00000
50000068 | 6,3200
50345435 | ,00000
50000234 | ,75000
.....
- Datatype of destination field "weight" on sql-server is set "decimal" by the wizard (i already tried to some other datatypes --> no success)
- Error message when DTS try to insert data into destination field:
Insert error, column 2 ("weight",dbtpye_numeric), status 12: Invalid
status for bound data
Can anybody help me?
-
Not surprising. DTS is rejecting numbers that start with a comma, those would be varchar!
Before importing the data, sort it (descending) by weight. The sort will force the commas to the beginning, and DTS will then recognize it as a varchar field. (Of course, you will need to set the datatype).
Are you saving the package? It is not difficult to open the saved package in enterprise manager and work with it from there.
Good Luck,
Sara
-
I just rewrite the query to remove the commas.
select Nr_
, ltrim(substring(weight, 1, patindex('%,%',weight) - 1)) + ltrim(substring(weight, patindex('%,%',weight) + 1, datalength(weight))) as weight
from Artikel
-
Hi ... the query rewrite did not work ... seems as if the C/ODBC-driver does not support the mentioned functions (e.g. ltrim).
But I don't need it anymore, cause I solved the problem
. It was just a matter of the C/ODBC-options. There's a field "indentifiers" with the default value "All except DOT". I changed this value into "a-z,A-Z,0-9,_" and now everything works fine.But thx for your replys anyway
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply