I am trying to create a view in Sql 2k which looks at a table in Nav Fin 2.5 and retrieve a list of Customer details. One of the field types is set to code20. When Sql tries to access or retreive this data it displays an expected data length error as some of the fields only have up to 5 Characters in but Sql is expecting a fixed length of 21. Has any one else come accros this error and if so how can i overcome it ?
Hi. I don’t know much about SQL Server, but it seems that the problem is the data type you declared in SQL 2k. If SQL is expecting a fixed length of 21 characters and Navision provides only 5, why don’t you use varchar or nvarchar instead of char? Maybe that’s the problem… Regards
Hello Wayne, there’s already the same question posted on 28.07.2000 by Ronald Raab(Using C/ODBC with OLE DB (SQL Server) and fixed length fields). Unfortunenatly no solution is provided in that thread. Please let me know if you find a workaround. Conrad Gerte
I have looked everywhere for a fix or workaround for this and everyone who has this problem or knows about the problem cannot suggest a fix.[:(][:(] Only option is to move to SQL but we have no immediate p[lans to move from 2.5 to SQL as yet !! but no doubt it will be forced upon us now that Microsoft have control of the reigns, i just hope that they chnage the coding so taht it is optimised for SQL rather rather than Native Database as at present. If anyone out there has any suggestions i would be more than grateful I am trying to find some way of changing the data type from Navision by using CAST / CONVERT or similare i.,e i want to be able to chnage the way SQL recognises the Native CODE field of 20 Chars and chnage the way it interpretes it (at present it thinks its a fixed length column of 21) if i can get it to think it is a varchar i think it will work but i need to play around a bit or if anyone has done something similar - perhaps they can give me some tips !!! There must be more people out there who have come across this error surely ! We have a long way to go before we move to SQL so an interim fix would be much appreciated. I suppose thats one thing to look forward to in SQL - integartion issues should almost disapear !!![?]
There are OLE DB to ODBC drivers out there with source code for those who need them. These are similar to “Microsoft OLE DB Provider for ODBC Drivers” but with source code. They are transparent drivers so you can modify the source code to translate the field type and length if you need to. I can’t guarantee that this will work or be compatible. Last time I checked someone on the web was offering the source code for $200 USD. I don’t have the URL anymore, but here are some places to start: http://ourworld.compuserve.com/homepages/ken_north/oledbVen.htm http://ourworld.compuserve.com/homepages/ken_north/ODBCVend.htm You could also try an “ODBC to ODBC Driver”, get the source code and make a translation if needed. I didn’t do this, so proceed at your own risk. Good luck…
Also, after you make the above ODBC to ODBC driver, you will get errors because C/ODBC driver is not stable enough to be used in a multithreaded environment. So the solution is to move to SQL Server.
I don’t know if this line will work: SELECT Name, RTRIM(MyCodeField) FROM Customer WHERE Name LIKE ‘A%’ Try it…
A more brute option is: SELECT LEFT([Field],5) FROM dbo.[Customer] CAST and CONVERT don’t work because these functions are for presenting the same data in another format (no loss of information is allowed, even if this information is trailing space)