XML data type is not supported in distributed queries

Hello guys, today I’ll talk about queries with Linked Server and XML Data.

When you try to make a SQL Server query using Linked Server, and the table has an XML Data you will receive the following message:

ErrorSQL

It easy solves the problem, you need to use an OPENQUERY like this:

SELECT

Name,
Description,
XML fields…
Other fields…

FROM

OPENQUERY ([Linked Server Name Here],

 ‘

SELECT

nvarchar1 as Name,
nvarchar2 as Description,
XML fields…..
Other fields…

FROM

[DATABASE NAME].[SCHEMA].[TABLE NAME 1] as TB1

INNER JOIN

[DATABASE NAME].[SCHEMA].[TABLE NAME 2] as TB2 ON TB1.ID = TB2.ID

WHERE ….

  ‘

)AliasName
Where …
Group by …

So, you just create a new select using OPENQUERY called the Linked Server and inside you use the “original” query.

Enjoy, see you!

 

#ProActive  #JoinUs

Diego Pereira

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog do Project

Tudo que você queria saber sobre Microsoft Project, Project Server e Project Online

ppm4all

by Allan Rocha

Paul's Project Server and Project Online Blog

Microsoft Project Server and Project Online (PPM)

%d bloggers like this: