Cannot run openquery on linked server

Question

I am running a query on linked server (IBM DB2) the reason I am doing this is because it takes lot of time to execute a simple query. below are the two queries, the first one runs successfully so the issue is not with the query

 select top 10 * from [AS400TS_LNK].[TEST].[AUPRDDBF].CONTACCT where  actrno = '8971'  

  select * from openquery(AS400TS_LNK, 'select * from [TEST].[AUPRDDBF].CONTACCT where  actrno = ''8971''')

and the error I get while running the open query

OLE DB provider "DB2OLEDB" for linked server "AS400TS_LNK" returned message "Token *N was not valid. Valid tokens: *N. SQLSTATE: 42601, SQLCODE: -104".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "select * from [AS400TS_LNK].[TEST].[AUPRDDBF].CONTACCT where actrno = '8971'" for execution against OLE DB provider "DB2OLEDB" for linked server "AS400TS_LNK".

Just a wild guess here, but easy to test. Try removing the square brackets from the openquery statement... — user1429080
– user1429080, Commented Oct 13, 2017 at 6:29
As @user1429080 mentioned, using square brackets to quote identifiers is a SQL Server thing; send standard SQL to the remote server, using double quotes for identifiers when necessary (which is not the case here anyway). — mustaccio
– mustaccio, Commented Oct 13, 2017 at 12:36

Bacon Bits · Accepted Answer · 2017-10-13 12:50:10Z

1

Try this:

select * 
from openquery(AS400TS_LNK, 'select * from TEST.AUPRDDBF.CONTACCT where actrno = ''8971''')

Or, failing that, try this:

select * 
from openquery(AS400TS_LNK, 'select * from TEST.AUPRDDBF.CONTACCT') 
where actrno = '8971'

answered Oct 13, 2017 at 12:50

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

Cannot run openquery on linked server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related