I executed a stored procedure that returns a SQL statement under 'Messages' tab after completion. This query window in SSMS is now showing disconnected and has "transport-level error has occurred" in results frame. I can see the procedure is still running in Activity Monitor but how will I be able to access the results when it completes?
Asked
Active
Viewed 375 times
1 Answers
4
As soon as the connection was lost the transaction was rolled back. If it was doing a lot of work and it ran for some time before the connection was lost, what you are seeing in the activity monitor is the rollback which could take awhile.
Type EXEC sp_who2 to see more of what is going on.
If you want the procedure to keep running, and you are on SQL Server Standard or a newer version of SQL Server Express, use SQL Agent to create a task (you don't need to schedule it) and then run it. It will stay running no matter if you lose your connection.
If you have access to the server running the database and you have SQL Express use the NT Scheduler to fire off a batch script that uses the sqlcmd utility to fire the stored procedure.
quickdraw
- 51
- 5