How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps