Skip to main content

Notifications

Announcements

Announcing Category Subscriptions!
News and Announcements icon
Dynamics 365 Community / Blogs / BC Life-An ERP On The Move! / Jo's Did You Know "...

Jo's Did You Know "You can set your AR Aging Routine to Run Automatically in All GP Databases"

JodeRuiter Profile Picture JodeRuiter 978 User Group Leader

Jo's Did You Know "You can set your AR Aging Routine to Run Automatically in All GP 

Every now and then I have a request from a client that they want to set up some automatic functionality in databases.  This can at times require coding or Macros, etc.  But there is one routine that is easier to do than that!

Some of these "Routines" can be set to run as Maintenance Plans at the end of the day so that reports, etc are always correct.

One of my favorites to set up is a routine to run the AR Aging Process automatically.  Here are some instructions to set these up.  I worked with Steve Gray to create this routine and the answer is also on his website dyndeveloper.com HERE

Use a Maintenance Plan to Run The Auto-Aging:

  • Log into SQL Server Management Studio
  • In Object Explorer Navigate to Management>Maintenance Plans
  • Right Click and select “New Maintenance Plan”
  • Name the Maintenance Plan “Auto AR Aging in All Databases”
  • Move to the Left side of the screen and choose “ToolBox”
  • Double Click “Execute T-SQL Statement Task”
  • The task will appear in the big grey box on the right
  • Right click the task and select “Edit”
  • Paste into the “Execute T-SQL Statement Task” window this query:

--declare a variable to hold a concatonated SQL script

DECLARE @command varchar(1000)

--at the end, we call sp_MSforeachdb. That command will run a script in each database on the server

--so, for our command, we first issue a 'use'

--USE ?

--the '?' is a place holder for the current db

--the 'if not exists' statement looks into DYNAMICS..SY01500 to see if this is a company db. If it is not, we exit and loop

SELECT @command = 'USE ?; if not exists (select 1 from dynamics..sy01500 where interid = db_name()) begin print ''exiting '' + db_name() return end; print ''working in '' + DB_NAME();'

--this is the statement requested

SET @command = @command + 'DECLARE @O_iErrorState int, @I_dAgingDate datetime; '

SET @command = @command + 'select @I_dAgingDate = convert(varchar(10), GetDate(), 102);'

SET @command = @command + 'EXEC dbo.rmAgeCustomer 0, '''', ''þþþþþþþþþþþþþþþ'', @I_dAgingDate, 127, 0, 0, '''', @O_iErrorState OUT;'

SET @command = @command + 'SELECT @O_iErrorState'

PRINT @command

--this is where the magic happens. execute @command against all the databases

EXEC sp_MSforeachdb @command
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • Click on Schedule at the top of the window
  • Set the schedule to run daily, overnight, after any backups or other items are running. .

 

Comments

*This post is locked for comments

  • MarkW101 Profile Picture MarkW101
    Posted at
    Nice script. I usually go directly to the SQL jobs and create it manually, but the maintenance plan is a good way to do it. Thanks for sharing this - especially for environments where there are multiple GP companies to age.
  • 26P2ER Profile Picture 26P2ER 1,773
    Posted at

    Spiffy!