I need SQL Agent so a user can schedule jobs on the main SQL 2005 server

[KAMAZON]

Hello, I installed SQL Server Management Express edition for a user to access the main sql 2005 server but they want the agent as well so that they can schedule jobs. However the express edition doesn't have that functionality but the main server does. I cannot have them connect to the main server to run their jobs and the MMC doesn't work remotely anyways. Do you guys have any suggs? Thanks.

 

[KLin]

Schedule the jobs for them if you don't want to give them access. There really isn't another way to do this.

 

[KAMAZON]

Hi KLin thanks for the info. Unfortunately I cannot RDP into the server and run SQL manager as it gives me an error when you do it via RDP. I'm going to install the 'client tools only' option on the workstation from the sql2005 free trial, hopefully that solves our problem.

 

[imported_Dhaval00]

Have you tried looking into the SMO API? It exposes pretty much all the functionalities that you can achieve via SQL Server Management Studio. We deploy Windows Forms applications for some of our remote sites that need such functionality. The users have schemas in SQL Server and roles/users based on these that the users use for authenticating them as "admins" remotely via the form. Then, via the GUI, they can schedule SQL Server Agent jobs (for example). Refer to the snippet below that creates a simple schedule (see SQL Server Books Online for API reference):

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Agent;

class Program
{
static void Main()
{

// Pass SQL Server IP in the constructor
Server sqlServer = new Server("local")
JobServer sqlAgent = sqlServer.JobServer;

JobSchedule js = new JobSchedule(sqlAgent, "Testing");
js.FrequencyTypes = FrequencyTypes.Daily;
js.FrequencyInterval = 1;
js.ActivityTimeOfDay = TimeSpan.FromHours(4);
js.IsEnabled = true;
js.Create();

}
}

The console program simply creates a simple job (check via SSMS), but you get the idea. I didn't include the code to authenticate the currently logged in user, and this code will fail if the current user doesn't have proper permissions. Again, look up the SMO API... it can accommodate all such functionality.