April 16, 2010 at 2:21 am
Hi there.
I got this simple SP which only deletes records based on some criteria:
begin try
delete from kundenrepl.dbo.tbl_kraft_bss_txt
where id in
(
select txt.id
from kundenrepl.dbo.tbl_kraft_bss as bss inner join
kundenrepl.dbo.tbl_kraft_gebiete as g on
bss.x1combera_id = g.combera_id inner join
kundenrepl.dbo.tbl_kraft_kundengruppen as kgr on
g.kraft_kgr = kgr.x1kgr inner join
kundenrepl.dbo.tbl_kraft_bss_txt as txt on
bss.id = txt.id_bss
where bss.x2besuchsdatum >= '05.02.2010'
)
print cast(@@rowcount as varchar(10)) + ' records deleted.'
end try
begin catch
declare @msg varchar(max)
declare @severity int
declare @state int
set @severity = error_severity()
set @msg = error_message()
set @state = error_state()
raiserror(@msg,@severity,@state)
end catch
If I run this procedure in QA it works great. When running from the job the @@rowcount is always 0 (although there are matching records) and no error is returned. The Stored Procedure deletes from a replicated database but lies in another one if this is important.
Thanks in advance,
Steffen.
April 16, 2010 at 12:56 pm
just a suggestion.
delete from kundenrepl.dbo.tbl_kraft_bss_txt
where id in
(
select txt.id
from kundenrepl.dbo.tbl_kraft_bss as bss inner join
kundenrepl.dbo.tbl_kraft_gebiete as g on
bss.x1combera_id = g.combera_id inner join
kundenrepl.dbo.tbl_kraft_kundengruppen as kgr on
g.kraft_kgr = kgr.x1kgr inner join
kundenrepl.dbo.tbl_kraft_bss_txt as txt on
bss.id = txt.id_bss
where bss.x2besuchsdatum >= '05.02.2010'
)
is the same as below ...dont need to join back to it again. the join has already been made....dont need the in.
delete txt
from kundenrepl.dbo.tbl_kraft_bss as bss inner join
kundenrepl.dbo.tbl_kraft_gebiete as g on
bss.x1combera_id = g.combera_id inner join
kundenrepl.dbo.tbl_kraft_kundengruppen as kgr on
g.kraft_kgr = kgr.x1kgr inner join
kundenrepl.dbo.tbl_kraft_bss_txt as txt on
bss.id = txt.id_bss
where bss.x2besuchsdatum >= '05.02.2010'
April 19, 2010 at 1:00 am
Hi.
Thanks for this, I usually use the IN-clause because it's easier to check which records will be deleted and in addition I didn't know one could do it the way you described. 😉
But the problem that nothing is deleted when I run the SP from a job still exists.
If there were permission problems the job would fail, wouldn't it?
Suggestions?
Steffen.
April 20, 2010 at 7:03 am
How are you testing this in the job? Running the select to confirm there are rows then immediately running the job?
And do you know no rows were deleted or are you assuming that based on the rowcount?
April 21, 2010 at 12:59 am
cfradenburg (4/20/2010)
How are you testing this in the job? Running the select to confirm there are rows then immediately running the job?And do you know no rows were deleted or are you assuming that based on the rowcount?
I'm sure that nothing is deleted, because I run the job which completes successful and then run the SP with begin/rollback transaction. The job history says that 0 rows were deleted, but the SP would.
April 21, 2010 at 5:36 am
The only thing I can think of is that there's some difference between what's in the job and what you're running manually. Either a different date or possibly something in the SP that updates the date.
May 5, 2011 at 12:52 pm
Has this issue been resolved? If so, how? I'm encountering the same issue and I am kinda clueless why it doesn't work when run in the job.
May 6, 2011 at 5:55 am
Hello,
a problem I encountered at some times is assuming wrong date formats. In your query you have a clause,
where bss.x2besuchsdatum >= '05.02.2010'
and this clause could be the origin of your problems. Maybe you run this SP in QA with a default date format, and you run the job with another default date format?.
you can try to write this date as '20100502' (locale independent format, I think), or better if you explicitly use CONVERT with a specific date format.
Regards,
Francesc
May 6, 2011 at 9:10 am
Exactly: the date was the problem. Sorry for not coming back.
Since I found this out, I always write dates in the format frfernan suggested because it is language independent.
E.g.: 14th March 2011 == 20110314
This is also in MSDN, but I didn't find it quickly.
Greetings
Steffen
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply