0

I have some queries that I want to execute every day automatically. Should I create it as stored procedure or there are another ways? here is my queries:

declare @tanggal_1 varchar(50)

set @tanggal_1 = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash_id where year([date]) = year(getdate()) order by numweek desc)

TRUNCATE table mytable
GO
INSERT INTO mytable (field1, field2, field3)
EXEC my_store_procedure @tanggal_1
GO
UPDATE mytable SET 
 [field4] = isnull(((nullif([cm1pl2],0)/nullif([cm1pl2_per],0))*100),0)
,[field5] = isnull(((nullif([cm1pl1],0)/nullif([cm1pl1_per],0))*100),0)
,[field6] = isnull(((nullif([cm1_cost_value],0)/nullif([cm1_cost_per],0))*100),0)
GO
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
blankon91
  • 521
  • 3
  • 15
  • 39
  • 3
    Looks like a [job](http://msdn.microsoft.com/en-us/library/ms182079.aspx) for [SQL Server Agent](http://msdn.microsoft.com/en-us/library/ms189237.aspx) – Remus Rusanu Jul 30 '12 at 09:50

3 Answers3

3

Create and schedule a job in sqlserver

I prefer to write SPs , but you can write queries directly.

Community
  • 1
  • 1
Raab
  • 34,778
  • 4
  • 50
  • 65
  • okay, thank you :) is that my query correct? because I can't use the variable in execute my_store_procedure – blankon91 Jul 30 '12 at 10:06
  • Are you talking about this part `INSERT INTO mytable (field1, field2, field3) EXEC my_store_procedure @tanggal_1` – Raab Jul 30 '12 at 10:14
  • yeah, including this part `set @tanggal_1 = (select top 1 ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash_id where year([date]) = year(getdate()) order by numweek desc)` – blankon91 Jul 30 '12 at 10:25
  • your variable assignment will be `select top 1 @tanggal_1 = ltrim(rtrim(cast(numweek as char)))+'-('+rangeweek+')' from tbl_weeklyflash_id where year([date]) = year(getdate()) order by numweek desc)` – Raab Jul 30 '12 at 17:09
  • yeah, it shows like `30-(07-08-2012 - 14-08-2012)` but I always get error message that says can't read my variable – blankon91 Jul 31 '12 at 03:03
1

You can make an agent which will do the needful.
Reference : http://msdn.microsoft.com/en-us/library/ms191439.aspx

NG.
  • 5,695
  • 2
  • 19
  • 30
1

One way is to write query and schedule job in sql server.

Ohter way is to prepare windows schedule for that query. First you need to create Store procedure of that queries. After that go to Computer Managment(right click on MyComputer and select Manage). and Go to Task Scheduler and create new task. and for that you need to create batch file to execute the store procedure which you need to run daily.

Brijesh Patel
  • 2,901
  • 15
  • 50
  • 73
  • I think, I prefer with schedule job in sql server. Thank you for the answer :) – blankon91 Jul 30 '12 at 10:26
  • 1
    In My case i used second one because sql job agent is not in my sql server version its due to security reason. but anyway both way are good example to create task schedule. :) – Brijesh Patel Jul 30 '12 at 10:53
  • oh so not all version in sql server have that feature? well, I think I need to check that feature my sql server. Thanks :) – blankon91 Jul 31 '12 at 03:04