Running DataSelf's MS SQL Jobs from Workstations

From DataSelf Knowledge Base
Jump to navigation Jump to search


  • How to access SQL Server Agent from any Windows platform using SQL Server Management Studio (SSMS).
  • How to run a SQL Server Agent job on demand.

Run a SQL Agent Job with a Batch File using OSQL.exe

http://www.rklesolutions.com/blog/run-sql-agent-job-windows-bat-file/

Key Points:

  • Grant user only SQLAgentOperatorRole permission on SQL Server. User does not have or need Admin level access to SQL Server.
  • Activate SQL Agent job from a batch file by calling OSQL.exe.
  • OSQL.exe is a Microsoft supplied file.


Running a DataSelf SQL Job on Demand

See also: Configuring Windows to Remotely Access SQL Server Agent Via SSMS


Procedure:

1. Open MS SQL Server Management Studio (All Programs > Microsoft SQL Server > SQL Server Management Studio).

2. Select the following:

  • Server type = Database Engine;
  • Server name = "name of the SQL Server instance provided by DataSelf or your IT team";
  • Authentication = leave "Windows Authentication" unless DataSelf or your IT provided you with credentials for "SQL Authentication".

Sqljob1.png


3. Expand the SQL Server Agent tree and double-click "Job Activity Monitor".

Sqljob2.png


4. DataSelf jobs start with the letters DS. All DSxyz jobs' Status should be "Idle". If not, wait until they are all Idle. Press "Refresh" often to refresh this window.

Sqljob3.png


5. Right-click the DS job to run, and click "Start Job at step..."

  • If a window pops up asking to choose one of the steps, click Step 1 and click Ok.

Sqljob4.png


6. Click Refresh and check if status has been changed to EXECUTING. If so, you just need to wait until the job finishes processing.

Sqljob5.png


7. Click Refresh again and check if status has been changed from EXECUTING to IDLE. If so, the job has finished refreshing.


Checking if a DataSelf SQL Job Ran Successfully

For DSA Extract Refreshes

1. Open a web broser and log to DSA Server v9 or newer. 2. Go to Content > Data Sources 3. On the "Live/Last extract" column, the data source(s) being refreshed should show a very recent date/time.

Sqljob6.png


  • Sometimes it takes several minutes after finishing the "Running a DataSelf SQL Job" procedure for extracts to complete their data refresh. You might have to refresh your browser's page until you see the column above updated.


For DSA AD Jobs

1. Wait a couple of minutes and check if users have received the expected refreshed reports by email.

Configuring Windows to Remotely Access SQL Server Agent Via SSMS

Pre-requisites:

  • IT: Please install MS SQL Management Studio (SSMS) on the user's workstation. Be sure it's of a compatible version with DataSelf's SQL instance.
  • IT: Please grant user the rights to run SQL Jobs from the DataSelf MS SQL instance: SQLAgentUserRole from msdb database. See the following for further details:
  • User: You've been informed the name of the SQL Server instance where the DataSelf SQL Job is.
  • User: You've been informed what credentials to use to connect to SQL Server (it's usually your own domain credentials).
  • User: You've been informed the name of the DataSelf SQL Job to run manually.