SQL Server - How to pass a binary variable to a linked server using OPENQUERY-CodePudding

I'm using SQL Server 2019 and I use a linked server to connect to another SQL Server.

I need to run a system function on the linked server, I'm using OPENQUERY to run the function on the linked server which is working fine in many cases. However one of the functions expects a BINARY parameter. I have got no clue on how to pass a binary variable to the linked server using OPENQUERY. Please can someone help? see my code sample below.

DECLARE @To_LSN  AS binary(10);
DECLARE @To_LSN_Timestamp datetime;

SELECT @To_LSN  = ReturnValue 
FROM OPENQUERY (LNK_SQL_SERVER, 
                'SELECT MY_DB.sys.fn_cdc_get_max_lsn() AS ReturnValue;');       

SELECT @To_LSN_Timestamp = ReturnValue 
FROM OPENQUERY (LNK_SQL_SERVER, 
                'SELECT MY_DB.sys.fn_cdc_map_lsn_to_time(@To_LSN) AS ReturnValue;');        

-- it won't accept the @To_LSN as a parameter.

In the second query above the function expects a binary parameter (@To_LSN), so if I try to split the query as a dynamic sql it throws error as conversion between varchar and binary fails.

CodePudding user response：

If you just need the LSN timestamp, why not do it in a single step and avoid any string or binary conversions/concatenation?

SELECT @To_LSN_Timestamp = ReturnValue 
  FROM OPENQUERY
  (
    LNK_SQL_SERVER, 
    N'SELECT MY_DB.sys.fn_cdc_map_lsn_to_time(
        MY_DB.sys.fn_cdc_get_max_lsn()
      ) AS ReturnValue;');

Even better:

EXEC LNK_SQL_SERVER.MY_DB.sys.sp_executesql 
  N'SELECT @rv = sys.fn_cdc_map_lsn_to_time(
        sys.fn_cdc_get_max_lsn());', 
  N'@rv datetime OUTPUT',
    @rv = @To_LSN_Timestamp OUTPUT;

If you need both the LSN and the timestamp outside, then:

EXEC LNK_SQL_SERVER.MY_DB.sys.sp_executesql 
  N'SELECT @lsn = sys.fn_cdc_get_max_lsn();
    SELECT @rv  = sys.fn_cdc_map_lsn_to_time(@lsn);', 
  N'@lsn binary(10) output, @rv datetime OUTPUT',
    @lsn = @To_LSN           OUTPUT,
    @rv  = @To_LSN_Timestamp OUTPUT;