If you use a stored procedure (SP) when calling a Linked Server, and the SP or script contains an Insert Into statement to store data in the tempdb table, the Linked Server may encounter a “metadata could not be determined” error. To resolve this issue, add SET NOCOUNT ON; before the script and define the output explicitly using WITH RESULT SETS().
Example:
SELECT * FROM OPENQUERY ([LinkServerName], 'SET NOCOUNT ON; EXECUTE [repository].[SpName] -2 WITH RESULT SETS((EventSource nvarchar(255), Module nvarchar(255), EventTimeStamp datetime, Severity nvarchar(50), Description nvarchar(max), IsSMS bit))')
This prevents the metadata error during execution.
Author Profile
Working hard for something we don’t care about is called stress, and working hard for something we love is called passion and data engineering is my passion.