-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|