April 27, 2011 at 3:10 pm
I need to change this statement. It does work the way it is but..
I need to evaluate each one of these, and if a record exists then fire off the job.
My problem is the way it's written below I get a job already in use error if one of the previous statements is true.
I know I need to evalute the entire statement and then fire off the job at the end but I don't know how to write it.
IF Exists
(select 1
from [VSMIAD1].DynamicsGP_Utilities.DBO.[BankImport]
where GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source in
(select b.ACTNUMST from [VSMIADBGP01].[LPB].[dbo].[GL00100] a
inner join [VSMIADBGP01].[LPB].[dbo].[GL00105] b on a.ACTINDX = b.ACTINDX where a.active = 0))
BEGIN
exec msdb..sp_start_job @Job_Name = 'ABE4CD9C-9CA0-4E8B-A109-9B2A9A832C5E'
END
GO
If not Exists
(select GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source as BankImport, b.ACTNUMST as GreatPlains
from [VSMIAD1].DynamicsGP_Utilities.DBO.[BankImport] as a
left outer join [VSMIAD1].[LPB].[dbo].[GL00105] as b
on GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source = b.ACTNUMST
where Len(GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source) = 26
and a.GP_Company in ('00')
and b.ACTNUMST is null)
BEGIN
exec msdb..sp_start_job @Job_Name = 'ABE4CD9C-9CA0-4E8B-A109-9B2A9A832C5E'
END
GO
If not Exists
(select GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source as BankImport, b.ACTNUMST as GreatPlains
from [VSMIAD1].DynamicsGP_Utilities.DBO.[BankImport] as a
left outer join [VSMIADBGP01].[LPBE].[dbo].[GL00105] as b
on GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source = b.ACTNUMST
where Len(GP_Company + '-' + GP_Division + '-' + GP_Department + '-' + GP_ACcount + '-' + GP_Location + '-' + GP_Source) = 26
and a.GP_Company in ('01')
and b.ACTNUMST is null)
BEGIN
exec msdb..sp_start_job @Job_Name = 'ABE4CD9C-9CA0-4E8B-A109-9B2A9A832C5E'
END
GO
April 27, 2011 at 3:21 pm
if Exists (select 1 ...)
begin
exec ...
end
else
if Exists (select 1 ...)
begin
exec ...
end
else
if Exists (select 1 ...)
begin
exec ...
end
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 27, 2011 at 3:30 pm
I didn't notice they were firing the same job. Do this:
if Exists (select 1 where 1=1)
and
not Exists (select 1 where 1=0)
and
not Exists (select 1 where 1=0)
exec...
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 29, 2011 at 7:00 am
awesome thanks everyone..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply