mibuso.com

Microsoft Business Solutions online community
It is currently Fri Oct 31, 2014 5:18 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Using ADO to kick-off SQL jobs from NAV
PostPosted: Fri Jul 17, 2009 9:55 pm 
Offline

Joined: Tue Jun 07, 2005 4:24 pm
Posts: 2860
Location: MA
Country: United States (us)
The sample code below is an example of how ADO can be used to kick-off a SQL Agent job from NAV. This can be helpful when you need to run things but they don't fit into a fixed schedule. This particular code is called from Adjust Cost, but I'm sure there are other uses. It allows us to run nightly SQL maintenance following the completion of Adjust Cost.


Code: Select all
Name                        DataType           Subtype           Length
ADOConnection           Automation           'Microsoft ActiveX Data Objects 2.8 Library'.Connection   
GLSetup                        Record           General Ledger Setup   
ADOCommand           Automation           'Microsoft ActiveX Data Objects 2.8 Library'.Command   
ADOParameter           Automation            'Microsoft ActiveX Data Objects 2.8 Library'.Parameter   
lvarActiveConnection      Variant      


OnRun()
IF ISCLEAR(ADOConnection) THEN;
  CREATE(ADOConnection);

ADOConnection.ConnectionString := GetConnectionString;
ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
  CREATE(ADOCommand);

lvarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := lvarActiveConnection;

ADOCommand.CommandText := 'sp_start_job';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

ADOParameter := ADOCommand.CreateParameter('@job_name', 200, 1, 50, GLSetup."Index Job Name");
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;

ADOConnection.Close;
---------------------

GetConnectionString() : Text[1024]
GLSetup.GET;
GLSetup.TESTFIELD("Server Name");
GLSetup.TESTFIELD("Index Job Name");

ConnectString := 'Driver={SQL Native Client};'
      + 'Server=' + GLSetup."Server Name" + ';'
      + 'Database=msdb;Trusted_Connection=yes;';
EXIT(ConnectString);


_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO to kick-off SQL jobs from NAV
PostPosted: Sun Nov 29, 2009 4:46 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9067
Location: 3rd rock from sun
Country: United States (us)
I have blogged about the other way around using SQL Agent jobs schedule to run NAV jobs.

Here is the link

http://mibuso.com/blogs/ara3n/2009/11/1 ... b-service/

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Using ADO to kick-off SQL jobs from NAV
PostPosted: Tue Mar 23, 2010 12:00 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
Thanks to both of you.

I am in a tuning project right now, and trying to decide between one of these approaches, your posts will make it easier for me to review both options.

Thanks.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: