T-sql – Getting data from Postgres through Linked server of SQL

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;

Even better, parameterize it:

DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql 
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME', 
  @t = @t, 
  @c = @c;

Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will.

Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.