March 19, 2003 at 8:54 am
SQL Server 2k, sp2 on win2k OS
I have a scheduled job to import data (stored proc) and send email (xp_sendmail) to participants. Process hang. Cannot kill the process. I have tried to kill the process, now Command column (in process info) indicates Killed/Rollback for about 5 hours. (Cursors, copy files from directory, inserts, sendmail, xp_cmdshell contain in the stored proc)
Need help to kill the process. I appreciate your help.
Thanks
Edited by - htrivedi_2000@yahoo.com on 03/19/2003 09:22:34 AM
March 19, 2003 at 9:39 am
I'd worry that something popped up a dialog. Can you send other mail? Maybe the outlook startup is looking for a profile?
If it won't kill, then you probably need to cycle the SQL Service or reboot.
Steve Jones
March 19, 2003 at 9:52 am
run killl [SPID] WITH STATUSONLY
If it show time remaining 0, then the process won't die, so you will have to reboot sql server
March 19, 2003 at 2:12 pm
i had to restart the SQL Server. I was not able to kill the process.
When i ran killl [SPID] WITH STATUSONLY
got return value 0.
After rebooting the server, it's working fine.
March 19, 2003 at 2:13 pm
i had to restart the SQL Server. I was not able to kill the process.
When i ran killl [SPID] WITH STATUSONLY
got return value 0.
After rebooting the server, it's working fine. Thanks for your help.
March 19, 2003 at 3:38 pm
I lived a simmilar situation a few days ago with Sqlserver 7, the problem starts when the Exchange server went down. A process that executes xp_sendmail hangs up, and there wasn't any way to kill it, i had to restart the service...
March 20, 2003 at 12:48 am
I have similar problems when connecting to an Oracle box through Oracle Client. For some reason I cannot connect to Oracle and my processes just hang. I use a DTS package to connect to 50 oracle boxes and upload data into SQL Server, 5 boxes at a time. When I kill a process I get the same problems
March 20, 2003 at 8:21 am
Process has multiple steps. Calls for dtsrun, xp_sendmail, xp_cmdshell to copy file to archive directory, ftp to copy to other destination and run regular data manipulation within a stored proc. We have now started running process manually and run the profiler at the same time to track the error or at what point process hangs.
Thanks for your input. If anyone has more information about it. Please let us know.
Thanks
March 21, 2003 at 8:10 am
There is an article about this on the knowledge base. Basically you cannot kill a job that is sending email if the eamil is where it died. Since that is external to SQL.
March 24, 2003 at 5:52 am
There is another forum that discusses send e-mail out differently. Basically they load all information into tables (different tables for data location, recipients, schedules, etc...) From that system they send mail out.
I am hopeful that if you search this site you might be able to find it. Instead of re-booting next time try stopping/restarting the service and see if that helps and use task manager to kill outlook
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 24, 2003 at 8:31 am
We have tried to stop the SQL services but it did not work. When you click on Stop services, it would indicate that "stopping" the services and that's it. It never start back the services or does nothing at that point.
Thanks
March 24, 2003 at 9:36 am
I was afraid you might say that. However, it never hurts to try... =)
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 24, 2003 at 4:11 pm
I did try it, but did not work.
Thanks
March 25, 2003 at 7:49 am
If you can, look into what whoteegan suggests. There is some work and investment in setting it up, but at the end of the day you'll never worry about your mission critical DTS packages hanging up just because some mail program failed.
I'll try my best to locate the article.
good luck
John Zacharkan
John Zacharkan
March 26, 2003 at 7:08 am
Hey, i'm fighting with a similar situation right now...
W2k Server SP3, SQL Server St.Ed. SP4. The SQL mail start and stops fine, but when i try to execute xp_sendmail on the query analizer, the process hangs. If i cancel it, this is the output:
Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver]Timeout expired
[Microsoft][ODBC SQL Server Driver]Communication link failure
But the process still alive in the current activity (showing a execute command), and i can't kill it. If i try to stop the mssql service doesn't respond, and the only way to stop it is restart the service.
I've read the article in http://support.microsoft.com/default.aspx?scid=kb;EN-US;q263556 about the common situations, and i didn't see any trouble.
Also i can't change the way to send mails through sql because the aplications that use xp_sendmail are packed and can't change the code...
Sending and receiving mails from the outlook client works fine!
Suggestions?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply