November 6, 2008 at 8:59 am
I have the following SP that when run in the query window updates my data perfectly:
alter proc dbo.inow6_AlterDatefield
as
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set nocount on
GO
update in_doc
set F3 = stuff (F3, 3,1, '/')
from in_doc
where creation_usr_id = '2000000001_00011B3EPCXJ'
and creation_time > getutcdate()-1
GO
update in_doc
set F3 = stuff (F3, 6,1, '/')
from in_doc
where creation_usr_id = '2000000001_00011B3EPCXJ'
and creation_time > getutcdate()-1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
However when I create an Agent Job that basically executes the above stored proc it will run sucessfully but not actually update the data as set out in the proc...
I've thought it was permission based but the domain account that has full access to the DB files has been assigned as the owner of the job and still the same behaviour plus again when running the proc in analyzer logged into the DB as myself it works like a champ...
Appreciate any ideas anyone can offer...
TH
November 6, 2008 at 9:09 am
try like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set nocount on
go
alter proc dbo.inow6_AlterDatefield
as
begin tran
update in_doc
set F3 = stuff (F3, 3,1, '/')
from in_doc
where creation_usr_id = '2000000001_00011B3EPCXJ'
and creation_time > getutcdate()-1
update in_doc
set F3 = stuff (F3, 6,1, '/')
from in_doc
where creation_usr_id = '2000000001_00011B3EPCXJ'
and creation_time > getutcdate()-1
commit tran
set ansi_nulls off
go
set quoted_identifier off
go
November 6, 2008 at 11:10 am
That did the trick....thank you very much for the insight!!!
I did some follow up reading on the begin and commit tran statements and I think I understand better now why it would run sucessfully but not actually alter the column.
Thanks again for your help 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply