--------------------(廣告好用的東西)---------------
ref https://blog.poychang.net/sql-server-open-query/
好棒的用法!!
DECLARE @SQL NVARCHAR(1000)
DECLARE @EmplID NVARCHAR(10)
SET @EmplID = '12258'
SET @SQL = 'SELECT * FROM dual WHERE EmplID = ' + @EmplID
SET @SQL = 'SELECT * FROM OpenQuery(PROD, ''' + REPLACE(@SQL, '''', '''''') + ''')'
EXEC(@SQL)
--------------------(正題)---------------
問題

    SELECT *  FROM OPENQUERY ([linkserver],
       'EXEC db.dbo.sp_xxxx')
 
訊息 7357，層級 16，狀態 2，行 54
無法處理物件 "EXEC db.dbo.sp_xxxx"。連結伺服器 "linkserver" 的 OLE DB 提供者 "SQLNCLI11" 指出物件沒有資料行，或是目前的使用者沒有使用該物件的權限。

測試步驟
1.排除connection問題.select linkserver其它table,可以
2.排除無權限使用OPENQUERY問題.OPENQUERY裡面放select
3.排除OPENQUERY無法使用EXEC問題.OPENQUERY EXEC (@query)
4.加上SET FMTONLY OFF;就解決了

    SELECT *  FROM OPENQUERY ([linkserver],
       'SET FMTONLY OFF;EXEC db.dbo.sp_xxxx')

解決方法
在EXEC之前,先執行SET FMTONLY OFF;

    SELECT *  FROM OPENQUERY ([linkserver],
       'SET FMTONLY OFF;EXEC db.dbo.sp_xxxx')

什麼是FATONLY ON
1.就是只要欄位名稱,不要內容(only the metadata)
View the data line header information of the query without actually performing the query.
Here we come up with ‘SET FMTONLY’ statement. If this is being set as ‘ON’ (by default it is OFF) at the start if a batch of SQL statements or in any stored procedure; then while executing that batch or stored procedure we will not get the actual results of that sp/batch, but instead we will get only the metadata (i.e. column names) being returned by the SQL.

2.只進行剖析語法是否正確 (only get parsed / complied.)
As we talked earlier about ‘SET NOEXEC ON’ statement in TSQL which restricts the SQL batch statements to get executed but they only get parsed / complied. When we set NOEXEC as ‘ON’ then in the output we will get a message ‘Command completed successfully’; provided SQL statement(s) are correct else error message(s) will there. But in any case we will not be able to see the results or the column names being returned by the SQL batch or a stored procedure using NOEXEC ON statement.
e.g.create proc ，傳回 "命令已順利完成。"但事實上沒有create proc。

3.設定只對執行的QUERY視窗有效.
再開一個QUERY視窗,它就是原本的OFF了
SET FMTONLY is set during the execution phase, not during the profile phase.

注意:FATONLY ON後,要把它弄回來OFF.不然之後的create proc會失效.

為什麼SP會沒有資料行?(待)

ref
https://dotblogs.com.tw/regionbbs/2011/06/20/troubleshooting_sql_server_openrowset
ref FATONLY ON的作用
https://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/