0

I'm trying to identify new data on a postgres database that does not yet exist on our local SQL Server 2017 so I can download the new data onto our SQL Server. To identify this, I'm connecting to the postgres db using a linked server and then using openquery.

I've inserted the MAX datetime2(7) from our local SQL "events" table into a temp table called #latest:

CREATE TABLE #latest (latest DATETIME2(7))

latest
2023-01-26 14:40:19.1470000

There is a "time" column within the "event" table in the linked server with a datatype of timestamp with time zone. Running this code returns successfully:

    DECLARE @Query          NVARCHAR(MAX)
    SELECT
        @Query = '
        SELECT
            [time],[eventID]
        FROM OPENQUERY(
        [PostgresServer]
        ,''
            select 
                "time",
                "eventID"
            from "event" t 
            limit 1
        ''
        )' 
    FROM #latest

    EXECUTE sp_executesql @Query
time eventID
2022-11-17 11:05:17.2450000 730d544e-de4b-47a7-b8d0-80742dc4240d

However, when I try to add a where clause, I get an error:

    DECLARE @Query          NVARCHAR(MAX)
    SELECT
        @Query = '
        SELECT
            [time],[eventID]
        FROM OPENQUERY(
        [PostgresServer]
        ,''
            select 
                "time",
                "eventID"
            from "event" t 
            where "time" > '[latest]'
            limit 1
        ''
        )' 
    FROM #latest

    EXECUTE sp_executesql @Query

Msg 102, Level 15, State 1, Line 33 Incorrect syntax near 'latest'.

I added + to each side of [latest] but then I started getting operator errors:

The data types varchar and datetime2 are incompatible in the add operator.

So I've ended up with the below:

where "time" > '+CAST([latest] AS NVARCHAR(100))+'

But I get this error:

Msg 7399, Level 16, State 1, Line 22 The OLE DB provider "MSDASQL" for linked server "PostgresServer" reported an error. The provider did not give any information about the error.

Msg 7350, Level 16, State 2, Line 22 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PostgresServer".

Is there a better way I can identify new data on the linked server? Or fix the error?!

thedegatron
  • 1

1 Answers1

0

I think I've found the answer here: SQL Server : use datetime variable in openquery

needed to add a varchar variable instead of using the datetime column in my temp table :)

thedegatron
  • 1