xml parameter to distributed query - difference between SQL2005 & SQL2012 I have inherited an old SQL2005 Enterprise database (on Windows 2003) that allows an xml parameter to a distributed query such as this:

exec sp_executesql 'exec [server].[database].dbo.sproc @Id=@Id,@PItems=@PItems output','@PId bigint, @PItems xml output', valueOf@PId, valueOf@PItems output


This no longer works in SQL2012, the error says:


  Msg 9512, Level 16, State 1, Line 1 Xml data type is not supported as
  a parameter to remote calls.


My question: is there a permission or tickbox (facet?) to allow xml parameters to distributed queries or does 2012 no longer support it?

Note: the tables do not have any columns of datatype xml. Only the parameter is xml.
It's not supported, but you can get around it..


select * from openquery(
 LinkedServerName,
 'exec [database].dbo.sproc @Id=@Id,
                       @PItems=@PItems output'',''
                       @PId bigint, 
                       @PItems xml output'', 
                       valueOf@PId, 
                       valueOf@PItems output'
)


There are many pitfalls. which are well explained here, as you are on 2012 you will want to look into executing the remote query with results set. some version of this.

SELECT * FROM OPENQUERY(LinkedServerName, 
          'EXEC msdb..sp_helpindex sysjobs
           WITH RESULT SETS ((index_name        sysname,
                              index_description nvarchar(500),
                              index_keys        nvarchar(500)))')