I want to run a batch file from query window or from Trigger.

Question

Post reply

I want to run a batch file from query window or from Trigger.

forsqlserver

SSCoach

Points: 18902
More actions
April 11, 2012 at 4:56 am

#259742

I want to run a batch file from query window or from Trigger.
But I am able to it from SSIS not from SSMS.
Please help.
Thanks

Viewing 15 posts - 1 through 15 (of 29 total)

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

Perry Whittle SSC Guru Points: 233855 More actions · Answer 1

can you supply a little more info on exactly what you're doing and any errors you receive

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Hi,

Thanks,

I am using XP_CMDSHELL.

On running: Exec XP_CMDSHELL 'c:\te.bat';

It continuosly goes to executing query state.

Thanks

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

EXEC xp_cmdshell 'c:\whatever.bat'

By default, xpcmdshell is disabled because inproper use is often a massive security hole. If you're going to use it, make sure no-one has access to run it directly instead having to run it through a SPROC that you can give permission to only the correct users.

--edit--

Seems I typed a little slow. I stand by my comment on permissions.

How long does the batch file take when you run it manually?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

forsqlserver SSCoach Points: 18902 More actions · Answer 4

Yes it has enabled from sp_configure and changed to value 1.

Thanks

Perry Whittle SSC Guru Points: 233855 More actions · Answer 5

forsqlserver (4/11/2012)
Hi,
Thanks,
I am using XP_CMDSHELL.
On running: Exec XP_CMDSHELL 'c:\te.bat';
It continuosly goes to executing query state.

So, am i correct in saying you can execute this from SSMS query window but not from SSIS package?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

forsqlserver SSCoach Points: 18902 More actions · Answer 6

But I am able to run it from SSIS not from SSMS.

Actually I am using Execute Process Task in Control Flow.

Thanks

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 7

Does the account which is being used by sql service has read & execute permission on the bat file ?

--

Ashok

forsqlserver SSCoach Points: 18902 More actions · Answer 8

It has full control...as I am the local server admin of the db server and file is created in c:\users\dbadmin\te.bat

and I have checked the security tab also. full permissions are provided to dbadmin.

Thanks

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 9

There seems an issue with bat file, if you are bale to execute this then problem may lies with bat file

and can you also please let me know whether sql service is being run under which account . Is it dbadmin or someone else like local system etc.

just try it in query window :-

exec master.dbo.xp_cmdshell 'dir'

and post your bat file.

--

Ashok

vinu512 SSCoach Points: 15729 More actions · Answer 10

.bat files when run using the xp_cmdshell will always give you only the process state....it actually gives you a table which has a column which would actually show you the code of your batch file....Am I right?...

Try these steps :

1. Create an SSIS package using the Execute Process task.

2. Create a job and call the package as a step of the job.

3. Run the Job.

This worked for me.

Hope that helps you too.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

forsqlserver SSCoach Points: 18902 More actions · Answer 11

This can be possible but i my actual scenario I have to call the batch file from a trigger.

Therefore I am trying it using SSMS first.

Thanks

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 12

I can run the batch file on my server perfectly and got the result like if i run the command directly

just run the command directly using xp_cmdshell very simple like dir the use the same command in a bat file then run bat file, it worked perfect;y on my server..

vinu512 SSCoach Points: 15729 More actions · Answer 13

This is how you call a job inside a trigger:

CREATE TRIGGER <TriggerName>

ON <TableName>

AFTER INSERT, UPDATE, DELETE

AS

EXEC msdb..sp_start_job

@job_name = '<JobName>';

GO

Hope this helps you.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

forsqlserver SSCoach Points: 18902 More actions · Answer 14

Hi,

Thanks,

Now My package is not showing in Management studio when I am selecting 'Integration sevices' option from connection windpw.

Thanks