I am well aware that MS SQL Server does not allow variables to be used in the OPENQUERY statement and I'm aware of the workarounds.
What I'd like to know is, does anyone know WHY variables or concatenated strings are not allowed?
The hoops this causes people to jump through is simply astounding and it just isn't in line with other functions such as EXEC.
Can anyone comment on this? Who do I have to bribe at MS to get his sorted out?
The limitation is caused by the way linked servers are implemented in SQL Server, and the way its dependant libraries work. There is a whole chain of libraries used to pass your SQL Command, all of them have different variable sizes and cache sizes. Some of the limitations are in the OCBC drivers themselves, so 8k is a safe bet from Microsoft. Not allowing you to pass variables just enforces the 8k limit rule.
declare @v1 varchar(max)
EXEC('SELECT * FROM OPENQUERY (, ''SELECT * FROM WHERE = ''''' + @v1 + ''''''')')