If you’ve encountered error message 7325 in SQL Server “Objects exposing columns with CLR types are not allowed in distributed queries” it’s probably because you’re trying to run a distributed query against a table that contains one or more columns with CLR types.

For example, you could be querying a table that uses a geography or geometry data type in one or more of its columns. These data types are implemented as .NET common language runtime (CLR) data types in SQL Server. And as the error message says, “Objects exposing columns with CLR types are not allowed in distributed queries”.

Fortunately, there’s an easy fix for this issue. And the answer is provided with the full error message.

Getting the Error

Here’s an example of a distributed query that results in error 7325.

SELECT TOP(10) * 
FROM Homer.WideWorldImportersDW.Dimension.City;

Result:

Msg 7325, Level 16, State 1, Line 1
Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"WideWorldImportersDW"."Dimension"."City"'.

In this case I ran a distributed query against a linked server called “Homer”. I was trying to query the “Dimension.City” table on the “WideWorldImportersDW” database, but it didn’t work. Apparently the table contains a CLR type.

The error message suggests that I use a pass-through query instead:

Please use a pass-through query to access remote object '"WideWorldImportersDW"."Dimension"."City"'.

Fixing the Error – Pass-Through Query

I’m not sure if this is actually classified as “fixing” the error or simply as “working around” the error. Either way, the error message tells me to use a pass-through query so that’s what I’ll do.

Using a pass-through query allows us to run queries against remote tables that contain columns with CLR types.

So we could change the previous query into the following:

SELECT TOP(10) *  
FROM OPENQUERY(
    Homer, 
    'SELECT * FROM WideWorldImportersDW.Dimension.City'
    );

When I run that query, I get the expected results without error.

The OPENQUERY() function allows us to run a pass-through query on the specified linked server. The first argument contains the linked server name, and the second argument is the query that we want to run (enclosed in single quotes).

So you can copy the original query and paste it as the second argument. If you do this, don’t forget to remove the linked server name from the query, otherwise you’ll get another error. In my example, I had to remove the “Homer” from Homer.WideWorldImportersDW.Dimension.City, so that it became WideWorldImportersDW.Dimension.City. This is because we’re already supplying the name of the linked server in the first argument.

Finding the Culprit

As mentioned, the error message told me that I was trying to query a column with a CLR type. I can verify this by jumping over to the remote (linked) server and running the following code:

USE WideWorldImportersDW;
SELECT 
  COLUMN_NAME,
  DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'City'
  AND TABLE_SCHEMA = 'Dimension';

Result:

+----------------------------+-------------+
| COLUMN_NAME                | DATA_TYPE   |
|----------------------------+-------------|
| City Key                   | int         |
| WWI City ID                | int         |
| City                       | nvarchar    |
| State Province             | nvarchar    |
| Country                    | nvarchar    |
| Continent                  | nvarchar    |
| Sales Territory            | nvarchar    |
| Region                     | nvarchar    |
| Subregion                  | nvarchar    |
| Location                   | geography   |
| Latest Recorded Population | bigint      |
| Valid From                 | datetime2   |
| Valid To                   | datetime2   |
| Lineage Key                | int         |
+----------------------------+-------------+

We can see that the Location column has a data type of geography, which is a CLR type. This is the most likely cause of the error.