|
|
Error Message
Server: 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 '<Object Name>'.
Causes
This error is usually encountered when performing a SELECT query on a linked server and the table being queried contains a GEOGRAPHY or GEOMETRY column.
|
To illustrate on how this error can be encountered, assume there’s a [dbo].[Location] table that contains a GEOGRAPHY column in the SQLServerHelper database in the TestServer server:
-- The following table is created in the SQLServerHelper database in the TestServer server
CREATE TABLE [dbo].[Location] (
[LocationID] INT NOT NULL IDENTITY(1, 1),
[LocationName] VARCHAR(100),
[LocationGeography] GEOGRAPHY
)
GO
Then from another server, the TestServer is defined as a linked server. To retrieve the list of locations from the [dbo].[Location] table from the second server, the following SELECT statement is then issued:
-- The following SELECT statement is executed on another server where
-- TestServer is defined as a linked server:
SELECT *
FROM [TestServer].[SQLServerHelper].[dbo].[Location]
Executing the SELECT statement will generate the following error message:
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 '"SQLServerHelper"."dbo"."Location"'.
Solution / Work Around:
To overcome this error, the OPENQUERY function will be used. The OPENQUERY function executes a specified pass-through query on the specified linked server wherein the server is an OLE DB data source. The OPENQUERY can be referenced in the FROM clause of a query as if it was a table name. Although the query passed to the OPENQUERY function may return multiple result sets, the function will only return the first one.
The syntax of the OPENQUERY function is as follows:
OPENQUERY( <linked_server>, '<query>' )
The <linked_server> parameter is an identifier representing the name of the linked server. The <query> parameter is the query string to be executed in the linked server.
To use OPENQUERY to overcome this error message, the following statement can be used to retrieve the contents of a table that contains a GEOGRAPHY or GEOMETRY column from the linked server:
SELECT *
FROM OPENQUERY([TestServer], 'SELECT * FROM [SQLServerHelper].[dbo].[Location]') [Loc]
|