USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
http://msdn2.microsoft.com/en-us/library/ms189915.aspx
http://www.msdner.com/dev-archive/84/19-85-847101.shtmSELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY([LINKED SERVER NAME HERE],'
SELECT
Cast(XML_Data as Varchar) as XML_Data
FROM
[DATABASE NAME].[SCHEMA].[TABLE NAME]'
) A
Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.
StephenDudzic at 2007-9-3 22:09:22 >
"Re: XML data type not supported in Distributed Queries
This is a limitation in SQL Server 2005. Columns of xml type or CLR type cannot be queried directly or referenced from one server to another - this means the following:
- You cannot use a table or view that contains xml or clr type as 4-part name in your query
- You need to cast the column to either nvarchar(max) or varbinary(max) or other appropriate type to use
- If you have a table that has xml type for example then you need to create a view that contains all columns other than xml and query it instead. Or you can issue a pass-through query using OPENQUERY with the appropriate columns only."
