I just ran into a less-than-documented issue with OLE DB and SQL Server 2008 64 bit Enterprise. The particular error message was:
OLE DB provider 'HP3KProvider' for linked server 'HP3000' returned data that does not match expected data length for column '[HP3KProvider].sid'. The (maximum) expected data length is 28, while the returned data length
So what if the data is smaller than what was expected? Well, SQL Server cares and errors out. To get it to stop doing this, a trace flag is needed.
Before you do this, try to research what other effects, if any this will have, although I could not find much on this flag. I will be running this in production and will update this post if I find anything.
To get this to stop, apply the trace flag 8765. You can do this either by using the command:
before the query, or use a startup flag (the method I used) for the service. This is added using SQL Configuration manager for each instance in the form:
and, as you'll see, the parameters are semi-colon delimited.
Trace Flag 8765
Did you ever find out anything more about this trace flag? It solved a problem for me recently, but I am still coming up short trying to find any info on it.
Thanks for your help,
I haven't found any information on it.