March 20, 2013 at 2:06 pm
Hi,
I've table as follows,
CREATE TABLE [dbo].[majikanAG_subMajikan_1](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[batch_Id] [uniqueidentifier] NOT NULL,
[icNo (Baru)] [varchar](100) NULL,
[icNo (Lama)] [varchar](100) NULL,
[payerNme] [varchar](300) NULL,
[zakatAmount] [decimal](10, 2) NULL,
[subMajikan] [varchar](100) NULL,
CONSTRAINT [PK__majikanA__51EFEBF8002AF460] PRIMARY KEY CLUSTERED
(
[idx] ASC,
[batch_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
My Stored Procedure as follows,
CREATE PROCEDURE [dbo].[addAGSummary_SubMajikan_Process1]
@agItem xml,
@batch_Id uniqueidentifier output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set transaction isolation level repeatable read
Begin transaction
Begin Try
select @batch_Id=NEWID()
insert into majikanAG_subMajikan_1(batch_Id, [icNo (Baru)], [icNo (Lama)],
payerNme, zakatAmount, subMajikan)
select @batch_Id,
a.b.value('icNo[1]','varchar(200)') as icNo, --as input1,
a.b.value('icNoOld[1]','varchar(15)') as icNoOld, --as input2,
upper(a.b.value('payerNme[1]','varchar(100)')) as payerNme, --as input3,
--a.b.value('amt[1]','decimal(10,2)') as amt, --as input4,
a.b.value('amt[1]','varchar(100)') as amt, --as input4,
a.b.value('subCd[1]','varchar(100)') as subCd --as input5,
from
@agItem.nodes('/data/ag') a(b)
COMMIT transaction
End Try
Begin Catch
-- Whoops, there was an error
--IF @@TRANCOUNT > 0
ROLLBACK transaction
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
End Catch
END
There are 2 scenario
1- If @agItem did not have so much data (1000 records), the stored procedure run well
2- If @agItem have a so much data (10000 records), the stored procedure cannot process as expected
Why no (2) is happening? Did XML Data Type is not suitable for 10000 records?
Please help. I'm stuck
March 20, 2013 at 5:27 pm
Before executing procedure, what is len(@agItem)? Maybe it is truncated before the procedure.
March 20, 2013 at 10:03 pm
Vedran Kesegic (3/20/2013)
Before executing procedure, what is len(@agItem)? Maybe it is truncated before the procedure.
yep. May be it is truncated
My application is ASP.NET. Here the code,
XDocument doc = new XDocument(
new XDeclaration("1.0", "utf-8", "yes"),
new XElement("data"));
//Flag to skip header line of text file
bool isHeaderLine = true;
//string xmlData = "<data>";
string _strFileLocation = MapPath("~/Content/AG-Persekutuan-Bln-09-2011.txt");
string batch_Id = string.Empty;
try
{
//System.Threading.Thread.Sleep(5000);
//myAG objAG = new myAG();
using (StreamReader sr = new StreamReader(_strFileLocation))
{
while (sr.Peek() >= 0)
{
string currentLine = sr.ReadLine();
//Skip first line of file
if (isHeaderLine)
{
isHeaderLine = false;
continue;
}
XElement xmlData = new XElement("ag",
new XElement("icNo", currentLine.Substring(20, 6) + "-" +
currentLine.Substring(26, 2) +
"-" + currentLine.Substring(28, 4)),
new XElement("icNoOld", currentLine.Substring(20, 12)),
new XElement("payerNme", currentLine.Substring(51, 40)),
new XElement("amt", currentLine.Substring(92, 9)),
new XElement("subCd", currentLine.Substring(6, 4))
);
doc.Element("data").Add(xmlData);
}
}
SqlConnection con = new SqlConnection(SQLConn);
con.Open();
SqlCommand cmd = new SqlCommand("addAGSummary_SubMajikan_Process1", con);
cmd.CommandTimeout = 20;
cmd.CommandType = CommandType.StoredProcedure;
//send value to sp
cmd.Parameters.AddWithValue("@agItem", doc.ToString(SaveOptions.DisableFormatting));
SqlParameter newSqlParam = new SqlParameter();
newSqlParam.ParameterName = "@batch_Id";
newSqlParam.SqlDbType = SqlDbType.UniqueIdentifier;
newSqlParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(newSqlParam);
cmd.ExecuteNonQuery();
batch_Id = cmd.Parameters["@batch_Id"].Value.ToString();
//}
//Response.Redirect("_longRunningResult.aspx?batch_Id=" + Server.UrlEncode(batch_Id) + "", false);
lblMsg.Text = "Successful transactions....";
}
catch (Exception ex)
{ lblMsg.Text = ex.Message; }
How to solve the truncate issue on my - cmd.Parameters.AddWithValue("@agItem", doc.ToString(SaveOptions.DisableFormatting));
I'm stuck
March 21, 2013 at 1:25 am
Try using "new SqlXml(...)" instead of converting doc to string.
March 21, 2013 at 1:37 am
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ac074ccb-1a52-4bda-a032-c874d3ba8ce5/[/url]
cmd.Parameters.Add("@Value", SqlDbType.Xml).Value = doc.ToString();
March 22, 2013 at 1:22 pm
I think XML Data Type cannot accept so many line. What a terrible
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply