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
– MZM, Commented Dec 17, 2022 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
– MSBI-Geek, Commented Dec 17, 2022 at 16:40
Does this answer your question? including parameters in OPENQUERY — GSerg
– GSerg, Commented Dec 17, 2022 at 16:44
throws error as conversion between varchar and binary fails - so convert properly, such as e.g. with convert(varchar(10), @To_LSN, 1). — GSerg
– GSerg, Commented Dec 17, 2022 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
– MSBI-Geek, Commented Dec 17, 2022 at 17:24

Aaron Bertrand · Accepted Answer · 2022-12-17 17:08:59Z

2

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;

answered Dec 17, 2022 at 17:08

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MSBI-Geek Over a year ago

Thanks much Aaron for a simple and clear answer with examples. It worked perfect.

Collectives™ on Stack Overflow

Pass a binary variable to a linked server using OPENQUERY

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related