Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Data Type as a parameter in a Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 2:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:56 AM
Points: 86, Visits: 695
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
Post #1433470
Posted Wednesday, March 20, 2013 5:27 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
Before executing procedure, what is len(@agItem)? Maybe it is truncated before the procedure.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1433546
Posted Wednesday, March 20, 2013 10:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:56 AM
Points: 86, Visits: 695
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
Post #1433574
Posted Thursday, March 21, 2013 1:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
Try using "new SqlXml(...)" instead of converting doc to string.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1433612
Posted Thursday, March 21, 2013 1:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ac074ccb-1a52-4bda-a032-c874d3ba8ce5/

cmd.Parameters.Add("@Value", SqlDbType.Xml).Value = doc.ToString();



_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1433613
Posted Friday, March 22, 2013 1:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:56 AM
Points: 86, Visits: 695
I think XML Data Type cannot accept so many line. What a terrible
Post #1434493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse