sql server return parameter size.

[Guest]

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

 

[WenYuan Wang]

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

 

[Guest]

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

Are their any work arounds?

 

[WenYuan Wang]

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

 

[Guest]

thanks,

 

[WenYuan Wang]

Your are welcome, Chuck,

Have a great day.
Wen Yuan