When using execute(@sql) at [linked_server] I get "Could not find server" error
I have written the following stored procedure to execute a PL/SQL script to a linked Oracle server:
ALTER PROCEDURE [dbo].[Oracle_s]
(
@sql varchar(4000)
)
AS
BEGIN
declare @result bit = 0;
set nocount on;
declare @temp nvarchar(4000) = 'SELECT * FROM OPENQUERY(LK_VPROD, ''' + REPLACE(@sql,'''', '''''') + ''')';
begin try
exec sp_executesql @temp;
--execute(@sql ) at [LK_VPROD];
set @result = 1;
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage;
set @result = 0;
end catch
set nocount off;
return @result;
END
When the code uses the script using the sp_executesql coding as above, it works.
When I switch it to the other option, it fails with the cannot find linked server error or Server is not configured for RPC.
The goal is to send a PL/SQL script that selects a number of records and returns an IEnumerable<T> to a generic interface using Dapper.
The Dapper part works directly with the Oracle server we use but we are going to the cloud with the Oracle server and the requirement is to use an SP in SQL SERVER to call the linked server to return the records list with an arbitrary input PL/SQL script as above.
The fields in sys.servers are:
name: LK_VPROD
product: OraOLEDB.Oracle
provider: OraOLEDB.Oracle
data_source: VPROD64
provider_string: OraOLEDB.Oracle
location, datalog and collation_name: null
is_linked, is_data_access_enabled, uses_remote_collation and is_remote_proc_transaction_promotion_enabled: 1
connect_timeout, query_timeout, is_remote_login_enabled, is_rpc_out_enabled, is_collation_compatible, lazy_schema_validation, is_system, is_publisher, is_subscriber, is_distributor, is_nonsql_subscriber, is_rda_server: 0
It's probably a configuration issue but this is the first time I have worked with a linked server in SSMS.