-1

I am reading the contents of a field from a linked server with an openquery statement without any problem. The field's data type is char(1). However if I try to convert to a different data-type, the openquery fails.

Neither of the statements below will work:

select * from  FROM OPENQUERY(linkedserver, 
  'select CONVERT (NVARCHAR(),QCTL.GL_TRANS.TRAN_TYPE) AS TRAN_TYPE'

select * from  FROM OPENQUERY(linkedserver, 'select 
  IIF(QCTL.GL_TRANS.TRAN_TYPE = ''C'', -1 * QCTL.GL_TRANS.AMOUNT, 
  QCTL.GL_TRANS.AMOUNT) AS AMOUNT'
Thom A
  • 88,727
  • 11
  • 45
  • 75
Leon
  • 1
  • 2

1 Answers1

0

You have to either specify a length when converting or remove the ().

select * from FROM OPENQUERY(linkedserver, 'select CONVERT(NVARCHAR(1),QCTL.GL_TRANS.TRAN_TYPE) AS TRAN_TYPE'

select * from FROM OPENQUERY(linkedserver, 'select CONVERT(NVARCHAR,QCTL.GL_TRANS.TRAN_TYPE) AS TRAN_TYPE'
mvisser
  • 652
  • 5
  • 11
  • I did try that before, but it also does not work. The problem occurs only when it is part of the openquery statement. In other words it will work like this: select convert(nvarchar,TRAN_TYPE) from openquery(linkedserver, ' select TRANTYPE FROM...') – Leon Aug 24 '18 at 11:05
  • Then why cant you just convert it when you get your dataset as you did above? – mvisser Aug 24 '18 at 11:19
  • I need to do a calcution in the openquery statement (if the tran_type is 'c' then change the amount field by multiplying by minus 1) . Then, in the outer select query the calculated amounts are summed (both positive and negative amounts. – Leon Aug 24 '18 at 11:23
  • Just use a Store Procedure instead, if you doing calculations across networks. SELECT * FROM OPENQUERY(linkedserver, 'EXEC Database.dbo.StoredProcedure') – mvisser Aug 24 '18 at 11:29