scheduled stored procedure to run asp page

[vivasuzi]

I want a simple stored procedure to run an asp page every friday. I've been reading 'beginners' articles on how to make stored procedures but I'm completely lost. Can anyone help me to create a stored procuedure on a SQL Server that does the following:

---------
runs every friday at 10PM (server time)
executes an asp page
this asp page does some db work and emails users in the db (i can do this part)
---------

I cannot access the server at all. All I can do is send scripts to our SQL Server admin who runs the scripts for me. Can a stored procedure be put in a script that I can send to the admin? He would then run the script to apply the changes to the server and then the stored procedure should run every friday. OR Will I have to tell the admin to set up a script task in Enterprise Manager?

---------

My local copy of SQL Server is down so I cannot even try to create anything in enterprise manager. Any help is great! I hope I'm not asking for too much *Suzanne*

http://www14.brinkster.com/vivasuzi

 

[cheyney]

You can create a stored procedure with the CREATE PROCEDURE... construct. Look in books online.

You can create and start a scheduled task with

msdb.dbo.sp_add_job
msdb.dbo.sp_add_jobstep
msdb.dbo.sp_add_jobschedule
msdb.dbo.sp_add_jobserver
msdb.dbo.sp_start_job

the @command parameter in your add_jobstep call would be something like 'EXEC <sprocname>' where <sprocname> is the name of the stored procedure you created with CREATE PROCEDURE.

Now, as for getting the stored procedure to execute an ASP page, that could be tricky. Does the ASP page read query string variables or form variables?

I would probably just write a quick VB program with a webbrowser control in it. The webbrowser control could navigate to the page you want when the program is launched and then exit when the page is loaded. You can execute a program from a stored procedure with the master..xp_cmdShell
stored procedure.

I know this is all very vague, but this is a somewhat involved process. If you need help doing any of these things, just ask, or consult books online

cheyney

 

[SQLSister]

Why would you even want to execute an ASP page, all data manipulation can be handled in the stored procedure. If you are doing something on a scheduled basis there is no need to hit a ASP Page. The only time you might need to hit an ASP page would be if the stored procedure was executed only when someone told it to be executed from the ASP page. Stored procedures are more efficient at data manupulation than ASP code and in the case of the scheduled procedure you can do everything on the SQL server and avoid tying up the network by sending the process to the web server or client for the ASP page to execute.

You need to work out the access issue with the dba, you cannot be expected to program functionality to a server you have no access to. Have him write the stored procedure or insist on access especially query analyzer. How else are you going to debug your program? If you do not have a development database, insist that one be set up and made available to you. You should not have access to production, but you certainly should have access to development. Otherwise there is no way to effectively do your job. I would suggest you discuss this with the dba and if he won't cooperate take it up the chain to your boss and his boss. And if he does not have a development database set up he is not doing his job as dba and you should point that out.

 

[vivasuzi]

The asp page takes users from the database, then creates an email to send to each user. Each user will get a different email depending on other information stored in the database. Plus, we want to format the email w/ html. Plus, we want it as an asp page so we can change the emails whenever we see fit.

The server is run by an outside company. I am not worried about complaining to people about my access levels, that's not the issue at all. My main job is to write asp pages, but I was just trying to do automatic emails for this project. I didn't know it would be this complicated.

I think I'm just going to change this one function so that the admin has to log in and run the page every week. That would just be the easiest way to do it, and I have a deadline b/c this is also a school project. *Suzanne*

http://www14.brinkster.com/vivasuzi