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 MSSQL 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
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 MSSQL Developer and DBA |
|
|
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.- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MSSQL Developer and DBA |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MSSQL Developer and DBA |
|
|
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 MSSQL Developer and DBA
Thats fineProvided SQL agent service account can access shared path \\serverB\...it can access the bat/vbs and execute it using xp_cmdshell------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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.6Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.NULL\\SERVERA\pros\UpdateSalesProducts.vbs(119, 2) Microsoft VBScript runtime error: Path not foundNULLNULLThanks,Gangadhara MSSQL Developer and DBA |
|
|
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. |
|
|
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 MSSQL Developer and DBA |
|
|
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 MSSQL Developer and DBA
can you try browsing to path manually from run?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 MSSQL Developer and DBA |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2011-10-11 : 01:32:58
|
how can we enable this proxy account ??Thanks,Gangadhara MSSQL Developer and DBA |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
Next Page
|