Last post Oct 07, 2006 03:12 AM by satish_nagdev
Oct 01, 2006 03:11 PM|tstanley|LINK
Hi. I run a small website on a shared plan with my hoster (the same one that hosts ASP.NET!) and I need to execute a SQL stored procedure on a daily interval. Normally, I would write a T-SQL task under SQL Server Agent using my SQL Server Management Studio.
However, I have no access to this feature on the hosted Db.
I am told that if I write a VBS script for my webserver, they will set it up to execute regularly. So, I need to create such a script that in turn triggers a stored procedure on the database, but I have no clue how to do this.
Any help will be MUCH appreciated.
Oct 02, 2006 02:11 AM|satish_nagdev|LINK
you first need to create .vbs i.e. vbscript file that will call you stored procedure or query whatever you want to call
then you need to create a windows scheduled task or ask your hoster to do it, in that you need to call cscript command of windows which is used to execute script files.
here's help of this command.
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
Usage: CScript scriptname.extension [option...] [arguments...]
//B Batch mode: Suppresses script errors and prompts from displaying
//D Enable Active Debugging
//E:engine Use engine for executing script
//H:CScript Changes the default script host to CScript.exe
//H:WScript Changes the default script host to WScript.exe (default)
//I Interactive mode (default, opposite of //B)
//Job:xxxx Execute a WSF job
//Logo Display logo (default)
//Nologo Prevent logo display: No banner will be shown at execution time
//S Save current command line options for this user
//T:nn Time out in seconds: Maximum time a script is permitted to run
//X Execute script in debugger
//U Use Unicode for redirected I/O from the console
hope it helps.
Oct 02, 2006 02:05 PM|tstanley|LINK
Thanks very much Satish.
Since I have only programmed within the Visual Studio environment, I do not have good experience with creating a standalone script. Could you point out where I might find an example script for executing a SQL stored procedure?
Oct 03, 2006 04:08 AM|satish_nagdev|LINK
http://www.15seconds.com/issue/010718.htm - please see this link
you can create script file in VS or in notepad also
create a file with .vbs extension
in that create 1 method like following
Dim res As Integer
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "update_empdetails"
cmd.Parameters.Append cmd.CreateParameter("empid", adVarChar, adParamInput, 6, txt_empid.Text)
cmd.Parameters.Append cmd.CreateParameter("firstname", adVarChar, adParamInput, 30, txt_firstname.Text)
cmd.Parameters.Append cmd.CreateParameter("title", adVarChar, adParamInput, 30, txt_title.Text)
cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 100, txt_address.Text)
cmd.Parameters.Append cmd.CreateParameter("result", adInteger, adParamOutput)
res = cmd("result")
, in main method you'll write code. then schedule a task to execute this script file at time you want to.
hope it helps & sorry for late reply
Oct 06, 2006 09:01 PM|tstanley|LINK
THANK YOU, Satish!
This is exactly the help I needed. I'm off and running!
All the best,
Oct 07, 2006 03:12 AM|satish_nagdev|LINK
cheers mate :)
thanks & regards,