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

 

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog do Douglas Romão

MVP Office Apps and Services | Modern Workplace and Business Applications Expert

Blog do Project

Tudo que você queria saber sobre Microsoft Project

ppm4all

by Allan Rocha

Paul's Project Server and Project Online Blog

Microsoft Project Server and Project Online (PPM)

%d bloggers like this: