XML insert/update to sp

[timmoser]

I have a C# program that sends and XML file to an SP that updates/inserts the data into the appropriate table. Has been working fine since I put it together a few months ago. Now I want to add another update/insert into the process but im getting an error. All of the code is the same; the data, tables and SP are different. To narrow the error possibilities down I removed all but one field from the XML and don't even do an insert/update. I still can’t get it to work.

This is the SP ...

CREATE PROCEDURE dwh_sr_updates @data nText
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @data
-- .. normally would do the update here ..
EXEC sp_xml_removedocument @hDoc

This is the XML created by C#
(It is not valid XML outside of C# but like I said this works for the original update)

<NewDataSet>\r\n<Data rowid=\"907490\" />\r\n</NewDataSet>

This is my C# code ...

static void Save(DataSet CurDataSet, SqlConnection CurSqlConnection, string SQLCommand){
DataTable CurDataTable = CurDataSet.Tables["Data"];
System.Text.StringBuilder CurStringBuilder = new System.Text.StringBuilder(9000);
System.IO.StringWriter CurStringWriter = new System.IO.StringWriter(CurStringBuilder);
foreach( DataColumn CurDataColumn in CurDataTable.Columns){
	CurDataColumn.ColumnMapping = System.Data.MappingType.Attribute;
}
CurDataSet.WriteXml(CurStringWriter);
SqlCommand CurSqlCommand = new SqlCommand();
CurSqlCommand.Connection = CurSqlConnection;
CurSqlCommand.CommandType = CommandType.StoredProcedure;
CurSqlCommand.CommandText = SQLCommand;
CurSqlCommand.Parameters.Add( new SqlParameter( "@data", System.Data.SqlDbType.NText));
CurSqlCommand.Parameters[0].Value = CurStringBuilder.ToString();
CurSqlCommand.ExecuteNonQuery();
}

The error I get isn’t very descriptive ”System Error” hehe. I can’t really run it outside of the C# application because I would have to change the XML which would not be reality.

If anyone has any idea what I can do or what the problem is I would really like to hear it.

Thanks for your help!

 

[timmoser]

I'm an idiot; I never set permission on the SP for the application to exec!!!

Thanks