Exact AsImport.exe - Where to best run ? Windows Task Scheduler 1.0 or SQL Jobs

These are the two most used tools to run AsImport.exe. Task Scheduler is very straight forward to use and it is designed to meet the basic needs of scheduling  programs or applications to run at predefined dates/times. If you have any level of sophistication in your automation requirements such as dependencies, multi-scheduling, alerts and notifications, history etc. you need to evaluate third party job batch scheduling products then.

That is why I prefer SQL Agent Jobs because, aside from ease of management and monitoring of the events, you can use it for simple to complex scheduling needs. Although there is a drawback in SQL jobs whenever you run the AsImport.exe without the –Oauto parameter, but my automation requirements is addressed then. Here is why.

 * In SQL jobs, you can configure the event to send you an email notification if the jobs have either failed or succeeded. To achieve the emailing notification, the following are required:

• Enable the Database Mail Stored Procedures
• via SQL Script

                     USE Master
                     go
                     sp_configure 'show advanced options', 1
                     reconfigure
                     go
                     sp_configure 'Database Mail XPs', 1
                     reconfigure 
                     go

• Configure the SQL mail or database mail
•  How to: http://www.mssqltips.com/tip.asp?tip=1438
• Add SQL Server Agent – operator and Enable the Alert System of  SQL  Server Agent Properties
• How to : http://www.mssqltips.com/tip.asp?tip=1523
• Create SQL Job (the script below is to transfer humres data from Synergy to Globe)
• USE [msdb]
  GO
  /****** Object:  Job [AsImport (Humres) Synergy to Globe - 555]    Script Date: 06/15/2011 11:41:19 ******/
  BEGIN TRANSACTION
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/15/2011 11:41:20 ******/
  IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  
  END
  
  
  DECLARE @jobId BINARY(16)
  EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AsImport Synergy to Globe - 111',
                          @enabled=1,
                          @notify_level_eventlog=0,
                          @notify_level_email=2,
                          @notify_level_netsend=0,
                          @notify_level_page=0,
                          @delete_level=0,
                          @description=N'No description available.',
                          @category_name=N'[Uncategorized (Local)]',
                          @owner_login_name=N'DomainName\Anthony',
                          @notify_email_operator_name=N'Anthony', @job_id = @jobId OUTPUT
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  /****** Object:  Step [Humres]    Script Date: 06/15/2011 11:41:20 ******/
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Humres',
                          @step_id=1,
                          @cmdexec_success_code=0,
                          @on_success_action=1,
                          @on_success_step_id=0,
                          @on_fail_action=2,
                          @on_fail_step_id=0,
                          @retry_attempts=0,
                          @retry_interval=0,
                          @os_run_priority=0, @subsystem=N'CmdExec',
                          @command=N'C:\Program Files\Exact Software\Globe\bin\AsImport.exe -rGlobeSVR -D111 -u -~ I -URLhttp://SynergySVR/synergy -Tresources -OPT22 -Oauto',
                          @flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  COMMIT TRANSACTION
  GOTO EndSave
  QuitWithRollback:
      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  EndSave:

(Unfortunately there is no native alerting system for Tasks Scheduler 1.0, nor any easy way to see what the task is doing. But I have heard so much change in Task Scheduler 2.0 (Windows 2008 server build).  But again my consideration is automation requirements without the use of third party tool.) 

* In SQL jobs, you can define dependencies via Job Steps like after running AsImport.exe, I want to backup my Synergy or Globe DB then.

* In SQL jobs, you can be notified whenever the jobs failed, succeeded and completed.

* The drawback :

• When running the AsImport.exe without the –Oauto parameter, it will bring the import/export screen of Globe application for users intervention. In SQL Jobs, the .exe that you need to start cannot be an interactive program/application (ex: calc.exe) as the user will never see the program popup because it is running inside the SQL Server’s own session and not in your session. The AsImport.exe actually starts, but you just can't see it. You can verify that it did start by searching the AsImport.exe in Windows Task Manager under Processes Tab on the database server. So, make sure you put the -Oauto parameter  when using SQL Agent Jobs for AsImport.exe

• When your Internet Explorer (IE) is configured to prompt for username and password under Internet Options Security Settings. Again, as a rule of thumb, do not launch program/application that needs user intervention. This can be accomplished by playing around with the Internet Explorer setting of the machine on which the job is going to run on. First, log on to the NT account that is used to run the job. Then,
• Open Internet Explorer and click Tools at the menu bar.
• Click Internet options and then the Security tab.
• Click Internet or Local intranet to select the zone to view or change security settings. Next, click Custom level
• Scroll down to the bottom under User Authentication\Logon,  configure the browser to log on automatically under the User Authentication section or automatic log on only in intranet zone.

So be sure to test your AsImport.exe at the command prompt, get rid of any pop-ups before transferring the same into SQL Jobs to keep your synchronization running.


Related blog: http://anthonyexactglobe.blogspot.com/2011/06/asimportexe.html