Alternative to using xp_cmdshell

Question

Post reply

Alternative to using xp_cmdshell

Viewing 15 posts - 16 through 30 (of 32 total)

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

C# Gnu SSCertifiable Points: 5973 More actions · Answer 1

That looks interesting

I guess you need a Job to look for the messages to start the other Jobs?

Something like:

RECEIVE TOP (1) * FROM MyReceivingQueue

I have go out now ... but looks useful

🙂

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

It is a bit more than that but that is the basis. I use "internal activation" so that I don't have to schedule another process it just pops into being and services the queue and then pops out..

CEWII

C# Gnu SSCertifiable Points: 5973 More actions · Answer 3

Ah I see

http://articles.techrepublic.com.com/5100-10878_11-6156264.html

ALTER QUEUE [SalesQueue] WITH ACTIVATION

( STATUS = ON,

MAX_QUEUE_READERS = 1,

PROCEDURE_NAME = usp_RecordSaleMessage,

EXECUTE AS OWNER

);

Rather like a trigger - but as you say with any rights you like..

Now I am going out!

Thanks

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Kind of like an asynchronous trigger, that can run in any security context you want..

CEWII

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

pawan_kirtikar (12/4/2009)
Hi,
Due to security reasons we cannot use the xp_cmdshell command to execute the system level calls through the stored procedures. We want to execute batch files at a specified time using the SQL Server 2005 stored procedures.

As much as I am a fan of Service Broker (Queues, RECEIVE, etc...), I don't think that it should be necessary here. Given the OP's requirements ("at a specified time"), the SQL Agent should be able to handle it all by itself without anything else, including the system stored procedures. Just schedule it to run at the required time. Done.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

C# Gnu SSCertifiable Points: 5973 More actions · Answer 6

Yes I thought that, but does

at a specified time

mean at regular intervals or does it mean at the time the Application wants to call it?

{edit - the mention of stored procedures in post makes one think OP has reason for not using SQL Agent - but we will have to see}

Maybe OP could clarify ....

Waiting 4 taxi..

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

C# Screw (12/4/2009)
Yes I thought that, but does
at a specified time
mean at regular intervals or does it mean at the time the Application wants to call it?
Maybe OP could clarify ....
Waiting 4 taxi..

The later would be "at the requested time", and not the "specified" time. Specifications happen before the application, not during it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

C# Gnu SSCertifiable Points: 5973 More actions · Answer 8

The later would be

do you mean latter rather than later!

Easy to have wrong meaning in posts 😀

c#

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Yes, the latter.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

Heh... wait just a minute, now. Let's ask the question... specifically, WHAT do those batch files do? You may not need to use anything in those batch files.

Also, what's wrong with setting up the proxy for xp_cmdshell for those jobs?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (12/5/2009)
Heh... wait just a minute, now. Let's ask the question... specifically, WHAT do those batch files do? You may not need to use anything in those batch files.
Also, what's wrong with setting up the proxy for xp_cmdshell for those jobs?

Actually, the question isn't whether they need a batch file, it's whether or not they actually need to involve SQL Server in this at all.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 12

Heh... and I was thinking the other way around. Depending on what those batch files do, you may not have to use anything BUT SQL Server. 😛

--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)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

I'd personally go the route that Elliot first proposed and use SQL Agent with CmdExec tasks and then use sp_start_job in a certificate signed wrapper stored procedure. Using the same certificate a server level login can be created that no one else can use, and can be granted the rights necessary to call into sp_start_job for any job on the server. The application users only need EXECUTE rights to this wrapper stored procedure and nothing else. The certificate takes care of the rest of the permissions issues. Its not that tough to do. If you'd like to see the code to make it happen I'd be happy to stub out an example and post it here.

C# Gnu SSCertifiable Points: 5973 More actions · Answer 14

certificate signed wrapper stored procedure

sounds like an excellent solution for elevated sp permissions.

(not that we are sure of the problem, maybe OP is on holiday)

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 15

And here is link to one of Jonathan's articles, of relevance :

Trading in xp_cmdshell for SQLCLR

http://www.sqlservercentral.com/articles/SQLCLR/65656/

Although Jonathan's elevated stored proc with certificate sounds most useful.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015