DTS - SQL Task question

Hi,

I have a DTS package. One of the steps is SQL task step. I have the following code under SQL statment.

declare
@.p_dtProcessDate DateTime,
@.p_iResultCode int
SET @.p_dtProcessDate = getdate()
EXEC SPCreateMLRDailyRpt @.p_dtProcessDate,@.p_iResultCode out

I want to evaluate @.p_iResultCode and if it is not equal to zero, I want to make this SQL task step to be failed so that the entire DTS package itself fails.

Does any one know how to do this ? I appreciate all your help.

-BheemNot that familure with DTS but how about RAISERROR?|||Paul,

Thanx for the response. But that does not fail the step. It says DTS package completed successfully.

-Bheem|||Using RAISERROR is the correct answer. Please post your code that did not function correctly using RAISERROR.|||A RAISERROR will trigger the 'On Failure' workflow. This was used in the flowing past post

Problem with package creation (http://dbforums.com/showthread.php?s=&threadid=314650)|||My code is below.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step is completed successfully without any problem. What am I doing wrong here ?

Thanx for all the responses.

-Bheem|||Have you tried the same code but leaving out the "if" statement ? Basically, forcing a RAISERROR.|||Yes, I did. It is not working either. Basically RAISERROR is not finctioning in DTS.

Does anyone know why this code is not working ?

-Bheem|||What version/service pack of sql server are you using ?|||I am using

SQL Server 7.0
Version 4.0
Service pack 6

If I use the following in DTS SQL task it works.

declare @.test int
set @.test = 0
if @.test = 0
RAISERROR('Failed', 16,1)

But RAISERROR is not working when I use EXEC <stored proc>
Thanx..
-Bheem|||I've created a small DTS package to test RAISERROR. I use a OLEDB connect that points to the local server and Pubs database using NT authentication. It starts with a SQL Task that get the milliseconds of the current time using DATEPART. If the value is less than 500 then RAISERROR and else do nothing. The SQL Task has 2 workflows ON SUCCESS and ON FAILURE, each with a message box. I've attached the DTS package see if you can run this.

I had to rename the extension from .DTS to .TXT so that I could attach|||achorozy,

The attachment is unreadable. Can you please paste your code here.

thanx..
-Bheemsen|||It's a DTS package that you can't read with notepad but import into DTS using Enterprise Manager.

Long form:

1) Create OLEDB connection to any database
2) Create SQL Task, set connection to OLEDB created in (1). Set SQL statement toif (SELECT DATEPART(ms,GETDATE())) < 500
raiserror('',16,1)
3) Create ActiveX Script called Good, code
Function Main()
Msgbox "Good"
Main = DTSTaskExecResult_Success
End Function
5) Set workflow 'ON SUCCESS' from SQL Task to ActiveX 'Good'
6) Create ActiveX Script called Bad, code
Function Main()
Msgbox "Bad"
Main = DTSTaskExecResult_Success
End Function
7) Set workflow 'ON FAILURE' from SQL Task to ActiveX 'Bad'

Run the package, it should flip from msgbox 'Good' to 'Bad' as the milliseconds in GETDATE() change from over 500 to under 500.|||achorozy,

Thanx for your response. Your code perfectly works. As I said, I had already tested RAISERROR with a SQL statment in the SQL task. That works.

But the RAISERROR is not working in my following code.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step says it is "completed successfully", it should fail actually. What am I doing wrong here ?

-Bheemsen|||Now I've changed my SQL Task.

I created a stored procedure :
CREATE PROC sp_Test (@.x int OUTPUT)
AS
SET NOCOUNT ON

SELECT @.x = DATEPART(ms,GETDATE())
Then I modified my SQL Task's SQL Statement to be
declare @.x int

exec sp_Test @.x OUTPUT

IF (@.x < 500)
raiserror('',16,1) This works fine for me. I get the RAISERROR sometimes (@.x < 500) and sometomes not (@.x >= 500).|||Are you still having the problem ?|||rnealejr,

Yes, I still have the problem. It is really weird. The same code works well in query analyzer, but not in DTS sql task step. It never fails there.

Your code is very perfect. It is wroking fine in DTS sql step as well as in query analyzer.

Looks like may be something wrong with my procedure. It is working fine when I write a small stored procedure and test the same return code in sql task step.

It is still a mystery for me.

Thanx for all responses. Please let me know if you find something for me. All the information regarding my problem is in this thread.

-Bheemsen|||There is a problem with the OLEDB provider for SQL. You need to download the latest mdac for your machine. If install version 2.6 or greater you should be ok. This should correct the non-failure in your dts script. If not, let me know.

Good luck.|||rnealejr,

I am sorry to ask this question. How do I check my OLEDB version and from where I should download the latest mdac ?

Thanx for all your help.
-Bheemsen|||Search for sqloledb.dll - right click on the file and go to properties - go to the version tab. Let me know which version you have installed.|||The OLEDB version installed is 07.01.0819

When I run "select @.@.version" in query analyzer, I got the following.

Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

Thanx..
-Bheemsen|||Go to the following web site for mdac version 2.6:

mdac (http://www.microsoft.com/data/download_260rtm.htm)

After the installation, make a note as to which version is installed now. It should begin with 2000. You can also upgrade to 2.7 version.
FYI: There is a service pack for 2.6.

Good luck.|||rnealejr,

Thanx a million for all that help. It worked like a charm. It looks like this bug fix is incorporated by Microsoft in its recent SP4 also. I had installed SP4 too on my machine. I need to inform my production support people to install the same.

Thanx again.
-Bheemsen|||Good deal. Be careful, because not even sp4 has the latest drivers and your dts problem will still be around. You can test it out on your existing servers and let me know (or uninstall the sp4 on your machine).

Good luck.|||Thanx. I will. How did you figure out this fix ??

Also, when you have time, can you please take a look at my another question at thread "Delete query to remove duplicate rows"

-Bheemsen