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  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. .