sql job agent hangs without completion

Question

sql job agent hangs without completion

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
April 3, 2008 at 10:03 am

#76942

It seems as though once or twice a month particular job shows status as "executing", when in actuality, it does not finish, and rather than send notification it fails, it just hangs. Via sp_who2, it shows status as runnable and the statement is an xp cmdshell. I tried isntalling the hotfix 2187 to see if that would fix it, obviously it did not. Anyone have this same issue?
¤ §unshine ¤

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 1

im experiencing the same error.

im trying to open google.com through the cmdshell program but its jsut says "executing query" for ages... no error message (yet)

have you managed to find a fix for this?

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 2

Is this for sql server 2k or 2k5? Are you running as a user or sa? It may be just the permissions. No fix for my issue yet.

¤ §unshine ¤

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

What is being run? It's possible you have executed something that is waiting for user input, and you'll never get that.

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 4

ok, i think i managed to sort it out.

i connected to SSMS with windows authentication and then gave my local user every flippin access i had on my coimputer 🙂

not sure which one sorted it out though.. gonna test

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 5

this is the exact script im running. It gives output "NULL", which is fine.

is there anyway i can get it to actually open up a new window? (i.e. not run silently in the background?)

thanks

use master

declare

@url as varchar(max)

set @url = 'xp_CMDShell "C:\PROGRA~1\INTERN~1\iexplore.exe http://www.google.com/"'

EXEC (@url)

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 6

Remember, xp_cmdshell is a synchronous process and control will not return to SQL Server until this process is complete. Therefore, when executing a process outside of the SQL Server environment via xp_cmdshell, any error that produces a dialog box with a prompt (such as OK/Cancel) will cause the agent job to hang.

You can look in the process monitor and find a cmd.exe process that can be killed returning control back to SQL Server.

DAB

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 7

hmm, it looks as if i need your help again here guys.

im running an application script that updates another DB in the same instance. the script on its own runs fine in these scenarios:

1. i run the script in an IE window.. updates fine

2. i run the script in a CMD (dos) window, it opens up an IE window on the desktop, loads the script and that runs fine.

however, when i run it from a query windows in SSMS i get output=NULL and no update on the table.

any clues here?

Is there a way to show the IE window?

thanks

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 8

ok, ive managed to get the script to run fine... however, the iexplore.exe and cmd.exe processes still stay open.

is there a way to close/stop these processes, maybe on a timeout?

thanks

James

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 9

ok, ive managed to use the dos command "start".

eg.

DECLARE

@username as varchar(255),

@password as varchar(255),

@command as varchar(255)

set @username = 'Administrator'

set @password = 'password'

set @command = 'start http://127.0.0.1/cgi-bin/command.exe/test.script' + 'username=' + @username + '&' + 'password=' + @password

EXEC master..xp_cmdshell @command

this above script works fine and does at least kill the process HOWEVER the ampersand (&) is not read properly in IE.

so my issue now, is how to get IE to not register the & as a special character.

I've tried placing the whole URL in " ", but not sure if im getting the code right...

any takers?

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 10

Are usernmae/password part of the query string?

Then use '?username=' + @username + '&' + 'password=' + @password

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 11

ok, got it right

http://www.windowsitlibrary.com/Content/405/29/2.html

to force the & symbol, I needed to type ^&

i also tried &amp

jamess-504304 SSC Journeyman Points: 92 More actions · Answer 12

ok, if i put a ^& then it takes the &

not too sure about the process though....