Question: 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.

Answer: 
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](http://www.sommarskog.se/share_data.html), 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)))')

```
I have found the solution to my problem. My "server name" part of the stored procedure was being loaded from a column in the database which included the port number (because it is a named instance), therefore it read

```
[Servername\Instance,Port].[database]....

```

but when I removed the port it worked. Demonstration below.

```
USE [master]
GO

CREATE DATABASE TestDatabase
go

ALTER DATABASE TestDatabase SET COMPATIBILITY_LEVEL = 90
GO

USE [TestDatabase]
go

CREATE TABLE [Table_A]([MyColumn] [varchar](100) NOT NULL)
go

INSERT INTO Table_A (MyColumn)
VALUES ('this is some text')
go

CREATE PROCEDURE [dbo].[GetSomeText] 
    @P_XmlParameter xml output
AS
BEGIN
    SET  @P_XmlParameter = (SELECT  MyColumn
                            FROM Table_A                                
                            FOR XML RAW('xmlrow'), ROOT('xmldoc'))
END
GO

CREATE PROCEDURE [dbo].[CallDistributedSproc] 
    @XmlParam xml output
AS
BEGIN

    declare @sql nvarchar(2000)
    declare @sqlparam nvarchar(2000)

    set @Sql = 'exec [' + @@SERVERNAME + '].[TestDatabase].dbo.GetSomeText @P_XmlParameter=@XmlParam output'

    set @SqlParam = '@XmlParam xml output'

    print @sql

    exec sp_executesql @Sql, @SqlParam, @XmlParam output
END
GO

declare @XmlParam xml
set @XmlParam= null
exec CallDistributedSproc @XmlParam=@XmlParam output
select @XmlParam

```