Urgent 2005: Linked Server data doesn’t match expected length for column
Question
Friday, June 13, 2008 3:51 PM | 1 vote
I’m hoping someone can help me here because I’ve hit a HUGE issue migrating from SQL Server 2000 to 2005. We just upgrade TEST to SQL 2005 (on Prod we are still 2000). On both I have a linked server pointed to the same Cache database (InterSystems Cache - not a fan, but the vendor uses it).
On PROD everything works fine but on TEST we are seeing a huge issue. On some of the cache tables they under state the actual length of the data to be returned. In turn we are seeing the following error message being returned by SQL Server:
**********************
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server '-X-' returned data that does not match expected data length for column '[MSDASQL].SecurityText'. The (maximum) expected data length is 32, while the returned data length is 35.
**********************
This is apparently tied to inaccurate Meta Data being sent to SQL. I even used Cache Monitor (a tool for quering Cache database) to verify the Meta Data and it does show the Cache database is stating the data will be no longer than 32 (though some of the data returned is of length 35).
Okay, Cache shouldn’t be returning in accurate Meta Data for sure. However, I can’t do anything about this. I can’t make changes on the Cache side due to vendor related matters.
With all that said, SQL 2000 (we have the latest SP) has no issue with the bad Meta Data, it just ignores it. So I did some checking around and found out that SQL 2000 had the same issue. Here is a link to the KB article (http://support.microsoft.com/kb/920930) with the exact error message we are seeing for the exact situation. There is a fix but for SQL 2000.
I also found others are running into the same issue with linked servers on 2005. Here a link to one of a few I found (http://www.sqlservercentral.com/Forums/Topic473552-146-1.aspx).
Does anyone have any ideas as to what I can/should do? I have no options on the Cache side so I have to find some way to make SQL Server 2005 behave as 2000 did when running into this issue.
This is a show stopper for our production environment because we pull so much information out from the vendor database over the linked server. So I am desperate.
Thank you in advance for any help or feedback you can provide.
Steve
All replies (17)
Wednesday, July 30, 2008 5:55 PM ✅Answered | 2 votes
SELECT principal_diagnosis_code, DIAGNOSIS FROM OPENQUERY(AVATAR,
'SELECT principal_diagnosis_code, cast(principal_diagnosis_value as varchar(40)) as DIAGNOSIS
FROM SYSTEM.history_diagnosis ')
actually, this works
where AVATAR is the name of the linked server on 2005
and principal_diagnosis_value is the field with the data error, it just trunactes it on the Cache side
Monday, June 16, 2008 1:59 PM
We experienced the same problem with TeraData and Postgre databases. Even though the metadata says the field is 32 characters, the user is able to insert any number of chars into the field. The ultimate solution to this is to fix the metadata on your cache server, since it is wrong.
However, I think this option stops it from looking at the size of the field. I have no way of testing it.
EXEC master.dbo.sp_serveroption @server=N'[linkedservername]', @optname=N'lazy schema validation', @optvalue=N'true'
Friday, June 20, 2008 1:24 PM
I didn't mean to fall off on this but a medical issue came up in my family and I've been away from work.
Thanks Tom for taking the time to reply. I really appreciate it!
I read up on this and it sure looks like it should work. However, it is version specific. LAZY SCHEMA is not avalible with Version Standard (how nice of MS). When I run the proc I get this message:
Msg 15169, Level 16, State 1, Procedure sp_serveroption, Line 81
The server option "lazy schema validation" is not available in this edition of SQL Server.
Do you or anyone else have any other ideas I could look at (I'm really against the wall on this)?
Thanks again!
Friday, June 20, 2008 2:02 PM
Now that I read that option, that is only for Enterprise for Partitioned tables. So it probably won't fix your problem.
I would try contacting InterSystems. They must have run into this before. It is not really a MS SQL problem as much as a problem with the InterSystems database engine.
Wednesday, July 2, 2008 1:27 PM
Sorry for the drop off on my part with this post. A big issue came up at home (again) and I had to drop everything. Sorry again for asking for feedback and then not responding.
Tom, thanks for the idea on the Lazy Schema. I saw your follow up comments but I thought I would try it anyway, just incase. Since I'm running XP I couldn't pull down 2K5EE so I pulled down Developer. I applied Lazy Schema, but as you thought it had no impact. I still get the error.
With that said I was finally able to persuade the powers-that-be here to open a support case with MS. They are looking at it and agree it shouldn't do this. So they are going to try to address it for us. If they are able to fix this issue I will post back what they did so that others running into this issue can do the same.
Thanks again.
Steve
.
Wednesday, July 2, 2008 1:38 PM
Although MS might have an answer, this is still a Intersystem problem, not an MS problem. The problem is the OLE DB driver talking to the InterSystem database. This is not specific to SQL server. Every OLEDB connection would be getting the same results.
InterSystem must have run into this before. I would contact them.
Wednesday, July 30, 2008 4:43 PM
I'm getting the same error on a Cache table. The odd part is I can import the data directly into sql using something like cast(BADFIELD as varchar(40)), but when I try to query against the linked server I cannot get past the
returned data that does not match expected data length
Did you ever get any other feedback on this?
Wednesday, August 20, 2008 3:01 PM
What OLE driver are you using to LInk SQL to Cache?
I am needing to do the same thing and trying to figure out where to start.
Thanks,
Wednesday, October 29, 2008 5:37 PM
SawyerJ we are a new Netsmart Avatar user attempting to link Infopath into the Avatar Cache database. I apologize for contacting you this way but wondered if you have had any success with this. I can get to the data via ODBC and MS-Access but not Sharepoint.
Any thoughts?
Wednesday, November 5, 2008 11:27 PM
I have to disagree. I am running into the same issue but my Caché schema is correct. A quick search of the web will show many entries where this is a problem area in MS SQL.
My scenario:
Originally InsuranceName had a maxlength of 50. MS Query could see this has VarChar 50.
I needed to bump it up to 100 so in Caché I changed the InsuranceName's maxlength to 100. MS Query could see this as VarChar 100. However, MS SQL Server 2005 is puking on an entry that is 52 characters long because it thinks it can only be 50 characters long. Another tech and I have spent several hours trying to make the MS SQL Server forget about the old schema and acknowledge the new one. In contrast, it is very clear in Caché how to add, drop, view linked tables (servers).
The same query works perfecly fine from Excel/MSQuery.
So, my search continues for a solution within MS SQL.
Monday, March 1, 2010 3:36 PM
Hi Robert,
Did you get an answer to your above problem. We are having similar issue. We have Avatar 2006, whose data we want to transfer from Intersystems Cache to sql server for reporting purpose. Linked server is giving me error while query data, with higher length. I am using DTS as of now. SSIS is also not so straight forward with this. Please let me know if you have any solution to this.
Thanks
Wednesday, March 3, 2010 1:30 PM
This is a problem with the "metadata" in the Intersystems Cache database. The metadata needs to be updated for this to work.
Please see this thread:
Thursday, July 28, 2011 7:58 PM
THANK YOU so much for the insight on this issue. It has caused me aggrevation for quite a while now. Now I am looking to script out a routine that will allow us to identify which columns are problematic.
Ernest
Ernest D Cook
Thursday, June 7, 2012 5:12 PM
Anyone with Intersystems Cache db experience know how to 'update' the 'metadata'? We have run into this same issue.
Thursday, June 7, 2012 8:40 PM
You need to contact Intersystems to correct the database metadata.
Thursday, August 16, 2018 10:36 AM
worked like a charm. Thanks
Friday, March 13, 2020 6:46 PM
First of all, you have to connect directly to MySQL and find the exact type and Length of the desired column ('SecurityText').
Then you can use cast() function in your openquery select statement like this:
SELECT * FROM OPENQUERY(LinkedServer,'SELECT cast(SecurityText as TypeFound(LengthFound)) as SecurityText FROM SchemaName.TableName')