If the object already exists the Agent Job will be logged as errorred rather than success.
In this situation, the procedure is simply not going to be there again (at least not until I update the bigger process), so I'm fine just running the create. When it's time to update the process again, I can just delete the job step if it is not needed.
Or you could just set the step to go to next step on failure....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
If the next step is to check why it failed that would be fine.
If it failed because it already exists then the job can continue but if it failed for other reasons it should probably stop.
Not sure if you can specifically identify the cause of the failure but you can at least check for the existance of the function afterwards. If it already existed it will still be there, if it was created it will now exist, if it failed for some other reason then terminate the job.
Alternatively you could have a job step which just checks for the existance of the function and fails the step if it doesn't exist with the create function as the next step.
The other option is to use dynamic SQL:
IF NOT EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[SCHEMA].[FUNCTION_NAME_HERE]')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
EXEC('CREATE FUNCTION [SCHEMA].[FUNCTION_NAME_HERE]
...');
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Check IF Exist For Function
IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE
object_id = OBJECT_ID(N'[Schema].[function_Name]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [Schema].[function_Name]
Print('function dropped => [Schema].[function_Name]')
END
GO
Check IF Exist for Stored procedure , Function also by clicking below link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html
Please note: 4 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply