0

I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query

select * from openquery(SALESFORCE,'select closeddate
                                    from case 
                                    where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")');

Output:

OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error: MALFORMED_QUERY: 
where closeddate > convert(datetime2, "2020-01-01
                           ^
ERROR at Row:3:Column:28
Bind variables only allowed in Apex code".
Msg 7320, Level 16, State 2, Line 21
Cannot execute the query "select closeddate
                                    from case1 
                                    where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE". 

Completion time: 2021-04-22T03:00:25.5943467-04:00

Where I am getting wrong

1 Answer 1

0

I think the problem is datetime2 is not a postgres data type:

convert(datetime2, "2020-01-01 00:00:00.0000000")

The openquery statement exucute in the target database the query between '' so it must be written in the targewt dialect

try this

select * from openquery(SALESFORCE,'select closeddate from case where closeddate > cast(''2020-01-01'' as date)')

3
  • But it is a SQL Server datatype - and it's SQL Server that's being queried?
    – Vérace
    Jun 13, 2022 at 18:23
  • If I have understood right, the linked server is pointing to a postgesql server. So the query sent by the openquery command to the server behind linked server has to be in target server dialect.
    – MBuschi
    Jun 13, 2022 at 20:52
  • My apologies - you are correct - on rereading the question (which I should have done in the first place!), I realise my error. Best...
    – Vérace
    Jun 14, 2022 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.