We’re attempting to read data from an external MySQL database (Win2003) using SQL Server’s Linked Server facility, Views in the Navision DB, and using LinkedObject=Yes in the Navision tables. The Views work fine from Enterprise Manager, but I get one of two error messages when trying to Run the Navision table. Both refer to Navision Text fields. 1. The xxx table contains an MBS Navision-field data type that is not compatible with the SQL Server data type: Field: xxx Type: Text150 SQL Type: Text 2. The following SQL Server error(s) occured while accesing the XXX table: 8626, “42000”, [Microsoft][ODBC SQL Server Driver][SQL Server] Only text pointers are allowd in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table. There was a prior topic on this subject, but unfortunately no resolution. Any ideas? Thanks
Your SQL View on the linked server is exposing that field as a SQL Server Text type which is a textual version of a BLOB, i.e. a large data type. Looking at your Navision table you are expecting this to be a regular Text150. Either MySQL is exposing a regular Varchar as a large type, or your view is doing this. Either way you need to change the type conversion so you end up with a Varchar(150) or similar, in the SQL view.
Thanks for the reply Robert. My gratitude. As a first attempt, I went to the View script and tried a CAST to Varchar in both the CREATE VIEW AS SELECT part of the statement as well as in the FROM (‘SELECT …’) portion, and neither worked. 1. Is there a “correct” way to change the type from within the View? 2. How does one change the way a database (MySQL) exposes it’s data type? (hopefully you’re not talking about an ODBC driver issue). Thanks, Alan
I dont know MySQL, but it sounds like the field has been created with the wrong type and casting wont help. What is the native MySQL type?
Robert, Turns out our customer has two servers. When I re-did the Data Source to their second server, and created a new Linked Server entry, everything works correctly. Clearly, the problem is something in the way the server or MySQL is setup. I’m going to have them check to see what’s different on the two. Don’t think there’s much I can do on my end. Thanks for the assistance. Appreciate it. Alan