Executing SQL Job from SProc

Question

Executing SQL Job from SProc

john.clyma 97764

Mr or Mrs. 500

Points: 501
More actions
June 15, 2015 at 8:51 am

#305122

I created a job to run a .exe file. Long story short, I need to call a web service and it isn't allowing me to from SQL (permission issues). I need to get this up and running so I opted to put the call to the web service in some .net code and call the executable from SQL. So I placed the .exe in a folder and created a job to execute it. It is a 1 step job and the line of code to execute is: C:\Scripts\NETradeService.exe. If I just double click the file in its folder, it runs fine. If I execute the job (right-click Start Job) it just spins and I need to right click and stop. I checked the Job History and it doesnt say much other than: The job was stopped prior to completion by User TRADE\administrator. The Job was invoked by User TRADE\administrator. The last step to run was step 1 (Step1).
It is the same if I execute the Job from the SProc. Any thoughts? I am new to forums, so if additional information is needed let me know and I will let you know.
John

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

The job runs under the context of the Agent service or the proxy. Be sure that account has rights. You might log in as that account and then try to run the .exe.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Which box is the .exe actually on? The SQL Server box or something else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 3

I am not a SQL admin by any means, but I know enough to be dangerous and that is not always a good thing I know. Which account are you speaking of?

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 4

john.clyma 97764

Mr or Mrs. 500

Points: 501

June 15, 2015 at 9:02 am

#1805479

The .exe is on the same box that is running SQL

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

john.clyma 97764 (6/15/2015)
The .exe is on the same box that is running SQL

Perfect. The other questions would be...

1. Does the exe require any user input after it starts?

2. Does it exit properly back to the command prompt when it's complete when you run it manually?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 6

Jeff - It doesnt require any input. I dont have the exe open a command box. It calls the service, updates the db and closes itself.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Definitely a puzzle. My apologies. Hopefully, someone else can help on this problem because I'm out of tricks on this one. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 8

Ok, a little more info...hope this helps. I created a new exe and removed the call to the Web Service and the Job runs successfully. So what accounts may be in play for calling that web service from SQL.

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 9

One more thought, in the Job, can I specify a user to run the exe as?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

http://www.sqlservercentral.com/articles/Stairway+Series/72461/

The likely cause if your call to the service might be looking for a password. That would get the .exe to freeze.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 11

Why don't you just create an SSIS package and call the webservice (using the WebService Task) from the package? This will allow you to pass in the necessary credentials to the webservice (if required) and allow for other error handling that the webservice might be returning.

====edited===

You would then edit your SQL Agent job and replace the step which calls the existing .exe with the SSIS package.

Steve

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 12

Ok, I have a little more info...I now have it to where I can connect and execute the job and it works. There is a SQL account CACM that is calling the Job. It starts it, but never stops. My guess is that the SQL account does not have permissions to run the exe. So, is there a way to (pardon my lack of terms) impersonate another account when it comes to calling the Job?