sqldba_newbie (4/9/2012)
opc.three (4/9/2012)
An alternative to using OPENQUERY is to use the EXECUTE...AT syntax to also force the query to execute on the remote server. OPENQUERY is a bit more flexible in that you can use it with SELECT...INTO, but the query parameter is limited to 8KB and OPENQUERY will not accept a variable.See Example G in this article: EXECUTE
If you know the shape of the resultset you can use the INSERT...EXECUTE technique to capture rows into a local table.
This is for a view. What do you mean by "query parameter is limited to 8KB"?
OPENQUERY is only able to send 8KB of SQL to the remote server.
From the Arguments section OPENQUERY (Transact-SQL)
Arguments
linked_server
Is an identifier representing the name of the linked server.
' query '
Is the query string executed in the linked server. The maximum length of the string is 8 KB.
I have seen this limit exceeded with lengthy report queries.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato