Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Scheduling VB script job in SQL server agent

Author  Topic 

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-07 : 02:23:24
Hi All,

I have a windows scheduling VB script job(.bat file) in another windows server where SQL is not installed,i need to schedule these job in SQL server through agent so that i can have an alert when it fails.

Any suggestions in this regard will useful.


Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 02:34:24
you need to add a T-SQL script using xp_cmdshell for executing bat from SQL Agent job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 02:34:53
Let's cal the sql server "ServerA" and the other windows server "ServerB":

Why do you need to run it from sql server agent? If you run the .bat-file from ServerA it will be executed as if it was local to the ServerA, not ServerB. I can be done using unc-paths (create a share on ServerB so that the file is accessible from ServerA) but the execution would still be local to ServerA.

It would probably be better to use Task Scheduler on ServerB instead.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-07 : 02:44:26
Thanks Lumbago.
Can we have a email alert with Task scheduler with windows server 2003 ?

If yes how can we do this ?

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 02:48:54
Hm, I can't remember exactly how scheduled tasks work with Win2003 but at least on 2008 (and Windows7) you can set up multiple actions for your scheduled job, one of them being sending email.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 03:02:16
you can have a email alert with sql server agent though for jobs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-07 : 03:06:03
Hi Visakh,
How can we schedule the .vbs file in sql agent through SQL with the .vbs/.bat file located in sever B where it does not have sql instance, i need to schedule it in another sql server ,pls guide me.

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 03:45:22
you can use unc path like \\serverB\...\xxxx.bat and use xp_cmdshell to execute the file from sql agent job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:29:25
Do you want the task to execute on Server B where the file is located or on the server where the agent is executing?
I usually create a step in the job to send emails as it has more control. One step for errors and another for success if needed.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-07 : 04:39:44
I want to run it in Server B only but the agent will be in Server A and i need to send an email notification on failure .


Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 04:49:34
quote:
Originally posted by gangadhara.ms

I want to run it in Server B only but the agent will be in Server A and i need to send an email notification on failure .


Thanks,
Gangadhara MS
SQL Developer and DBA


Thats fine
Provided SQL agent service account can access shared path \\serverB\...
it can access the bat/vbs and execute it using xp_cmdshell


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:53:51
Then you need to create a thread on server B and execute it in that. By default running it in the agent will get the file from server B but execute on server A.
It's not a trivial task to execute on a remote server and to reliably retrieve the result. Easiest is probably to install sql server express on server B then you can do a remote sp call to execute the file and get a return.

A long time ago I ran something which had a local vbs stub to call a remote stub which executed a bat file on the remote server but that would probably be dependent on the version of windows (and last time I looked I couldn't find the code).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-07 : 05:03:02
I tried to run this command

EXEC master..xp_CMDShell '\\SERVERA\pros\UpdateSalesProducts.vbs'

I am getting the below error:

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
NULL
\\SERVERA\pros\UpdateSalesProducts.vbs(119, 2) Microsoft VBScript runtime error: Path not found
NULL
NULL



Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 05:09:58
That would run the task on Server A.
Isn't the file on ServerB? You have serverA as the location


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-10 : 02:08:34
Sorry it was the mistake i have used server B path.

Still i am getting the same error.

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 02:17:38
quote:
Originally posted by gangadhara.ms

Sorry it was the mistake i have used server B path.

Still i am getting the same error.

Thanks,
Gangadhara MS
SQL Developer and DBA


can you try browsing to path manually from run?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-10 : 05:02:25
yes i checked with that and its running the .vbs file.

Seems like we are not able to run this query through T-SQL ??

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 05:18:59
then that means proxy account is not able to execute it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-10-11 : 01:32:58
how can we enable this proxy account ??

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:49:42
check from which account sql agent job is executing by look in job properties. Check if this account has access to folder if not give it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:13:52
You still have nothing to run the task on serverB - that will load it from serverB but run it on ServerA.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
    Next Page

- Advertisement -