I have a very basic stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[SP1] (@PARAM1 CHAR(40))
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'SELECT * FROM OPENQUERY(ORCL_A, ''SELECT * FROM SCHEMA1.TABLE1 WHERE FIELD1 = '''''+ @PARAM1 + ''''' '')'
EXEC ( @SQL )
If I run the sql straight I get a row back. If I run
exec SP1 'blah'
I get nothing back. It doesn't error, the fields are there, but there is no record returned.
I have other similar SP's that point to other linked db's that work fine with the sp call. I must be missing something very simple. The permissions all look okay, public user has exec grant on the sp and the table on the linked server has grant to the user in the linked server.
What am I missing?