SQL Server Agent Job Set Up

Question

I'm a newbie to SQL and .Net and this question might be stupid too to ask but I still need help.

I've a Database located on Server1. Now I got another Server, lets call it Server2 to work on my Development.

Server1 on which my database is located seems to be a SQL Server Express installation so I don't see a SQL Server Agent in there. Server2 has a SQL Server Agent. So can I use Server2's SQL Server Agent and schedule queries to run automatically on Server1? Is this possible? If it is then can someone explain me how to do it.

Answer 1

If you create a linked server on Server2 that points to Server1, yes you can create jobs on Server2 that execute code on Server1 using server1.database.dbo.object syntax.

You can learn about creating linked servers here:

http://technet.microsoft.com/en-us/library/ff772782.aspx

(You'll have to change the version to your version, since you didn't tell us what version you're using. You'll also want to make sure the linked server is collation compatible and supports data access/RPC.)

Of course you will have to deal with security and also how to deal with connectivity failures, and general dependencies between the two servers.

You can add error handling with sp_testlinkedserver.

Answer 2

I've successfully run Jobs from SQL Express, there's no built in support for the mangement studio, but you can manually do all the work. it is a pretty long-winded process though. Follow the excellent and very detailed guide in this blog post.

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

Alternatively there is a project to create an SQL Agent for SQL Express: http://standalonesqlagent.codeplex.com/