tags:

views:

168

answers:

0

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?