Results 1 to 13 of 13

Thread: Stored proc hangs when DTS trigger is included

  1. #1
    Join Date
    Nov 2006
    Posts
    42

    Stored proc hangs when DTS trigger is included

    Hello:

    I have a stored procedure (SQL Server 2000, SP4) that inserts data into two database tables. It is then set to run a DTS package using xp..cmdshell. The DTS package exports the database tables to .txt files and then uploads those to a DB2 database.

    The stored proc without the DTS trigger works fine, and if I run the DTS trigger from Query Analyzer, it works fine also. But if I include the xp..cmdshell command in my stored proc, it hangs.

    I have other stored procedures triggering DTS packages and I am not having any issues with them.

    Any ideas what might be causing this?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Did you see text file created when run the sp? Where did you call the sp? Which sql login called the sp?

  3. #3
    Join Date
    Nov 2006
    Posts
    42
    I do not see the text file. The SP is typically triggered by a button in a web application (ASP.NET), but I have also reproduced the problem when trying to run the SP in Query Analyzer. The logins used all have the necessary permissions.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    What do you mean 'have the necessary permissions'? Is it member of sysadmin?

  5. #5
    Join Date
    Nov 2006
    Posts
    42
    Yes, sorry I was not clearer about that. The logins are members of sysadmin.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Then questions are:

    What's sql service startup account?

    Where do you put those text files?

  7. #7
    Join Date
    Nov 2006
    Posts
    42
    The startup account is set to "Local System Account." The files are being stored in a folder on the same server. I made sure that "NETWORK SERVICE" has full permissions on this folder.

    Again, the package runs successfully if I launch it from Query Analyzer; it just hangs when I try to run it from within the stored procedure or trigger it from the web app.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Did you trace the sp in profiler?

  9. #9
    Join Date
    Nov 2006
    Posts
    42
    Hi, hope you had a nice holiday weekend. I've never really used Profiler. What should I be looking for in my trace?

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Trace all sql related events for the sp, get details in books online.

  11. #11
    Join Date
    Nov 2006
    Posts
    42
    This is interesting. When I ran each individual step in the stored proc, it ran fine. So I commented out all the steps related to error-checking (IF @@ERROR !=0 etc.), then reran the whole process and it ran just fine. So I think my issue is in the way the error-checking is set up.

    I think I can take it from here; thanks so much for all your help and quick replies!

  12. #12
    Join Date
    Jan 2008
    Posts
    1
    If you are using a DTS package, why not have the DTS package run the stored procedure (using Execute SQL task) then use a workflow that upon success it runs the export.

  13. #13
    Join Date
    Nov 2006
    Posts
    42
    The stored procedure is triggered by a web app. The DTS package is then triggered by the SP but only if certain conditions are met.

    I figured out the problem; it was some poorly placed "BEGIN TRAN" and "COMMIT TRAN" statements.

Posting Permissions

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