Pass a binary variable to a linked server using OPENQUERY

Question

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.

first query returns a varchar type (assigned to '@To_LSN') ?? SELECT @To_LSN = ReturnValue FROM OPENQUERY(LNK_SQL_SERVER, 'SELECT MY_DB.sys.fn_cdc_get_max_lsn() AS ReturnValue;'); Then in the second query you pass '@To_LSN'. First query returned type varchar? — MZM, Dec 17 '22 at 16:30
Sorry! the first query returns a binary value which is assigned to @To_LSN bianary(10), and then the same variable/value is input to the second query. — MSBI-Geek, Dec 17 '22 at 16:40
Does this answer your question? [including parameters in OPENQUERY](https://stackoverflow.com/questions/3378496/including-parameters-in-openquery) — GSerg, Dec 17 '22 at 16:44
`throws error as conversion between varchar and binary fails` - so convert properly, such as [e.g. with](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#binary-styles) `convert(varchar(10), @To_LSN, 1)`. — GSerg, Dec 17 '22 at 16:49
Thanks @GSerg, I tried converting between varchar and binary, but I was worried it may have any unexpected consequences, only because I never worked with binary conversions before. But thanks for taking time to help me. — MSBI-Geek, Dec 17 '22 at 17:24

score 2 · Accepted Answer · answered Dec 17 '22 at 17:08

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;

Thanks much Aaron for a simple and clear answer with examples. It worked perfect. — MSBI-Geek, Dec 17 '22 at 17:22

Pass a binary variable to a linked server using OPENQUERY

1 Answers1