-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: r_karpagam
Message 1 in Discussion
when executing the stored procedures which performs bulk insert,updates & deletes
using openxml , from with in .aspx page it sometimes gives the following error
Timeout expired. The timeout period elapsed prior to completion of the operation or
the server is not responding The stored procedure accepts xml string as input and
parses it and performs inserts,updates,deletes based on some criteria. The following
is the source code for SP CREATE Procedure CurriculumAssignment @XMLString ntext
as declare @itree integer --variable to hold handle to the newly created document /*
Read and parse the XML text provided as input using sp_xml_preparedocument. The
document parsed is represented in
a tree structure and used to access the newly created internal representation of xml
document.
*/ exec sp_xml_preparedocument @itree output,@XMLString
if @@error <> 0
GoTo ErrHandler
/* Execute the insert,update and delete statement using the openxml specifying the
node to be processed, and with clause specifying
the column names that match the corresponding attribute names
*/ /* If Course Status is changed and a new course is assigned insert the course
details into role_course_allotted table
for the specified role */ Begin Tran
Insert into role_course_allotted
select RoleId,CourseId,getdate() as
allotteddate,CourseCurrentOrderId,NullIf(CourseCurrentPReId,0) from
openxml(@itree,'Curriculum/Course')
with -- Specify the structure of the rowset
(
RoleId integer '../@RoleId',
CourseId integer '@CourseId',
CourseCurrentOrderId integer '@CurrentOrderId',
CourseCurrentPReId integer '@CurrentPreRequisiteID',
CourseCurrentStatus integer '@CurrentStatus',
CoursePreviousStatus integer '@PreviousStatus'
)
where CourseCurrentstatus <> Coursepreviousstatus and coursecurrentstatus = 1 if
@@error <> 0
--Rollback Tran
GoTo ErrHandler /* If Course Status is changed and course is unassigned delete
the course details from role_course_allotted table
for the specified role */ Delete from role_course_allotted
from role_course_allotted inner join
(select RoleId,CourseId from openxml(@itree,'Curriculum/Course')
with
(
RoleId integer '../@RoleId',
CourseId integer '@CourseId',
CourseCurrentStatus integer '@CurrentStatus',
CoursePreviousStatus integer '@PreviousStatus'
)
where CourseCurrentStatus <> CoursePreviousStatus and CourseCurrentStatus = 0 )
a on role_course_allotted.courseid = a.courseid and role_course_allotted.roleid =
a.roleid if @@error <> 0
--RollBack Tran
GoTo ErrHandler /* If Course Status is changed and course is unassigned delete the
lesson details from role_lesson_allotted table
for the specified role */ Delete from role_lesson_allotted
from role_lesson_allotted inner join (select LessonId,RoleId
from openxml(@itree,'Curriculum/Course/Lesson')
with
(
RoleId integer '../../@RoleId',
LessonId integer '@LessonId',
CourseCurrentStatus integer '../@CurrentStatus',
CoursePreviousStatus integer '../@PreviousStatus'
)
where CourseCurrentStatus <> CoursePreviousStatus and CourseCurrentStatus = 0
) a
on role_lesson_allotted.lessonid = a.LessonId and role_lesson_allotted.RoleId =
a.RoleId if @@error <> 0
--RollBack Tran
GoTo ErrHandler /* If course status is not changed and course is still assigned
and the course orderid is changed update the
course orderid for the specified role in the role_course_allotted table */ UPDATE
role_course_allotted SET orderid = XML_Course.CourseCurrentOrderId
from openxml(@itree,'Curriculum/Course')
with (
RoleId integer '../@RoleId',
CourseId integer '@CourseId',
CourseCurrentStatus integer '@CurrentStatus',
CoursePreviousStatus integer '@PreviousStatus',
CourseCurrentOrderId integer '@CurrentOrderId',
CoursePreviousOrderId integer '@PreviousOrderId'
) XML_Course
where CourseCurrentStatus = CoursePreviousStatus and CourseCurrentStatus =1 and
CourseCurrentOrderId <> CoursePreviousOrderId
and role_course_allotted.CourseId = XML_Course.CourseId and
role_course_allotted.roleid = XML_Course.RoleId if @@error <> 0
--RollBack Tran
GoTo ErrHandler /* If course status is not changed and is still assigned and the
course PrerequisiteId is changed update the
course PrerequisiteId for the specified role in the role_course_allotted table */
UPDATE role_course_allotted SET PreReqCourseID =
NullIf(XML_Course.CurrentCoursePreReqID,0)
from openxml(@itree,'Curriculum/Course')
with
(
RoleId integer '../@RoleId',
CourseId integer '@CourseId',
CourseCurrentStatus integer '@CurrentStatus',
CoursePreviousStatus integer '@PreviousStatus',
CurrentCoursePreReqID integer '@CurrentPreRequisiteID',
PreviousCoursePreReqID integer '@PreviousPreRequisiteID'
) XML_Course
where CourseCurrentStatus = CoursePreviousStatus and CourseCurrentStatus = 1 and
CurrentCoursePreReqID <> PreviousCoursePreReqID
and role_course_allotted.CourseId = XML_Course.CourseId and
role_course_allotted.roleid = XML_Course.RoleId if @@error <> 0
--RollBack Tran
GoTo ErrHandler /* If lesson status is changed and is assigned insert the lesson
details into role_lesson_allotted table for
the specified role */ insert into role_lesson_allotted
select RoleId,LessonId,LessonCurrentOrderId,NullIf(LessonCurrentPreId,0) from
openxml(@itree,'Curriculum/Course/Lesson')
with
(RoleId integer '../../@RoleId',
LessonId integer '@LessonId',
LessonCurrentOrderId integer '@CurrentOrderId',
LessonCurrentPreId integer '@CurrentPreRequisiteID',
LessonCurrentStatus integer '@CurrentStatus',
LessonPreviousStatus integer '@PreviousStatus'
) where LessonCurrentStatus <> LessonPreviousStatus and LessonCurrentStatus = 1 if
@@error <> 0
--RollBack Tran
GoTo ErrHandler /* If the Lesson status is changed and is unassigned but the
course remains Unassigned delete the lesson
details from the role_lesson_allotted table for the specified role */ Delete from
role_lesson_allotted
from role_lesson_allotted inner join (select * from
openxml(@itree,'Curriculum/Course/Lesson')
with
(RoleId integer '../../@RoleId',
LessonId integer '@LessonId',
LessonCurrentStatus integer '@CurrentStatus',
LessonPreviousStatus integer '@PreviousStatus',
CourseCurrentStatus integer '../@CurrentStatus')
where LessonCurrentStatus <> LessonPreviousStatus and LessonCurrentStatus =0 and
CourseCurrentStatus = 1
) a on role_lesson_allotted.lessonid = a.lessonid and role_lesson_allotted.roleid =
a.roleid if @@error <> 0
--RollBack Tran
GoTo ErrHandler /* If lesson status is not changed and is still assigned and
lesson order id is changed update the lesson order id
for the specified role in the role_lesson_allotted table */ UPDATE
role_lesson_allotted SET orderid = XML_Course.LessonCurrentOrderId
from openxml(@itree,'Curriculum/Course/Lesson')
with (
RoleId integer '../../@RoleId',
LessonId integer '@LessonId',
LessonCurrentStatus integer '@CurrentStatus',
LessonPreviousStatus integer '@PreviousStatus',
LessonCurrentOrderId integer '@CurrentOrderId',
LessonPreviousOrderId integer '@PreviousOrderId'
) XML_Course
where LessonCurrentStatus = LessonPreviousStatus and LessonCurrentStatus =1 and
LessonCurrentOrderId <> LessonPreviousOrderId
and role_lesson_allotted.LessonId = XML_Course.LessonId and
role_lesson_allotted.roleid = XML_Course.RoleId if @@error <> 0
--RollBack Tran
GoTo ErrHandler /*If lesson status is not changed and is still assigned and lesson
PrerequisiteId is changed update the lesson
PrerequisiteId for the specified role in the role_lesson_allotted table */
UPDATE role_lesson_allotted SET PreReqLessonId =
NullIf(XML_Course.CurrentPreRequisiteID,0)
from openxml(@itree,'Curriculum/Course/Lesson')
with (
RoleId integer '../../@RoleId',
LessonId integer '@LessonId',
LessonCurrentStatus integer '@CurrentStatus',
LessonPreviousStatus integer '@PreviousStatus',
CurrentPreRequisiteID integer '@CurrentPreRequisiteID',
PreviousPreRequisiteID integer '@PreviousPreRequisiteID'
) XML_Course
where LessonCurrentStatus = LessonPreviousStatus and LessonCurrentStatus =1 and
CurrentPreRequisiteID <> PreviousPreRequisiteID
and role_lesson_allotted.LessonId = XML_Course.LessonId and
role_lesson_allotted.roleid = XML_Course.RoleId if @@error <> 0
--RollBack Tran
GoTo ErrHandler Commit Tran -- Reclaim the memory used using sp_xml_removedocument
exec sp_xml_removedocument @itree
--return 1
return 0
ErrHandler:
print 'error occured'
RollBack Tran
exec sp_xml_removedocument @itree
--return 0
return 1
GO
The above SP is executed using sqlcommand object whose commandtimeout property is set
to 0.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
