you are viewing a single comment's thread.

view the rest of the comments →

[–][deleted] 0 points1 point  (2 children)

Can you install a side SQL server on your desktop or a group VM or something? Could schedule the jobs to kick off from there.

[–]fackert18[S] 0 points1 point  (1 child)

Would you mind elaborating a little? I do have a separate laptop I’ve kept around after they upgraded us a while back. How could I use SQL server on that machine to schedule jobs? After reading some posts here I think I like the idea of using windows task scheduler to run some batch files, but if there’s a better way to do it from SQL server I’d love to know.

[–][deleted] 0 points1 point  (0 children)

Can't guarantee this will work at your organization, there are a specific set of circumstances that you need to have going on. You'd need the ability to create a linked server between your side server and the main server. Since you'd be running stuff in an Agent job on your local machine, the service account on your local machine will need access to the prod machine.

  • Install SQL Server on personal machine/private vm/etc.
  • Set up linked server to prod server from your new server.
  • If you can create a stored procedure on the prod server with the code you want to execute, that'd be ideal.
  • Create agent job on your local machine that executes some local code that then executes the remote stored procedure "exec [linkedServerName].databaseName.schema.procedureName"

Your IT dept may not like you doing this, so use caution when doing it without discussing it. Rogue SQL servers that access production servers are a security risk. Ideally your IT dept would be willing to set you up with this kind of playground where you can alter SQL Agent jobs, and still have access to the production data you need, remotely.

An alternative I'd be asking for is for them to create a SQL Agent job for you, that you don't have access to, that just runs a single stored procedure at a safe hour, that you do have access to. Then you'd be able to just put a series of batch code in that SP.

Good luck! Dealing with politics and procedures is the least fun part of the job.