I have a sproc that returns xml data in an output parameters and I get an
error.
It works fine within the SQL05 environment but barfs in VS05.
Are their any fixes or work arounds?


SqlCommand cmd = new SqlCommand("web.SurveyAndTransform", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p0 = new SqlParameter("@SurveyID", SqlDbType.Int);
            p0.Direction = ParameterDirection.Input;
            p0.Value = surveyID;
            cmd.Parameters.Add(p0);

            SqlParameter p1 = new SqlParameter("@xmlData", SqlDbType.Xml);
            p1.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(p1);

            SqlParameter p2 = new SqlParameter("@xslt", SqlDbType.Xml);
            p2.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(p2);

            conn.Open();
            cmd.ExecuteNonQuery();

ERROR String[1]: the Size property has an invalid size of 0.




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [web].[SurveyResultAndTransform]
    (
    @SurveyID int,
    @xmlData xml OUT,
    @xlst xml OUT
    )

AS
    SET NOCOUNT ON


set @xlst = (
SELECT SurveyTransforms.Transform

FROM  SurveyProfiles
INNER JOIN  Surveys ON SurveyProfiles.SurveyProfileID =
Surveys.SurveyProfileID
INNER JOIN  SurveyTransforms ON SurveyProfiles.SurveyTransformIdReport =
SurveyTransforms.SurveyTransformID
where SurveyID=@SurveyID) ;



--Now create the XML results

with
  question ( QuestionID ) as
      (Select distinct  QuestionID  From SurveyResults Where
SurveyID=@SurveyID),          
  results ( QuestionID, Answer, AnswerCount) as
        (Select  QuestionID, Answer, Count(Answer)
              From SurveyResults
              Where SurveyID=@SurveyID
              group by questionid, answer)

select SurveyName, SurveyQuestions,
(
Select  question.QuestionID, results.Answer, results.AnswerCount
  From question  join results
  on question.questionid =results.questionid
for xml auto,   type
)QuestionResults into  #TempResultTable
from SurveyProfiles Join
      Surveys on  SurveyProfiles.SurveyProfileID = Surveys.SurveyProfileID
WHERE  SurveyID=@SurveyID ;

set @xlst = (
select * from   #TempResultTable
for xml Raw('Results'), Elements)

drop table #TempResultTable

Hi Chuck,

As far as I know, this is an issue with SqlDbType.xml. The work around is
set the size property for Parameter.
For example.
SqlParameter p1 = new SqlParameter("@xmlData", SqlDbType.Xml);
p1.Direction = ParameterDirection.Output;
p1.Size = 1;
cmd.Parameters.Add(p1);

By the way, I tested your code on my machine. I noticed that you have
mis-typed the ParameterName for @xlst in Stored Procedure.
[@xlst-@xslt]

@xlst xml OUT
SqlParameter p2 = new SqlParameter("@xslt", SqlDbType.Xml);


Please feel free to reply me if you have anything unclear. I'm glad to work
with you.
Hope this helps!
Wen Yuan

I have no idea of the size.
The data is in the database and the size could vary greatly.

Are their any work arounds?

Hi Chuck,
Thanks for your reply.

I checked in the buglist and figure out the issue. It's a product issue.
The root cause of this issue is the problem in SqlParameter.Validate() --
the check for output types + size at the top of the method doesn't exempt
non-fixed-length types which don't require a user-supplied size value, i.e.
XML, TEXT, NTEXT and IMAGE.

The work around is to set *any* positive size value (not specifying 0) to
the property for Parameter. Note: The size value is not related to XML
filed in your database, because XML field is non-fixed-length types.

Please feel free to reply me if you have any further question and we will
follow up.
Best regards.
Wen Yuan

thanks,

Your are welcome, Chuck,

Have a great day.
Wen Yuan

Simple way of finding whether a certain value is in a DataSet column
Data Sources window and data access library
Critique My SQL
Datagrid does not update datasource efficiently
Using a Variable within a Connection String