Force DTS Failure
-
Hi all I'm trying to write a DTS that as it's first step checks a log table as to whether the process has already run for that date, and if it has, exit. I thought I'd be able to have an 'Execute SQL Task' with something like the following: DECLARE @ContributionDate DATETIME DECLARE @NoOfRecords INT SET @ContributionDate = GetDate() CREATE TABLE #Results(NoOfRecords INT) INSERT INTO #Results EXEC db.dbo.vsDissemination_HasRun @ContributionDate, 'C' SET @NoOfRecords = (SELECT TOP 1 NoOfRecords FROM #Results) DROP TABLE #Results IF @NoOfRecords > 0 BEGIN RAISERROR('Dissemination has occurred',1,1) END ELSE SELECT 1 Which when the error was raised would go down the On Failure branch, but it always goes to the On Success branch. Running the script in Query Analyzer does either return a numeric value or an error. Firstly, is this the best way to do this check? If it is, why doesn't it work - if not, how should I be doing this? Thanks Ben
-
Hi all I'm trying to write a DTS that as it's first step checks a log table as to whether the process has already run for that date, and if it has, exit. I thought I'd be able to have an 'Execute SQL Task' with something like the following: DECLARE @ContributionDate DATETIME DECLARE @NoOfRecords INT SET @ContributionDate = GetDate() CREATE TABLE #Results(NoOfRecords INT) INSERT INTO #Results EXEC db.dbo.vsDissemination_HasRun @ContributionDate, 'C' SET @NoOfRecords = (SELECT TOP 1 NoOfRecords FROM #Results) DROP TABLE #Results IF @NoOfRecords > 0 BEGIN RAISERROR('Dissemination has occurred',1,1) END ELSE SELECT 1 Which when the error was raised would go down the On Failure branch, but it always goes to the On Success branch. Running the script in Query Analyzer does either return a numeric value or an error. Firstly, is this the best way to do this check? If it is, why doesn't it work - if not, how should I be doing this? Thanks Ben
Try
RAISERROR('Dissemination has occurred',16,1). -
Try
RAISERROR('Dissemination has occurred',16,1).Thanks Andy - The error is now being thrown (if I run the package in the designer) but it doesn't go to the On Failure step - is that expected? Ben