Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2002
    Posts
    62

    Post Unanswered: 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.

    -Bheem

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Not that familure with DTS but how about RAISERROR?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    May 2002
    Posts
    62
    Paul,

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

    -Bheem

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Using RAISERROR is the correct answer. Please post your code that did not function correctly using RAISERROR.

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    A RAISERROR will trigger the 'On Failure' workflow. This was used in the flowing past post

    Problem with package creation
    MCDBA

  6. #6
    Join Date
    May 2002
    Posts
    62
    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

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried the same code but leaving out the "if" statement ? Basically, forcing a RAISERROR.

  8. #8
    Join Date
    May 2002
    Posts
    62
    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

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    What version/service pack of sql server are you using ?

  10. #10
    Join Date
    May 2002
    Posts
    62
    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

  11. #11
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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
    Attached Files Attached Files
    MCDBA

  12. #12
    Join Date
    May 2002
    Posts
    62
    achorozy,

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

    thanx..
    -Bheemsen

  13. #13
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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 to
    Code:
    if (SELECT DATEPART(ms,GETDATE())) < 500
           raiserror('',16,1)
    3) Create ActiveX Script called Good, code
    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
    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.
    MCDBA

  14. #14
    Join Date
    May 2002
    Posts
    62
    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

  15. #15
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Now I've changed my SQL Task.

    I created a stored procedure :
    Code:
    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
    Code:
    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).
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •