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, 1024));
cmd.Parameters["@xmlInput"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
}
catch(SqlException SqlExp)
{
Console.WriteLine(SqlExp.Message);
}
finally
{
conn.Close();
sr.Close();
}
}
------------------Stored Proc-----------------------
F 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 erorr????
-
Please ignore the missing "I" in the "IF EXISTS" with regards to the SP. That is not the problem; that is me not being able to copy and paste.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@xmlInput", SqlDbType.Text, 1024));
Possibly 1024(1KB) should be 5120000 (5MB)?
-
Hi David,
No Same error!
Thanks for the reply.
any other ideas????
-
Anyone any ideas ...? I'm certainally confused
-
Temporarily comment out the INSERT statement.
If that doesn't work try supplying a small XML file in the correct format.
What happens if you change the parameter to NTEXT rather than TEXT?
-
Are you sure you've got the direction set properly? It seems as if the XML you are sending to the stored procedure should be defined as an input parameter (the direction is relative to the result set/SP, not your c# code).
cmd.Parameters["@xmlInput"].Direction = ParameterDirection.Output;
Try commenting this line out.
Also, have you executed the SP from Query Analyzer to verify it works properly?
-
Changing direction of Parameter to input gives me an error of:
Procedure 'AddArgentinaTrades' expects parameter '@xmlInput', which was not supplied.
But I have added the parameter?
Any ideas?
-
Also removing the Insert will stop the insert.
The stored proc works in Query Analyzer when providing the xml as a string.
-
any ideas ???
-
My point about removing the INSERT was to prove that the XML shredding actually works and could come back as a recordset.
If this was the case the chances are that there would be a mismatch between the fields in the table being inserted into and the XML being selected from.
-
Can u give your Table Structure & XML String u r passing to SP
Regards
-
Hi Manish,
Thanks for helping out. It's most likely a silly error on my part as i cant program to save my life.
Here is the table sql:
CREATE TABLE [dbo].[Import_Argentina] (
[un_cid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tnum] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[snum] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[entityid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[why] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bs] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ae] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trd_date] [datetime] NULL ,
[set_date] [datetime] NULL ,
[mat_date] [datetime] NULL ,
[val_date] [datetime] NULL ,
[trader] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[famt] [float] NULL ,
[price] [float] NULL ,
[coupon] [float] NULL ,
[next_coupon] [datetime] NULL ,
[last_coupon] [datetime] NULL ,
[cpnfreq] [int] NULL ,
[cpnrate] [float] NULL ,
[cpntype] [int] NULL ,
[daycounttype] [smallint] NULL ,
[exch_notion] [smallint] NULL ,
[contract_spot] [float] NULL ,
[base_cur] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year_basis] [int] NULL ,
[buy_currency] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_amount] [float] NULL ,
[sell_currency] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_amount] [float] NULL ,
[timestamp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Here is the XML:
<?
xml version="1.0" encoding="utf-8"?>
<
ArgentinaInputFile xmlns="urn:ArgentinaSchema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:ArgentinaSchema.xsd C:\Path\ArgentinaSchema.xsd">
<Data>
<un_cid>str0</un_cid>
<tnum>1</tnum>
<snum>str1</snum>
<cid>String2</cid>
<entryid>string3</entryid>
<ctype>string4</ctype>
<why>string5</why>
<comp>string6</comp>
<oc>string7</oc>
<bs>string8</bs>
<ae>string9</ae>
<cp>string10</cp>
<trd_date>10/12/2005</trd_date>
<set_date>11/01/2005</set_date>
<mat_date>14/12/2005</mat_date>
<val_date>03/01/2006</val_date>
<trader>string11</trader>
<famt>1.3</famt>
<price>1.4</price>
<coupon>1.5</coupon>
<next_coupon>01/01/2005</next_coupon>
<last_coupon>02/02/2006</last_coupon>
<cpnfreq>12</cpnfreq>
<cpnrate>1.6</cpnrate>
<cpntype>15</cpntype>
<daycounttype>16</daycounttype>
<exec_notion>17</exec_notion>
<contract_spot>1.7</contract_spot>
<base_cur>str12</base_cur>
<year_basis>15</year_basis>
<buy_currency>str13</buy_currency>
<buy_amount>1.9</buy_amount>
<sell_currency>str14</sell_currency>
<sell_amount>2.1</sell_amount>
<timestamp>23:10:00</timestamp>
</Data>
<Data>
<un_cid>str0</un_cid>
<tnum>1</tnum>
<snum>str1</snum>
<cid>String2</cid>
<entryid>string3</entryid>
<ctype>string4</ctype>
<why>string5</why>
<comp>string6</comp>
<oc>string7</oc>
<bs>string8</bs>
<ae>string9</ae>
<cp>string10</cp>
<trd_date>10/12/2005</trd_date>
<set_date>11/01/2005</set_date>
<mat_date>14/12/2005</mat_date>
<val_date>03/01/2006</val_date>
<trader>string11</trader>
<famt>1.3</famt>
<price>1.4</price>
<coupon>1.5</coupon>
<next_coupon>01/01/2005</next_coupon>
<last_coupon>02/02/2006</last_coupon>
<cpnfreq>12</cpnfreq>
<cpnrate>1.6</cpnrate>
<cpntype>15</cpntype>
<daycounttype>16</daycounttype>
<exec_notion>17</exec_notion>
<contract_spot>1.7</contract_spot>
<base_cur>str12</base_cur>
<year_basis>15</year_basis>
<buy_currency>str13</buy_currency>
<buy_amount>1.9</buy_amount>
<sell_currency>str14</sell_currency>
<sell_amount>2.1</sell_amount>
<timestamp>23:10:00</timestamp>
</Data>
</
ArgentinaInputFile>
Latest C# code:
public static void ImportData()
{
string xmlLocationString = GetConfigData("XMLDocumentPath");
string DBConn = @"Server=Your DB Server";
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.Input;
conn.Open();
cmd.ExecuteNonQuery();
}
catch(SqlException SqlExp)
{
Console.WriteLine(SqlExp.Message);
}
finally
{
conn.Close();
sr.Close();
}
}
}
Thanks for your help!!!!!
-
Your CREATE TABLE statement defines the fifth column as entityid, but in your XML it is named entryid. Since entityid is NOT NULL, the insert failed. After changing the tags in the XML to <entityid> and </entityid>, the insert worked.
-
Hi mkeast ,
Your right thanks for the input. I have made the change and re reun the code from Visual Studio but still getting the same error of:
Procedure 'AddArgentinaTrades' expects parameter '@xmlInput', which was not supp
lied.
by SP is as follows:
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
SELECT * FROM dbo.Import_Argentina
GO
I have stepped through the code to check the name of the input parameter is correct and it is!
I have updated the xml and checked the path to the xml but still no solution. I dont' see how it works for you? It must be something silly on my side but I cannot see it?
So still stuck....
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply