none
Temporal History Table across Linked Server no ROWS returned RRS feed

  • Question

  • Hi SQL Experts,

    I have a SQL linked server SQL server to SQL Server.  Security for the connection is "Be made using the Login's current security context'

    I am able to execute 

    SELECT        

    PARENT.*
    FROM  [DB].[dbo].[tablename] AS PARENT 
    INNER JOIN
              [DB].[History].[tablename] AS HIST ON HIST.primarykey = PARENT.primarykey
      WHERE  (HIST.SysEndTime >= CAST('2019-06-04 18:34:44' AS DATE ) )

    This returns 3 rows as expected.

    I execute the same statement through the linked server object.

    SELECT         

    PARENT.*
    FROM  [LINKEDSERVER].[DB].[dbo].[tablename] AS PARENT 
    INNER JOIN
              [LINKEDSERVER].[DB].[History].[tablename] AS HIST ON HIST.primarykey = PARENT.primarykey
      WHERE  (HIST.SysEndTime >= CAST('2019-06-04 18:34:44' AS DATE ) )

    No rows are returned.

    I have also tried using OPENQUERY to execute the SQL across the Linked Server, no rows returned.

    The problem is that I can not see any rows in the History table when I query it across the Linked Server object.

    I get a full set of records from the Temporal Table

    Does anyone know why the permissions on the History table are different  across a Linked Server Object?

    Does anyone know how to get around the permissions problem?

    Thanks.

    Friday, June 7, 2019 6:56 AM

All replies