I've noticed that when I have a sql server agent job that calls a DTS
package, and that package in turn calls another DTS package, when the
inner package fails, it does not return an error to the outer package
and hence the sql server agent job indicates success even though the
inner dts package failed.
Is there a way to get the sql server agent job to indicate a failure
when the inner dts package fails?
Thanks in advance.
Walter
waltmallon@.yahoo.comunfortunately you will have to either write an extra step into the
"called/children" packages that returns a success or failure, I have done
this in the past with the RAISERROR WITH LOG for TSQL and and the err
collection in an ActiveX script this way your package can return a status to
the parent DTS packag, I am sure Darren Green (sqldts.com) will/may have
better ideas
--
Olu Adedeji
"Walter Mallon" <waltmallon@.yahoo.com> wrote in message
news:28db1398.0402190854.46eedc55@.posting.google.com...
> I've noticed that when I have a sql server agent job that calls a DTS
> package, and that package in turn calls another DTS package, when the
> inner package fails, it does not return an error to the outer package
> and hence the sql server agent job indicates success even though the
> inner dts package failed.
> Is there a way to get the sql server agent job to indicate a failure
> when the inner dts package fails?
> Thanks in advance.
> Walter
> waltmallon@.yahoo.com
Showing posts with label returning. Show all posts
Showing posts with label returning. Show all posts
Friday, March 23, 2012
nesting DTS packages and returning errors
I've noticed that when I have a sql server agent job that calls a DTS
package, and that package in turn calls another DTS package, when the
inner package fails, it does not return an error to the outer package
and hence the sql server agent job indicates success even though the
inner dts package failed.
Is there a way to get the sql server agent job to indicate a failure
when the inner dts package fails?
Thanks in advance.
Walter
waltmallon@.yahoo.comunfortunately you will have to either write an extra step into the
"called/children" packages that returns a success or failure, I have done
this in the past with the RAISERROR WITH LOG for TSQL and and the err
collection in an ActiveX script this way your package can return a status to
the parent DTS packag, I am sure Darren Green (sqldts.com) will/may have
better ideas
--
Olu Adedeji
"Walter Mallon" <waltmallon@.yahoo.com> wrote in message
news:28db1398.0402190854.46eedc55@.posting.google.com...
> I've noticed that when I have a sql server agent job that calls a DTS
> package, and that package in turn calls another DTS package, when the
> inner package fails, it does not return an error to the outer package
> and hence the sql server agent job indicates success even though the
> inner dts package failed.
> Is there a way to get the sql server agent job to indicate a failure
> when the inner dts package fails?
> Thanks in advance.
> Walter
> waltmallon@.yahoo.comsql
package, and that package in turn calls another DTS package, when the
inner package fails, it does not return an error to the outer package
and hence the sql server agent job indicates success even though the
inner dts package failed.
Is there a way to get the sql server agent job to indicate a failure
when the inner dts package fails?
Thanks in advance.
Walter
waltmallon@.yahoo.comunfortunately you will have to either write an extra step into the
"called/children" packages that returns a success or failure, I have done
this in the past with the RAISERROR WITH LOG for TSQL and and the err
collection in an ActiveX script this way your package can return a status to
the parent DTS packag, I am sure Darren Green (sqldts.com) will/may have
better ideas
--
Olu Adedeji
"Walter Mallon" <waltmallon@.yahoo.com> wrote in message
news:28db1398.0402190854.46eedc55@.posting.google.com...
> I've noticed that when I have a sql server agent job that calls a DTS
> package, and that package in turn calls another DTS package, when the
> inner package fails, it does not return an error to the outer package
> and hence the sql server agent job indicates success even though the
> inner dts package failed.
> Is there a way to get the sql server agent job to indicate a failure
> when the inner dts package fails?
> Thanks in advance.
> Walter
> waltmallon@.yahoo.comsql
Monday, March 12, 2012
Nested Cursors
What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @.TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno
--delete from tblElementCPO
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.Count = count (*)
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
if @.Count > 0
begin
select @.ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
select @.Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @.TemplateRecNo
and ProjTypeRecno = @.ProjTypeRecNo
if @.Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @.TemplateRecNo)
AND (ProjTypeRecNo = @.ProjTypeRecno)
AND (PreElementTypeRecNo = @.ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PreElementTypeRecno
if @.PreElementRecno is not null
begin
select @.PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PostElementTypeRecno
if @.PostElementRecno is not null
begin
select @.Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @.ElementTypeDepRecno
and PreElementRecNo = @.PreElementRecNo
and PostElementRecno = @.PostElementRecno
if @.Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,
@.PostElementRecno, @.ElapsedTimeDueDates,
@.ElapsedTimePlanDates, @.Description,
GETDATE(), CURRENT_USER)
end
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.NextPlanDate = ProjectedCompletionDate,
@.NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @.PreElementRecNo
end
else
begin
select @.NextPlanDate = @.StartDate
select @.NextDueDate = @.StartDate
end
select @.NextPlanDate =
dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)
select @.NextDueDate =
dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@.PostElementRecno,
@.NextPlanDate, @.NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @.ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?
Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.
|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.Joe
Nested Cursors
What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @.TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno
--delete from tblElementCPO
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.Count = count (*)
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
if @.Count > 0
begin
select @.ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @.ProjRecNo
select @.Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @.TemplateRecNo
and ProjTypeRecno = @.ProjTypeRecNo
if @.Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @.TemplateRecNo)
AND (ProjTypeRecNo = @.ProjTypeRecno)
AND (PreElementTypeRecNo = @.ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PreElementTypeRecno
if @.PreElementRecno is not null
begin
select @.PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @.ProjRecNo
and IssueRecno = @.IssueRecNo
and ElementTypeRecno = @.PostElementTypeRecno
if @.PostElementRecno is not null
begin
select @.Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @.ElementTypeDepRecno
and PreElementRecNo = @.PreElementRecNo
and PostElementRecno = @.PostElementRecno
if @.Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,
@.PostElementRecno, @.ElapsedTimeDueDates,
@.ElapsedTimePlanDates, @.Description,
GETDATE(), CURRENT_USER)
end
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @.PostElementRecNo
if @.Count = 0
begin
select @.NextPlanDate = ProjectedCompletionDate,
@.NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @.PreElementRecNo
end
else
begin
select @.NextPlanDate = @.StartDate
select @.NextDueDate = @.StartDate
end
select @.NextPlanDate =
dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)
select @.NextDueDate =
dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@.PostElementRecno,
@.NextPlanDate, @.NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @.ElementTypeDepRecno, @.PreElementTypeRecNo,
@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,
@.Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @.ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?
Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.
|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.Joe
Subscribe to:
Posts (Atom)