Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unanswered: Problem looping DTS and BCP within a Stored procedure


    This is the scenerio

    SP=Stored Procedure

    I call SP1 which calls a DTS package.

    DTS package calls SP2 and SP2 calls SP3 and SP 4 using an IF condition

    SP3 has 3 BCP OUT commands after which it calls SP1 again

    Now, the problem is that if I follow all the steps, then only 1 of the BCP command executes and the control exits out of SP3 and returns control to SP1.

    If I ignore SP1 and DTS and run SP2 independently, then all BCP steps in SP3 are executed and control returns to SP2

    I ma not sure if using DTS and BCP in SQL stored procedures might cause any problems

    Appreciate your help

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    It seems like your DTS is executing under a different security context than when you execute your SP3 from QA. Are you checking for errors after each BCP in your SP3? If you're calling your BCP using xp_cmdshell, - consider doing INSERT @temp_tbl exec master.dbo.xp_cmdshell [.....].

Posting Permissions

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