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?

Did you see text file created when run the sp? Where did you call the sp? Which sql login called the sp?

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.

What do you mean 'have the necessary permissions'? Is it member of sysadmin?

Yes, sorry I was not clearer about that. The logins are members of sysadmin.

Then questions are:

What's sql service startup account?

Where do you put those text files?

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.

Did you trace the sp in profiler?

Hi, hope you had a nice holiday weekend. I've never really used Profiler. What should I be looking for in my trace?

Trace all sql related events for the sp, get details in books online.

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!

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.

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.