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
--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'
--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'
--this is where the magic happens. execute @command against all the databases
EXEC sp_MSforeachdb @command