Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: How to call a stored procedure from a DTS package

    How do i call/execute a stored procedure from a DTS package? I tried using the "Execute SQL Task", but I can't get the expected result. What other task or object should I use? I searched here already but can't get the answer to my prob. Hope you can help me. Thanks a lot.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is your expected result?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    i execute the stored procedure from Query Analyzer, the result is different when i execute the storedproc from a "Execute SQL task" in DTS package. I can't figure out why the behavior is like this. I tried creating sample simple tables, the execution is ok. But when I'm doing it in complex queries, i can't get the expected result. But the stored proc is running perfectly in the Query Analyzer. Don't know what's wrong.

  4. #4
    Join Date
    Oct 2005
    Posts
    119
    To explain further...this is the stored procedure, i used the simple tables i created to test if it will work before i apply it in my actual tables. I also created a dts package for this and it worked properly.

    But when I'm doing it in the actual tables, the result set is different as compared to when i execute the stored proc in the query analyzer.

    CREATE PROCEDURE sp_TEST
    AS

    BEGIN

    SET NOCOUNT ON
    --Declare Variables
    DECLARE @LastName varchar(10)

    --Declare Cursor
    DECLARE CursorLastName CURSOR FOR
    SELECT LastName FROM NAMES1
    ORDER BY LastName, FirstName, MiddleName

    --Open Cursor
    OPEN CursorLastName

    FETCH NEXT FROM CursorLastName
    INTO @LastName

    --Fetch all records
    WHILE @@FETCH_STATUS=0
    BEGIN
    --INSERT RECORDS TO DESTINATION TABLE
    INSERT INTO NAMES2
    SELECT * FROM NAMES1
    WHERE LastName=@LastName

    IF @@ERROR<>0
    BEGIN
    --INSERT RECORD TO ERROR TRAPPING TABLE
    INSERT INTO NAMES3
    SELECT * FROM NAMES1
    WHERE LastName=@LastName
    END

    --Get next from fetch
    FETCH NEXT FROM CursorLastName
    INTO @LastName

    END

    CLOSE CursorLastName
    DEALLOCATE CursorLastName

    END
    GO

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Are you sure the info is correct in the connection properties object (no pointing to staging server or something).

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    yup im sure the connection is correct. it seems like the execution of the DTS stops on that task where it's calling the storedprod...because not all records are inserted.

  7. #7
    Join Date
    Oct 2005
    Posts
    119
    is there some looping command i can use in replacement of cursor?

Posting Permissions

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