# Related Sites > SQL Course >  Stored proc hangs when DTS trigger is included

## jjkatz

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?

----------


## rmiao

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

----------


## jjkatz

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.

----------


## rmiao

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

----------


## jjkatz

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

----------


## rmiao

Then questions are: 

What's sql service startup account?

Where do you put those text files?

----------


## jjkatz

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.

----------


## rmiao

Did you trace the sp in profiler?

----------


## jjkatz

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

----------


## rmiao

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

----------


## jjkatz

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!

----------


## kamikazekid

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.

----------


## jjkatz

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.

----------

