I have a sproc which looks like that:
CREATE PROCEDURE xyz ( @param1 int, @param2 int, @param3 xml OUTPUT )
I'm trying to access it using OLEDB:
DEFINE_COMMAND_EX( Accessor, _T( "{ ? = CALL xyz( ?,?,? ) }" ) )
BEGIN_PARAM_MAP( Accessor )
SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
COLUMN_ENTRY(1, _result)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(2, param1)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(3, param2)
SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
COLUMN_ENTRY(4, param3)
END_PARAM_MAP()
What happens is OLEDB will take a parameter and assume it's nvarchar(4000). Sql query profiler will show that OLEDB is trying to do this:
declare @p3 nvarchar(4000)
set @p3=NULL
exec xyz -1,-1,@p3 output
select @p3
Which obviously fails with Implicit conversion from data type xml to nvarchar(4000) is not allowed.
. Is there a workaround? I know, making XML column a resultset will work, but is there a way to make it work as an output parameter?