Linked Postgresql server returns truncated fields when run as scheduled job

• Gordon Dooley

  SSC Rookie

  Points: 32

  More actions

  November 16, 2022 at 2:38 pm

  #4113764

  I have a SPROC that makes a call to a linked server (Postgres) and pulls back the results. The remote field is a TEXT field and is therefore cast to varchar(32000) in the query.The simplified test SPROC is:

  DECLARE @sql nvarchar(max)
  DECLARE @tsql nvarchar(max)
  SET @sql = N'SELECT abstract::varchar(32000)
  FROM xxxxxx A
  LIMIT 6 OFFSET 0'
  
  SELECT @tsql = N'SELECT * FROM OPENQUERY(linkedserver,''' + @sql + ''')'
  INSERT INTO test_scheduled – or test_manual
  EXEC (@tsql);

  When I run the SPROC manually in SSMS everything works correctly and I get a table containing six rows. If I check the length of the text in each row with LEN(Abstract) I see this:

  939

  955

  1558

  1200

  1039

  2243

  If I then run the exact same SPROC as a scheduled job in SSMS it still works but the text returned is truncated at 512 characters (or less), so the corresponding text lengths for the same six record are:

  512

  512

  511

  511

  512

  512

  (I’m not sure why two have truncated at 511 chars instead of 512 but that’s the least of my worries).

  I’m pretty sure this is due to the ODBC driver, but I can’t understand why it works in one context but not the other. Can anyone advise?

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  November 17, 2022 at 3:10 pm

  #4114357

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.