C# Stored Procedure / OpenXML Failure - Can Anyone Help..???

 

Hi,

I have some c# code which calls a SP which is erroring Basically I pass in a XML string which can be upto 5 MB is size (not sure about overflow issues here), which then calls a SP which inserts the data into a SQL table.

The c# code is as follows:

-------C#----------------------

SqlConnection conn = new SqlConnection(DBConn);



using(StreamReader sr = new StreamReader(xmlLocationString))

{

try

{

string @xmlInput = sr.ReadToEnd();

SqlCommand cmd = new SqlCommand();

cmd.Connection=conn;

cmd.CommandText = "[AddArgentinaTrades]";

cmd.CommandType = CommandType.StoredProcedure;



cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@xmlInput", SqlDbType.Text, 5120000));

cmd.Parameters["@xmlInput"].Direction = ParameterDirection.Output;

conn.Open();



cmd.ExecuteNonQuery();

}



catch(SqlException SqlExp)

{

Console.WriteLine(SqlExp.Message);

}

finally

{

conn.Close();

sr.Close();



}

}

------------------Stored Proc-----------------------

 

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddArgentinaTrades' AND Type ='P')
DROP PROCEDURE AddArgentinaTrades
GO

CREATE PROCEDURE AddArgentinaTrades
@xmlInput as text
AS

Declare @idoc  int

EXEC master.dbo.sp_xml_preparedocument @idoc OUTPUT, @xmlInput

INSERT INTO MarketRiskdev.dbo.Import_Argentina
SELECT  un_cid, tnum, snum, cid, entityid, ctype, why, comp, oc, bs, ae, cp, trd_date, set_date, mat_date, val_date,
trader, famt, price, coupon, next_coupon, last_coupon, cpnfreq, cpnrate, cpntype, daycounttype, exch_notion,
contract_spot, base_cur, year_basis, buy_currency, buy_amount, sell_currency, sell_amount, [timestamp] FROM OPENXML(@idoc, 'ArgentinaInputFile/Data',2)
WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50),
  why  varchar(50),
  comp  varchar(50),
  oc  varchar(50),
  bs  varchar(50),
  ae  varchar(50),
  cp  varchar(50),
  trd_date datetime,
  set_date datetime,
  mat_date datetime,
  val_date   datetime,
  trader  varchar(50),
  famt  float(8),
  price  float(8),
  coupon  float(8),
  next_coupon datetime,
  last_coupon datetime,
  cpnfreq  int,
  cpnrate  float,
  cpntype  int,
  daycounttype smallint,
  exch_notion smallint,
  contract_spot float(8),
  base_cur varchar(50),
  year_basis int,
  buy_currency varchar(50),
  buy_currency varchar(50),
  buy_amount float(8),
  sell_currency varchar(50),
  sell_amount float(8),
  [timestamp] varchar(50))
  

EXEC  master.dbo.sp_xml_removedocument @idoc

GO

Error Msg:

A severe error occurred on the current command.  The results, if any, should be
discarded.

Can anyone help here as I have no idea. I have tried reducing the size of XML to 5KB and still get the same error????