ntext saving problem

[krotha]

I am calling stored procedure from my .net application to save a record into a table. I got following error message.
Our server is Windows 2003 server and SQL Server is SP4. My stored procedure takes ntext as a input parameter and program is sending an XML string through the input parameter. When I trace through SQL Profiler the calls comes into procedure and aborts immediately after the call and doesn’t go through the procedure at all.


I think the problem is caused by SqlCommandBuilder.DeriveParameters method.
When we use SqlCommandBuilder.DeriveParameters to derive parameter for a SP which has ntext as in parameter , it is always discovering it as nvarchar.

When I changed the parameter to ntext to nvarchar it works fine. Any idea why the deriveParameters method finding the ntext parameter as nvarchar.

This is the error I get.

An error occurred in the following module: table1.Save
Source: DBAccess
Message: A severe error occurred on the current command. The results, if any, should be discarded.
Stack Trace: at DATA.DBAccess.ExecSP(String SPName, ArrayList ParamValues)
at table1.Save(DataSet dsTable1)
Target Site: Void ExecSP(System.String, System.Collections.ArrayList)

 

[Joulius]

Normally you should add a XmlDocument as an NText parameter to a SqlCommand this way:

oCmd.Parametes.Add(new SqlParameter("@XmlData", SqlDbType.NText, XmlData.OuterXml.Length, ParameterDirection.Input, true, 0, 0, "XmlData", DataRowVersion.Current, XmlData.OuterXml));

I think that the SqlCommandBuilder.DeriveParameters method fails to create the @XmlData parameter correctly and, to be exact, I think that the length parameter of the SqlParameter constructor is incorrect. Instead of setting XmlData.OuterXml.Length, it tries to set XmlData.ToString().Length.
This is just a guess, so you will have to debug this.

I hope this helps.