Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
END
Just a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.co m...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END
|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).
|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.c om...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).
Showing posts with label generate. Show all posts
Showing posts with label generate. Show all posts
Monday, March 19, 2012
Error Could not generate asynchronous keyset. The cursor has been deallocated.
Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold
'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.googl
e.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the er
ror messages and that it work on
some server but not on other, that the difference can be this configuration.
You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug
and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.goog
le.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold
'?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.googl
e.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the er
ror messages and that it work on
some server but not on other, that the difference can be this configuration.
You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug
and open a case with MS PSS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.goog
le.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).
Error Could not generate asynchronous keyset. The cursor has been deallocated.
Hi -
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).
Version 2000.80.760.0.
We have an update trigger that suddenly began to produce the above
error. Through trial and error, I was able to eliminate the error by
changing a table variable to a temporary table.
The problem can happen or not depending on which machine it is on,
despite the fact that the machines are running the same version. The
problem first occurred on a quad processor, but was then replicated on
a single-processor machine.
Using a temp table is OK, but if anyone can shed any light on why this
would fail, and only on some machines, and not in any of our previous
testing, any help would be greatly appreciated!
Here is a condensed version of the trigger:
ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
on cbord.cbo4004p_eventlist
for update
As
BEGIN
IF UPDATE(TLC) RETURN;
IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
WHERE ie.itemevent_intid = i.itemevent_intid
AND ie.itemevent_type = 1000)
BEGIN
-- bunch of declarations...then the problematic variable
DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
Key (Eventlist_intid));
INSERT INTO @.lt_unchangedrows(Eventlist_intid)
(SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
FROM inserted)
BEGIN
DECLARE bins_csr cursor forward_only dynamic read_only
FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
i.source, i.storageloc_intid, i.item_intid,
i.itemuofm1_intid, i.itemxa_intid,
-- etc......
FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
WHERE ie.itemevent_type = 1000
AND i.eventlist_intid = d.eventlist_intid
AND i.itemevent_intid = ie.itemevent_intid
AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
@.lt_unchangedrows)
OPEN bins_csr
-- fails here
FETCH next from bins_csr into @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
WHILE (@.@.fetch_status = 0)
BEGIN
-- do stuff
FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
@.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
END
close bins_csr
deallocate bins_csr
END
END
ENDJust a wild guess, and without looking at the code:
Perhaps sp_configure is different in the configuration for 'cursor threshold'?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406141626.3567b23@.posting.google.com...
> Hi -
> Version 2000.80.760.0.
> We have an update trigger that suddenly began to produce the above
> error. Through trial and error, I was able to eliminate the error by
> changing a table variable to a temporary table.
> The problem can happen or not depending on which machine it is on,
> despite the fact that the machines are running the same version. The
> problem first occurred on a quad processor, but was then replicated on
> a single-processor machine.
> Using a temp table is OK, but if anyone can shed any light on why this
> would fail, and only on some machines, and not in any of our previous
> testing, any help would be greatly appreciated!
> Here is a condensed version of the trigger:
> ALTER TRIGGER cbord.aucbo009_cbo4004p_bins
> on cbord.cbo4004p_eventlist
> for update
> As
> BEGIN
> IF UPDATE(TLC) RETURN;
> IF EXISTS (SELECT 1 FROM cbo4002p_itemevent ie,inserted i
> WHERE ie.itemevent_intid = i.itemevent_intid
> AND ie.itemevent_type = 1000)
> BEGIN
> -- bunch of declarations...then the problematic variable
> DECLARE @.lt_unchangedrows TABLE(Eventlist_intid integer, Primary
> Key (Eventlist_intid));
> INSERT INTO @.lt_unchangedrows(Eventlist_intid)
> (SELECT inserted.eventlist_intid FROM inserted,deleted -- etc.
> IF (SELECT COUNT(*) FROM @.lt_unchangedrows) < (SELECT COUNT(*)
> FROM inserted)
> BEGIN
> DECLARE bins_csr cursor forward_only dynamic read_only
> FOR SELECT i.itemevent_intid, i.writeinflag, i.binid,
> i.source, i.storageloc_intid, i.item_intid,
> i.itemuofm1_intid, i.itemxa_intid,
> -- etc......
> FROM inserted i, deleted d, cbord.cbo4002p_itemevent ie
> WHERE ie.itemevent_type = 1000
> AND i.eventlist_intid = d.eventlist_intid
> AND i.itemevent_intid = ie.itemevent_intid
> AND i.eventlist_intid NOT IN (SELECT eventlist_intid FROM
> @.lt_unchangedrows)
> OPEN bins_csr
> -- fails here
> FETCH next from bins_csr into @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> WHILE (@.@.fetch_status = 0)
> BEGIN
> -- do stuff
> FETCH next from bins_csr INTO @.li_csr_itemevent_intid,
> @.lc_csr_writeinflag, @.ls_csr_binid, @.ls_csr_source, -- etc.
> END
> close bins_csr
> deallocate bins_csr
> END
> END
> END|||Thanks - I did find that the machine in question had this option set
to 0 (always generate asynchronously). For now we seem to be OK as
long as we set the option to -1. Now I am wondering, is this a bug? If
using the asynchronous cursor option (either always on, 0, or with
some threshold like 5,000) can significantly benefit performance, then
we may be interested in tracking this issue so that we can make use of
the feature in the future (when it's fixed).|||I can't say whether it is a bug or not. I was just guessing, based on the error messages and that it work on
some server but not on other, that the difference can be this configuration. You could research this in KB,
Google, etc and based on that determine whether you want to call this a bug and open a case with MS PSS...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matt White" <mjw@.cbord.com> wrote in message news:d8a1796f.0406150744.35a168e7@.posting.google.com...
> Thanks - I did find that the machine in question had this option set
> to 0 (always generate asynchronously). For now we seem to be OK as
> long as we set the option to -1. Now I am wondering, is this a bug? If
> using the asynchronous cursor option (either always on, 0, or with
> some threshold like 5,000) can significantly benefit performance, then
> we may be interested in tracking this issue so that we can make use of
> the feature in the future (when it's fixed).
Friday, February 24, 2012
Error cause job to quit running
I have a sp that calls another sp. The second sp will generate an error
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good:). But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here'?
BarryHi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLACE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure we could possibly improve the performance a little (for instance I
could do a binary search for the correct number which would in threory take
fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
name I want for the backup file), but so what. The question is why does
this die as a job after only ten iterations.? You are looking for a
workareound and I am looking for an answer to my question/problem. You are
not solving anything. Why does the sp fail to run as a job when it runs
perfectly well in query analyser?
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. Based on my
> reserach, you can only do two thing in a job step, success or failure,
> whenever it fails, the job will jump to "On Failure".
> IMO, we'd better try to find whether there is anything we could do to
> improve the design. We appreciate your patience and look forward to
> hearing
> from you!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hello Barry,
I would appreciate your patience while I am looking into this issue.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 30 Sep 2004 12:23:46 -0500
| Lines: 43
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sure we could possibly improve the performance a little (for instance I
| could do a binary search for the correct number which would in threory
take
| fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
| name I want for the backup file), but so what. The question is why does
| this die as a job after only ten iterations.? You are looking for a
| workareound and I am looking for an answer to my question/problem. You
are
| not solving anything. Why does the sp fail to run as a job when it runs
| perfectly well in query analyser?
|
| Barry
|
|
| ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
message
| news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > Hi Barry,
| >
| > I wanted to post a quick note to see if you would like additional
| > assistance or information regarding this particular issue. Based on my
| > reserach, you can only do two thing in a job step, success or failure,
| > whenever it fails, the job will jump to "On Failure".
| >
| > IMO, we'd better try to find whether there is anything we could do to
| > improve the design. We appreciate your patience and look forward to
| > hearing
| > from you!
| >
| >
| >
| > Sincerely yours,
| >
| > Mingqing Cheng
| >
| > Online Partner Support Specialist
| > Partner Support Group
| > Microsoft Global Technical Support Center
| > ---
| > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > This posting is provided "as is" with no warranties and confers no
rights.
| > Please reply to newsgroups only, many thanks!
| >
| >
|
|
||||Sure, thanks for taking a look.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: <barryfz@.home.com>
>References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
>Subject: Re: Error cause job to quit running
>Date: Mon, 4 Oct 2004 08:26:04 -0500
>Lines: 101
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#a2VKXhqEHA.1296@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12|||Any luck figuring this out yet?
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
I have already responded to your questions (on 10/4/04) as follows,
Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 7 Oct 2004 07:14:06 -0500
| Lines: 104
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Any luck figuring this out yet?
|
| Barry
|
|
| "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
message
| news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
| >
| > Hello Barry,
| >
| > I would appreciate your patience while I am looking into this issue.
| >
| > Thanks,
| >
| > Vikrant Dalwale
| >
| > Microsoft SQL Server Support Professional
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > Get secure !! For info, please visit http://www.microsoft.com/security.
| > Please reply to Newsgroups only.
| >
| > --
| > | From: <barryfz@.home.com>
| > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
| > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
| > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
| > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
| > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
| > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
| > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
| > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
| > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
| > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error cause job to quit running
| > | Date: Thu, 30 Sep 2004 12:23:46 -0500
| > | Lines: 43
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Sure we could possibly improve the performance a little (for instance
I
| > | could do a binary search for the correct number which would in threory
| > take
| > | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
| > the
| > | name I want for the backup file), but so what. The question is why
does
| > | this die as a job after only ten iterations.? You are looking for a
| > | workareound and I am looking for an answer to my question/problem. You
| > are
| > | not solving anything. Why does the sp fail to run as a job when it
runs
| > | perfectly well in query analyser?
| > |
| > | Barry
| > |
| > |
| > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
| > message
| > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > | > Hi Barry,
| > | >
| > | > I wanted to post a quick note to see if you would like additional
| > | > assistance or information regarding this particular issue. Based on
my
| > | > reserach, you can only do two thing in a job step, success or
| > failure,
| > | > whenever it fails, the job will jump to "On Failure".
| > | >
| > | > IMO, we'd better try to find whether there is anything we could do
to
| > | > improve the design. We appreciate your patience and look forward to
| > | > hearing
| > | > from you!
| > | >
| > | >
| > | >
| > | > Sincerely yours,
| > | >
| > | > Mingqing Cheng
| > | >
| > | > Online Partner Support Specialist
| > | > Partner Support Group
| > | > Microsoft Global Technical Support Center
| > | > ---
| > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > | > This posting is provided "as is" with no warranties and confers no
| > rights.
| > | > Please reply to newsgroups only, many thanks!
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
||||Thanks. We are testing the workaround now. I do not see a posting on the
board from you on 10/4 and if you sent a private email it was not received.
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:N%232G5OKrEHA.3356@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I have already responded to your questions (on 10/4/04) as follows,
> Hello Barry,
> Looking at the issue, you are probably hitting the BUG documented in
> FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
> SQLAgent job to fail and to lose output in SQL Server
> http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
> The original intension of this behavior (by design) was to prohibit the
> SQLAgent form running the job if there is an error. This has the potential
> for denial of service by filling the error log up if the job raises an
> error in an endless loop.
> Note that, as mentioned in the KB article, this fix applies only to
> Errors
> with Severity less than 20.
> Please follow the workarounds documented in the KB.
> If you want the HOTFIX please send email to (remove "online." from this no
> Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> *Include "Followup: < 24820437>" in the email Subject.
> *Location of the post
> *Subject Line
> *First Name, Last Name
> *MSDN Subscriber ID
> *Company name (if any)
> *Phone number
> *e-mail address
>
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> <ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 7 Oct 2004 07:14:06 -0500
> | Lines: 104
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path:
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
> phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Any luck figuring this out yet?
> |
> | Barry
> |
> |
> | "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
> message
> | news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> | >
> | > Hello Barry,
> | >
> | > I would appreciate your patience while I am looking into this issue.
> | >
> | > Thanks,
> | >
> | > Vikrant Dalwale
> | >
> | > Microsoft SQL Server Support Professional
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > Get secure !! For info, please visit
> http://www.microsoft.com/security.
> | > Please reply to Newsgroups only.
> | >
> | > --
> | > | From: <barryfz@.home.com>
> | > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> | > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> | > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> | > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> | > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> | > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> | > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> | > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | > | Subject: Re: Error cause job to quit running
> | > | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | > | Lines: 43
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | Sure we could possibly improve the performance a little (for
> instance
> I
> | > | could do a binary search for the correct number which would in
> threory
> | > take
> | > | fewer trys) or MS could rewrite their DB Maint Plans so I can
> specify
> | > the
> | > | name I want for the backup file), but so what. The question is why
> does
> | > | this die as a job after only ten iterations.? You are looking for a
> | > | workareound and I am looking for an answer to my question/problem.
> You
> | > are
> | > | not solving anything. Why does the sp fail to run as a job when it
> runs
> | > | perfectly well in query analyser?
> | > |
> | > | Barry
> | > |
> | > |
> | > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> | > message
> | > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > | > Hi Barry,
> | > | >
> | > | > I wanted to post a quick note to see if you would like additional
> | > | > assistance or information regarding this particular issue. Based
> on
> my
> | > | > reserach, you can only do two thing in a job step, success or
> | > failure,
> | > | > whenever it fails, the job will jump to "On Failure".
> | > | >
> | > | > IMO, we'd better try to find whether there is anything we could do
> to
> | > | > improve the design. We appreciate your patience and look forward
> to
> | > | > hearing
> | > | > from you!
> | > | >
> | > | >
> | > | >
> | > | > Sincerely yours,
> | > | >
> | > | > Mingqing Cheng
> | > | >
> | > | > Online Partner Support Specialist
> | > | > Partner Support Group
> | > | > Microsoft Global Technical Support Center
> | > | > ---
> | > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > | > This posting is provided "as is" with no warranties and confers no
> | > rights.
> | > | > Please reply to newsgroups only, many thanks!
> | > | >
> | > | >
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good:). But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here'?
BarryHi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLACE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Sure we could possibly improve the performance a little (for instance I
could do a binary search for the correct number which would in threory take
fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
name I want for the backup file), but so what. The question is why does
this die as a job after only ten iterations.? You are looking for a
workareound and I am looking for an answer to my question/problem. You are
not solving anything. Why does the sp fail to run as a job when it runs
perfectly well in query analyser?
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. Based on my
> reserach, you can only do two thing in a job step, success or failure,
> whenever it fails, the job will jump to "On Failure".
> IMO, we'd better try to find whether there is anything we could do to
> improve the design. We appreciate your patience and look forward to
> hearing
> from you!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hello Barry,
I would appreciate your patience while I am looking into this issue.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 30 Sep 2004 12:23:46 -0500
| Lines: 43
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sure we could possibly improve the performance a little (for instance I
| could do a binary search for the correct number which would in threory
take
| fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
| name I want for the backup file), but so what. The question is why does
| this die as a job after only ten iterations.? You are looking for a
| workareound and I am looking for an answer to my question/problem. You
are
| not solving anything. Why does the sp fail to run as a job when it runs
| perfectly well in query analyser?
|
| Barry
|
|
| ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
message
| news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > Hi Barry,
| >
| > I wanted to post a quick note to see if you would like additional
| > assistance or information regarding this particular issue. Based on my
| > reserach, you can only do two thing in a job step, success or failure,
| > whenever it fails, the job will jump to "On Failure".
| >
| > IMO, we'd better try to find whether there is anything we could do to
| > improve the design. We appreciate your patience and look forward to
| > hearing
| > from you!
| >
| >
| >
| > Sincerely yours,
| >
| > Mingqing Cheng
| >
| > Online Partner Support Specialist
| > Partner Support Group
| > Microsoft Global Technical Support Center
| > ---
| > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > This posting is provided "as is" with no warranties and confers no
rights.
| > Please reply to newsgroups only, many thanks!
| >
| >
|
|
||||Sure, thanks for taking a look.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: <barryfz@.home.com>
>References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
>Subject: Re: Error cause job to quit running
>Date: Mon, 4 Oct 2004 08:26:04 -0500
>Lines: 101
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#a2VKXhqEHA.1296@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12|||Any luck figuring this out yet?
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > Hi Barry,
> | >
> | > I wanted to post a quick note to see if you would like additional
> | > assistance or information regarding this particular issue. Based on my
> | > reserach, you can only do two thing in a job step, success or
> failure,
> | > whenever it fails, the job will jump to "On Failure".
> | >
> | > IMO, we'd better try to find whether there is anything we could do to
> | > improve the design. We appreciate your patience and look forward to
> | > hearing
> | > from you!
> | >
> | >
> | >
> | > Sincerely yours,
> | >
> | > Mingqing Cheng
> | >
> | > Online Partner Support Specialist
> | > Partner Support Group
> | > Microsoft Global Technical Support Center
> | > ---
> | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > This posting is provided "as is" with no warranties and confers no
> rights.
> | > Please reply to newsgroups only, many thanks!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
I have already responded to your questions (on 10/4/04) as follows,
Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 7 Oct 2004 07:14:06 -0500
| Lines: 104
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Any luck figuring this out yet?
|
| Barry
|
|
| "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
message
| news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
| >
| > Hello Barry,
| >
| > I would appreciate your patience while I am looking into this issue.
| >
| > Thanks,
| >
| > Vikrant Dalwale
| >
| > Microsoft SQL Server Support Professional
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > Get secure !! For info, please visit http://www.microsoft.com/security.
| > Please reply to Newsgroups only.
| >
| > --
| > | From: <barryfz@.home.com>
| > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
| > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
| > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
| > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
| > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
| > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
| > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
| > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
| > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
| > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error cause job to quit running
| > | Date: Thu, 30 Sep 2004 12:23:46 -0500
| > | Lines: 43
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Sure we could possibly improve the performance a little (for instance
I
| > | could do a binary search for the correct number which would in threory
| > take
| > | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
| > the
| > | name I want for the backup file), but so what. The question is why
does
| > | this die as a job after only ten iterations.? You are looking for a
| > | workareound and I am looking for an answer to my question/problem. You
| > are
| > | not solving anything. Why does the sp fail to run as a job when it
runs
| > | perfectly well in query analyser?
| > |
| > | Barry
| > |
| > |
| > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
| > message
| > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > | > Hi Barry,
| > | >
| > | > I wanted to post a quick note to see if you would like additional
| > | > assistance or information regarding this particular issue. Based on
my
| > | > reserach, you can only do two thing in a job step, success or
| > failure,
| > | > whenever it fails, the job will jump to "On Failure".
| > | >
| > | > IMO, we'd better try to find whether there is anything we could do
to
| > | > improve the design. We appreciate your patience and look forward to
| > | > hearing
| > | > from you!
| > | >
| > | >
| > | >
| > | > Sincerely yours,
| > | >
| > | > Mingqing Cheng
| > | >
| > | > Online Partner Support Specialist
| > | > Partner Support Group
| > | > Microsoft Global Technical Support Center
| > | > ---
| > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
| > | > This posting is provided "as is" with no warranties and confers no
| > rights.
| > | > Please reply to newsgroups only, many thanks!
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
||||Thanks. We are testing the workaround now. I do not see a posting on the
board from you on 10/4 and if you sent a private email it was not received.
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:N%232G5OKrEHA.3356@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I have already responded to your questions (on 10/4/04) as follows,
> Hello Barry,
> Looking at the issue, you are probably hitting the BUG documented in
> FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
> SQLAgent job to fail and to lose output in SQL Server
> http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
> The original intension of this behavior (by design) was to prohibit the
> SQLAgent form running the job if there is an error. This has the potential
> for denial of service by filling the error log up if the job raises an
> error in an endless loop.
> Note that, as mentioned in the KB article, this fix applies only to
> Errors
> with Severity less than 20.
> Please follow the workarounds documented in the KB.
> If you want the HOTFIX please send email to (remove "online." from this no
> Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> *Include "Followup: < 24820437>" in the email Subject.
> *Location of the post
> *Subject Line
> *First Name, Last Name
> *MSDN Subscriber ID
> *Company name (if any)
> *Phone number
> *e-mail address
>
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> <ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 7 Oct 2004 07:14:06 -0500
> | Lines: 104
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path:
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
> phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Any luck figuring this out yet?
> |
> | Barry
> |
> |
> | "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
> message
> | news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> | >
> | > Hello Barry,
> | >
> | > I would appreciate your patience while I am looking into this issue.
> | >
> | > Thanks,
> | >
> | > Vikrant Dalwale
> | >
> | > Microsoft SQL Server Support Professional
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > Get secure !! For info, please visit
> http://www.microsoft.com/security.
> | > Please reply to Newsgroups only.
> | >
> | > --
> | > | From: <barryfz@.home.com>
> | > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> | > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> | > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> | > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> | > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> | > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> | > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> | > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | > | Subject: Re: Error cause job to quit running
> | > | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | > | Lines: 43
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | Sure we could possibly improve the performance a little (for
> instance
> I
> | > | could do a binary search for the correct number which would in
> threory
> | > take
> | > | fewer trys) or MS could rewrite their DB Maint Plans so I can
> specify
> | > the
> | > | name I want for the backup file), but so what. The question is why
> does
> | > | this die as a job after only ten iterations.? You are looking for a
> | > | workareound and I am looking for an answer to my question/problem.
> You
> | > are
> | > | not solving anything. Why does the sp fail to run as a job when it
> runs
> | > | perfectly well in query analyser?
> | > |
> | > | Barry
> | > |
> | > |
> | > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> | > message
> | > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > | > Hi Barry,
> | > | >
> | > | > I wanted to post a quick note to see if you would like additional
> | > | > assistance or information regarding this particular issue. Based
> on
> my
> | > | > reserach, you can only do two thing in a job step, success or
> | > failure,
> | > | > whenever it fails, the job will jump to "On Failure".
> | > | >
> | > | > IMO, we'd better try to find whether there is anything we could do
> to
> | > | > improve the design. We appreciate your patience and look forward
> to
> | > | > hearing
> | > | > from you!
> | > | >
> | > | >
> | > | >
> | > | > Sincerely yours,
> | > | >
> | > | > Mingqing Cheng
> | > | >
> | > | > Online Partner Support Specialist
> | > | > Partner Support Group
> | > | > Microsoft Global Technical Support Center
> | > | > ---
> | > | > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> | > | > This posting is provided "as is" with no warranties and confers no
> | > rights.
> | > | > Please reply to newsgroups only, many thanks!
> | > | >
> | > | >
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>
Error cause job to quit running
I have a sp that calls another sp. The second sp will generate an error
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good
. But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here??
Barry
Hi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =
N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =
N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_ db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLA CE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sorry I got my files mixed up. Here are the correct sp's and the output I
get from running these in QA vs in a job. Basically the QA runs to
completiong while the job quits after 10 iterations??
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your prompt updates and detailed information!
> However, I found you show me spSYSRestoreGDInventoryDB and
> spSYSRestoreCPI.
> spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
> spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
> putting spSYSRestoreCPI here? Or would you please so kind as to tell me
> how to reproduce that error?
> Moreover, it is highly appriciated if you could provide me error log in
> SQL
> Server
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
begin 666 RestoreJobErrorProblem.zip
M4$L#!!0````(`(U%-#&V-(*^YP$``&H$```;````<W!365-297-T;W)E1T1)
M;G9E;G1O<GDN<W%LC9-=;YLP%(:OA\1_.'= 0U*2IM,Z*5*;DK911U.%KM,D
MI,I@.0U'!I+E'\_&P@.E)5+G"^3#.<_Q^_HC8@.0)`AM61, W_E]_3;@.H&+EU
MEW1+J)SNX-)+Z345#%(J= UQ7=.UD"0I51-=.SU1WV\-"%TR9D4.HL!HQR%$
MT5NYJ4K;AC/(4UH*PB$J2BH(D\50<@.)QP1H`XC0C%.4$*G0['CF.#<[%</Q]
M.'&<J0WA6ZQ2)Z>U'$RB#$D=ES<2?% @.W2(6O2)F3L[/K4[!_+>KO&/Y$6E.
M>AE?*MRC9TZ?]+R/=#_KNL>STGNW\0^KELV):!7-`!(BE"ZSGY6KSL!P#!A
MA+@.PU;]'Q(29YW938@.'B^P6L`UB*.@.YC_#7L5R=VR.WD.$::4\N"@.3RP9 @.`O
M0RY82A.S]6'+@.U41KB(+AF-[\@.7DNEW(=1NHD=ELK7(H1VNRO6Y]8RW!TN2U
MKI2Q/;7:36]OT0P>C""85Y?2&3>3.\2*# >=.]^=O^#P1:GH[-^@.77,`QFA^
M=6_4!\R:UR-MH1#QPV>D:W"S7GG@.+OU[J:/2I)Z$KOU9/MV!MWI>@.'&PKNQB
MP-,*C.AGL&\9Y)R_9U48?"X>Y3@.V;%WKM\J*Y+\[R=I1IAJM%X^_ KJX7RABA
MN';X#U!+`P04````" #E+C0QG0''V*8!``!)`P``'0```'-P4UE34F5S=&]R
M94=$26YV96YT;W)Y1$(N<W%LG5)=:]LP%'UV(/_AO RGK GIV& =RX@.;*ZG!
MB8MMZ#XRAFS?)::.[$IJT_S[27679H6,,?0@.Z=YS[SG2/;DDK@.F-K'.H)OF2
MQ*1T+6GF!^*>A#GN_(MNAZMNI]LI**^X)(SGI9@.(+5$*_6<T,<%[+O,UE[WW
M)[;&+D7ZN6:$H8T%4[#/09(FZ"4L9),4@.F\(TSB:8\.5)CDHLGJ@.=JK@.FF=<
MD<+U)8M9BQMAX1YH= V7X\?1%7PO]2Z\A.';0?I[JV,2,R]E1R! M$!OX<W9
MR^8_K +W%-,@.9/M\_F'Y6]ARH]1M]7A='I;Y)C"8^U,7I^AVDN"KK3QK^\SB
MZ#J]M/?AJQ.$T>PX>U6O_H,\K%>#L.7^"[/]E7[?80\-Y1I-K529582?O*SN
MS$BYPI90U,+5,$:IJAUN1+W%=LTU])J0\?SFKFE'4CYZA![HJ)/V)K# [;HT
M3.,QD[*6^(0AN"B>;?)QA#=OWYV;L6:T*H79G1<VVA]?X\RF^_U&&D/"3>6N
M%"NX)I$;)_7V0/.<)W-:NSC_*-4A4;3_]-3_$.;7@.ER;_0502P,$% ````@.`
M4UPV,?3$4[$Z!@.``0VT!`! ```!105)U;DIO8E)U;BYT>'1L[=I-3^-6&$#A
M/1+_X>Z82D/JA&^D+L) VU&ATR%L*B&-3')#71R;V@.XM_[[7"95@.1EVTG>JH
MU0E("4[RVKZ/O3@.2;ZOP?AF;QS"N\O*QC4VX7%:;&YL;\;<X#>W]Y,?)96R[
MNHG?G+ZM'F*57CZ>GO2?V-S8?O9X/P[AW;*[7W;;GSSZSY[&,G9%=1MF>9?
MY&T,\Z*,86MZ?/W'ENM%V_Y2KOZ\?K:W#V5].RAG\ZW!/YK2;Q@.LUF.N?HKA
MS>79^.HLG(ZOQB?CR5FX;^II;-M0M"$ORWJ:K_8S'&19N#@.)=15F17L7MCX>
M^1G'I?/LITUB\Q";XW#1WH:=439\'<[C0RS#</]UF'1Y%\/H=?BA'S];-O'/
MC-*WBBJ&W6QSXTU>5747ZOM8A9M\>K>\#[/X4$S3NEU?GZPV9,.G%]_F35W.
MGB_<RT6\^3#*LMWL2>LFQP,OYN:Q!.U]-BT]1-2+]/T^OY?+M,!S$(DQA#
MEQ9I\OX\K$_OZ</IE,,\/2_2P:>O=7E1MA\O03;<^70)AG]M"2[/)E?O+I_Q
M))8N-HNB6KOD-U7=+!+4XW\*8"@.`"S 2@. 78$8 %V!6 !=@.3@. 78%X %.!"
M!3@.4@. 4X$@.`%&%K",( E# -8PC" )0P#6,(P@."4,`UC",( E# -8PC" )<P"
MC"QA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BQA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BUA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $F8!]BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2
MA@.$L81C $F8!]BUA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!
M#BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!#BUA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!CBQA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $D8!AIDE# -8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&L)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC +L&<)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6
M, Q@."<,`EC +L&\)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +
M<& )PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&@.)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&0)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC *D%X(P )8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`_],27@.UNVS@.+>_O98;C/;V.[.LI9
MWN4W>9O6[=E>MEZ'>5&^W/;A-'UR*]35^JWABZ'#OS7QO+Y].?"34VJ7TWX/
M\V4ZBW#__!R.GO985&%O,-H=A39.ZVK6AE>'@.X/]W7!Q\F7:\D4:VO]L_\N/
MM!;++K3Y(L$ERT4ZP?[(\O!S?1/R:A::916*;G4T;[]?73?]9?)8+\-M[,(P
M"^D;39'.I_VI_K77FZ?+IK\.TO*D=_L5:D.5YK<A2X_M+!MF@. S ^+].<^BZ4
MQ5U_-:8KH,S;;C7L\;C?V?H.."W:NZ_3B./C=^F&N8BS(C\.)Y_OMLF>_J=]
M==3)IZM7=^8@.I-TUZZNQ?6R[N'BZ1;X*HU=7:0V>-D[7-_.\2 O5+\WJ>FCO
MX[28%W$VZ!%_!U!+`0(4`!0````(`(U%-#&V-(*^YP$``&H$```;````````
M``$```"V@.0````!S<%-94U)E<W1O<F5'1$EN=F5N=&]R>2YS<6Q02P$"% `4
M````" #E+C0QG0''V*8!``!)`P``'0`````````!````MH$@.`@.``<W!3 65-2
M97-T;W)E1T1);G9E;G1O<GE$0BYS<6Q02P$"% `4````" !37#8Q],13L3H&
M``!#;0$`$ `````````!`" `MH$!! ``44%2=6Y*;V)2=6XN='AT;%!+!08`
1`````P`#`-(```!I"@.``````
`
end
|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good
. But when I put the parent sp in a job stepand run it as a job, it quits on the first generated error. I don't
understand what is going on here??
Barry
Hi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =
N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =
N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_ db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLA CE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sorry I got my files mixed up. Here are the correct sp's and the output I
get from running these in QA vs in a job. Basically the QA runs to
completiong while the job quits after 10 iterations??
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your prompt updates and detailed information!
> However, I found you show me spSYSRestoreGDInventoryDB and
> spSYSRestoreCPI.
> spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
> spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
> putting spSYSRestoreCPI here? Or would you please so kind as to tell me
> how to reproduce that error?
> Moreover, it is highly appriciated if you could provide me error log in
> SQL
> Server
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
begin 666 RestoreJobErrorProblem.zip
M4$L#!!0````(`(U%-#&V-(*^YP$``&H$```;````<W!365-297-T;W)E1T1)
M;G9E;G1O<GDN<W%LC9-=;YLP%(:OA\1_.'= 0U*2IM,Z*5*;DK911U.%KM,D
MI,I@.0U'!I+
E'\_&P@.E)5+G"^3#.<_Q^_HC8@.0)`AM61, W_E]_3;@.H&+EUMEW1+J)SNX-)+Z345#%(J= UQ7=.UD"0I51-=.SU1WV\-"%TR9D4.HL!HQR%$
MT5NYJ4K;AC/(4UH*PB$J2BH(D\50<@.)QP1H`XC0C%.4$*G0['CF.#<[%</Q]
M.'&<J0WA6ZQ2)Z>U'$RB#$D=ES<2?% @.W2(6O2)F3L[/K4[!_+>KO&/Y$6E.
M>AE?*MRC9TZ?]+R/=#_KNL>STGNW\0^KELV):!7-`!(BE"ZSGY6KSL!P#!A
MA+@.PU;]'Q(29YW938@.'B^P6L`UB*.@.YC_#7L5R=VR.WD.$::4\N"@.3RP9 @.`O
M0RY82A.S]6'+@.U41KB(+AF-[\@.7DNEW(=1NHD=ELK7(H1VNRO6Y]8RW!TN2U
MKI2Q/;7:36]OT0P>C""85Y?2&3>3.\2*# >=.]^=O^#P1:GH[-^@.77,`QFA^
M=6_4!\R:UR-MH1#QPV>D:W"S7GG@.+OU[J:/2I)Z$KOU9/MV!MWI>@.'&PKNQB
MP-,*C.AGL&\9Y)R_9U48?"X>Y3@.V;%WKM\J*Y+\[R=I1IAJM%X^_ KJX7RABA
MN';X#U!+`P04````" #E+C0QG0''V*8!``!)`P``'0```'-P4UE34F5S=&]R
M94=$26YV96YT;W)Y1$(N<W%LG5)=:]LP%'UV(/_AO RGK GIV& =RX@.;*ZG!
MB8MMZ#XRAFS?)::.[$IJT_S[27679H6,,?0@.Z=YS[SG2/;DDK@.F-K'.H)OF2
MQ*1T+6GF!^*>A#GN_(MNAZMNI]LI**^X)(SGI9@.(+5$*_6<T,<%[+O,UE[WW
M)[;&+D7ZN6:$H8T%4[#/09(FZ"4L9),4@.F\(TSB:8\.5)CDHLGJ@.=JK@.FF=<
MD<+U)8M9BQMAX1YH= V7X\?1%7PO]2Z\A.';0?I[JV,2,R]E1R! M$!OX<W9
MR^8_K +W%-,@.9/M\_F'Y6]ARH]1M]7A='I;Y)C"8^U,7I^AVDN"KK3QK^\SB
MZ#J]M/?AJQ.$T>PX>U6O_H,\K%>#L.7^"[/]E7[?80\-Y1I-K529582?O*SN
MS$BYPI90U,+5,$:IJAUN1+W%=LTU])J0\?SFKFE'4CYZA![HJ)/V)K# [;HT
M3.,QD[*6^(0AN"B>;?)QA#=OWYV;L6:T*H79G1<VVA]?X\RF^_U&&D/"3>6N
M%"NX)I$;)_7V0/.<)W-:NSC_*-4A4;3_]-3_$.;7@.ER;_0502P,$% ````@.`
M4UPV,?3$4[$Z!@.``0VT!`! ```!105)U;DIO8E)U;BYT>'1L[=I-3^-6&$#A
M/1+_X>Z82D/JA&^D+L) VU&ATR%L*B&-3')#71R;V@.XM_[[7"95@.1EVTG>JH
MU0E("4[RVKZ/O3@.2;ZOP?AF;QS"N\O*QC4VX7%:;&YL;\;<X#>W]Y,?)96R[
MNHG?G+ZM'F*57CZ>GO2?V-S8?O9X/P[AW;*[7W;;GSSZSY[&,G9%=1MF>9?
MY&T,\Z*,86MZ?/W'ENM%V_Y2KOZ\?K:W#V5].RAG\ZW!/YK2;Q@.LUF.N?HKA
MS>79^.HLG(ZOQB?CR5FX;^II;-M0M"$ORWJ:K_8S'&19N#@.)=15F17L7MCX>
M^1G'I?/LITUB\Q";XW#1WH:=439\'<[C0RS#</]UF'1Y%\/H=?BA'S];-O'/
MC-*WBBJ&W6QSXTU>5747ZOM8A9M\>K>\#[/X4$S3NEU?GZPV9,.G%]_F35W.
MGB_<RT6\^3#*LMWL
2>LFQP,OYN:Q!.U]-BT]1-2+]/T^OY?+M,!S$(DQA#MEQ9I\OX\K$_OZ</IE,,\/2_2P:>O=7E1MA\O03;<^70)AG]M"2[/)E?O+I_Q
M))8N-HNB6KOD-U7=+!+4XW\*8"@.`"S 2@. 78$8 %V!6 !=@.3@. 78%X %.!"
M!3@.4@. 4X$@.`%&%K",( E# -8PC" )0P#6,(P@."4,`UC",( E# -8PC" )<P"
MC"QA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BQA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BUA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $F8!]BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2
MA@.$L81C $F8!]BUA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!
M#BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!#BUA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!CBQA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $D8!AIDE# -8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&L)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC +L&<)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6
M, Q@."<,`EC +L&\)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +
M<& )PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&@.)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&0)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC *D%X(P )8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`_],27@.UNVS@.+>_O98;C/;V.[.LI9
MWN4W>9O6[=E>MEZ'>5&^W/;A-'UR*]35^JWABZ'#OS7QO+Y].?"34VJ7TWX/
M\V4ZBW#__!R.GO985&%O,-H=A39.ZVK6AE>'@.X/]W7!Q\F7:\D4:VO]L_\N/
MM!;++K3Y(L$ERT4ZP?[(\O!S?1/R:A::916*;G4T;[]?73?]9?)8+\-M[,(P
M"^D;39'.I_VI_K77FZ?+IK\.TO*D=_L5:D.5YK<A2X_M+!MF@. S ^+].<^BZ4
MQ5U_-:8KH,S;;C7L\;C?V?H.."W:NZ_3B./C=^F&N8BS(C\.)Y_OMLF>_J=]
M==3)IZM7=^8@.I-TUZZNQ?6R[N'BZ1;X*HU=7:0V>-D[7-_.\2 O5+\WJ>FCO
MX[28%W$VZ!%_!U!+`0(4`!0````(`(U%-#&V-(*^YP$``&H$```;````````
M``$```"V@.0````!S<%-94U)E<W1O<F5'1$EN=F5N=&]R>2YS<6Q02P$"% `4
M````" #E+C0QG0''V*8!``!)`P``'0`````````!````MH$@.`@.``<W!3 65-2
M97-T;W)E1T1);G9E;G1O<GE$0BYS<6Q02P$"% `4````" !37#8Q],13L3H&
M``!#;0$`$ `````````!`" `MH$!! ``44%2=6Y*;V)2=6XN='AT;%!+!08`
1`````P`#`-(```!I"@.``````
`
end
|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Wednesday, February 15, 2012
Error accessing stored procedure containing a temp table from VB
I am getting an error trying to generate a disconnected recordset from a SQL 2000 database. The application is in VB 6. I am using an ADO Command object.
This code works fine for most of my storer procedures. 2 procedures make use of temporary tables. The results set being passed top VB is a select from the temp table.
When I use the temp table I get an error that states:
"Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
If I check the recordcount of rstResults, in the immediate window, after the .open method I get an error stating that the object is not open.
Again this is only for the stored procedure that Selects from a temp table.
Here is the code:
On Error GoTo ErrHandler
Dim rstResults As ADODB.Recordset
Dim conSQL As ADODB.Connection
Dim comFetch As ADODB.Command
Set conSQL = New Connection
conSQL.Open gstrAdminConn
'-- Associate the Command object to stored procedure --
Set comFetch = New ADODB.Command
With comFetch
Set .ActiveConnection = conSQL
.CommandType = adCmdStoredProc
.CommandText = "FetchChildren"
.Parameters("@.PageCode") = strPageCode
.Execute
End With
Set rstResults = New ADODB.Recordset
With rstResults
'-- Open a disconnected recordset off of the command --
.CursorLocation = adUseClient
.Open comFetch, , adOpenKeyset, adLockBatchOptimistic
'-- Dissociate command from connection --
Set comFetch.ActiveConnection = Nothing
Set comFetch = Nothing
'-- Dissociate recordset from connection --
'Set .ActiveConnection = Nothing
End With
Set GetChildren = rstResults
Exit Function
ErrHandler:
err.Raise vbObjectError + 1001, "basPages.GetChildren", Error$try using a
"set nocount on" at the begining of your procedure.|||Thanks. The SET NOCOUNT ON worked.
Why was that causing the error?
This code works fine for most of my storer procedures. 2 procedures make use of temporary tables. The results set being passed top VB is a select from the temp table.
When I use the temp table I get an error that states:
"Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
If I check the recordcount of rstResults, in the immediate window, after the .open method I get an error stating that the object is not open.
Again this is only for the stored procedure that Selects from a temp table.
Here is the code:
On Error GoTo ErrHandler
Dim rstResults As ADODB.Recordset
Dim conSQL As ADODB.Connection
Dim comFetch As ADODB.Command
Set conSQL = New Connection
conSQL.Open gstrAdminConn
'-- Associate the Command object to stored procedure --
Set comFetch = New ADODB.Command
With comFetch
Set .ActiveConnection = conSQL
.CommandType = adCmdStoredProc
.CommandText = "FetchChildren"
.Parameters("@.PageCode") = strPageCode
.Execute
End With
Set rstResults = New ADODB.Recordset
With rstResults
'-- Open a disconnected recordset off of the command --
.CursorLocation = adUseClient
.Open comFetch, , adOpenKeyset, adLockBatchOptimistic
'-- Dissociate command from connection --
Set comFetch.ActiveConnection = Nothing
Set comFetch = Nothing
'-- Dissociate recordset from connection --
'Set .ActiveConnection = Nothing
End With
Set GetChildren = rstResults
Exit Function
ErrHandler:
err.Raise vbObjectError + 1001, "basPages.GetChildren", Error$try using a
"set nocount on" at the begining of your procedure.|||Thanks. The SET NOCOUNT ON worked.
Why was that causing the error?
Subscribe to:
Posts (Atom)