0

I have a field 'Total field' in my leavemaster table. Now my requirment is to auto update this field to add 15 days when new year starts i.e Jan 1st. I thought to use it by trigger. but it is not help when i try to implement it. Can anyone help me to do this? is there any other idea to done this Thanks in advance...

I have created the bat file as following :

SQLCMD -E -Timesheet -ic:\BAT\UpdateTotalLeave.sql

and my sql file as following:

UPDATE empTable SET TotalLeave = TotalLeave + 15

and i set the windows task scheduler as shown in image(i set the today date and time for testing) but not updated in table.

task scheduler

is there any error in bat file or my task scheduler ? thanks

KaviSuja
  • 450
  • 1
  • 9
  • 37
  • What database are you using? Is the idea that you want the DB to recognize when Jan 1 occurs and to then, automatically update the Total field on every row? – Ben Dec 21 '15 at 05:06
  • Do you _always_ want to add 15 days, or just for a certain range of dates? By the way, I think trigger may be the way to go here. – Tim Biegeleisen Dec 21 '15 at 05:09
  • yes for every year starts i need to add 15 days. i.e i am working in leave management. so, here for employees the available leave should be added 15 days on new year – KaviSuja Dec 21 '15 at 05:12
  • In an ERP this would normally be done by some kind of period rollover function where you decide it's the new year, press a button and all things like this are processed. A trigger won't help here. Nothing gets 'triggered' in the new year. – Nick.Mc Dec 21 '15 at 05:20

2 Answers2

3

I think you can write a SQL server Agent Job, which runs on Jan 1st of every year and to update the required tables based on your criteria.

SQL Trigger may not help in this case as triggers works on insert/update/delete operations.

Venkatesh
  • 334
  • 2
  • 11
  • thanks for your reply. I thought to work with SQL server Agent Job. But in our server there is express edition only available (there could not able to run SQL server Agent Job).. so how can done this – KaviSuja Dec 21 '15 at 05:27
  • hm.. then you have to write a batch file and add that batch file to windows scheduled tasks. You can use **sqlcmd** utility to do the job. I have not tried with **sqlcmd** utility. You can refer [Link](https://msdn.microsoft.com/en-us/library/ms162773.aspx) – Venkatesh Dec 21 '15 at 05:34
  • there are multiple cmd availble. which one is( osql or sqlcmd ) best – KaviSuja Dec 21 '15 at 06:19
  • Looks like sqlcmd has advanced feature than osql. You can go for sqlcmd. – Venkatesh Dec 21 '15 at 06:31
  • 1)Create a sql file with UPDATE stmts to the respective table. 2) Create a batch file with your environment details along with the step 1 .sql file 3) Call the batch file using Windows Task Scheduler. – knkarthick24 Dec 21 '15 at 06:55
  • I have created the bat file. i want to run it on jan 1st only. Is there any settings available in Windows Task Scheduler – KaviSuja Dec 21 '15 at 07:35
  • You can use one time option in windows scheduler, then select the date as the day of the next year with time will give you the required results. – Venkatesh Dec 21 '15 at 07:44
  • i tried and tested it with today date. but table not updated.. I have attached the image of task scheduler in my question. please refer and is there any error i did there? – KaviSuja Dec 21 '15 at 08:02
  • Did you checked whether the batch file is working properly? i.e. check manually running the batch file from command prompt and check whether the table is updating properly. As per the screenshot looks like the task is executed. – Venkatesh Dec 21 '15 at 08:43
  • i checked it with command prompt but i got error like time out expired. network related error occured. i gave the details as 'sqlcmd -E -S SysName\DBname -U sa -P mypwd -i c:\BAT\UpdateTotalLeave.sql' – KaviSuja Dec 21 '15 at 09:28
  • i changed my bat file as 'sqlcmd -E -S system3 -i c:\BAT\UpdateTotalLeave.sql' and test this command via command prompt. its working.. but when scheduled to task scheduler not updated – KaviSuja Dec 21 '15 at 10:02
0

I create batch file and schedule it in task scheduler to update the db value. its now working. i referred the following :

http://www.codeproject.com/Questions/554372/WantplustoplusbatchplusfileplustoplusexecuteplusSQ

http://windows.microsoft.com/en-in/windows/schedule-task#1TC=windows-7

Executing set of SQL queries using batch file?

Community
  • 1
  • 1
KaviSuja
  • 450
  • 1
  • 9
  • 37