0

I have procedure which copies data from a client table a history table.

How I can do it automatically without involving user pressing button each month in some specific day?

I am using SQL Server 2008 Express Edition, so I cannot schedule a job using SQL Server Agent.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Andrey
  • 1,629
  • 13
  • 37
  • 65

2 Answers2

1

In SQL Express this is not possible - you will need to write a program which does this, then you can use the Windows task scheduler to execute the program. The higher versions of SQL server include SQL Server Agent, which you can get to run things for you.

Richard
  • 29,854
  • 11
  • 77
  • 120
1

Since you are using SQL Express you can't use SQL Server Agent. However there are many alternatives, all of which you can schedule using schtasks or Windows Task Scheduler depending on your operating system:

All of these languages/tools (and many others) have the capacity to connect to SQL Server and execute a stored procedure. You can also try these Agent replacements:

Here is an example using VBScript:

  1. Open Notepad. Type the following code, replacing $tokens$ with your real values:

    s = "$your server/instance name$"
db = "$your database name$"
u = "$your SQL auth user ID$"
p = "$your SQL auth password$"
constr = "provider=SQLOLEDB;Data Source=" & s & ";Initial Catalog=" & s & _
         ";User ID=" & u & ";Password=" & p & ";Network=DBMSSOCN;"
Set conn = CreateObject("ADODB.Connection")
conn.Open constr
conn.Execute "EXEC dbo.$your procedure name$;"
conn.close: set conn = nothing

  2. Save this file as c:\somewhere\ScheduledProcedure.vbs

  3. Open a command prompt and type the following (this will schedule this script to run on the 15th of every month at midnight), all on one line:

    schtasks /create /tn "some name" 
  /tr "wscript ""c:\somewhere\ScheduledProcedure.vbs""" 
  /sc monthly /d 15 /st 00:00

To see this task in your list of scheduled tasks (there are a lot, but it should be in the very first group):

schtasks /query

If you later want to delete it:

schtasks /delete /tn "some name" /F
Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanx! will look for something I know Visual studio has something like timer but not sure if it will work with asp.net – Andrey Mar 12 '13 at 20:42
  • @Andrey what does any of this have to do with asp.net? If all you are trying to do is execute a stored procedure on a schedule, the fact that asp.net also uses that database is pretty much irrelevant. – Aaron Bertrand Mar 12 '13 at 20:45
  • I have DB and that Db connect to web site written on ASP.Net so i think i can call this procedure from .Net site by using timer. – Andrey Mar 12 '13 at 20:56
  • I'll confess I have no idea what "timer" is, but if it relies on web site activity, I'm not sure that will work. What if nobody visits your web site at the right time? – Aaron Bertrand Mar 12 '13 at 20:58
  • I dont know :) problem is that i may not be able install some other software (SQLScheduler Express Agent Standalone SQL Agent (beta))on server which will host web page and db – Andrey Mar 12 '13 at 21:02
  • Well (a) you don't have to install software. Windows already has AT and a task scheduler, and surely you can create something as simple as a VBScript. Also (b) you can schedule this from any machine that has reliable connectivity to the database server. – Aaron Bertrand Mar 12 '13 at 21:06
  • I don't know Visual Basic I know C# on entry level :( I think will have to make users click button every month, and wright data into table. – Andrey Mar 12 '13 at 21:14
  • This is not difficult to do. I can assure you that you can do this without needing your users to click on anything. – Aaron Bertrand Mar 12 '13 at 21:17
  • I've added an example that shows how to execute a stored procedure using VBScript (you just need to plug in your specific variables), and then schedule it using `schtasks.exe`. Hope it is helpful. – Aaron Bertrand Mar 12 '13 at 21:34