Blog

Our latest posts and rants

Dynamics GP logo

How to Filter on “All Sites” in a Microsoft Dynamics GP SmartList

This is probably documented elsewhere but we weren’t aware of it until a few days ago, so I thought I’d post it as a quick tip.

Have you ever looked at a SmartList of inventory quantities…

AllSites1

…then wanted to isolate only the rows showing total quantities (the rows labeled “All Sites”)?

Setting the filter to match All Sites

But trying to match “All Sites” shows no rows.

Shows no rows

The trick is to match on a blank field, not the phrase “All Sites.”

Matching on a blank field

That shows the rows we want.

Shows only the "all sites" rows

In the underlying database, the “All Sites” rows are coded using an empty field.

Aha!

Hope this helps someone!

Google Building

New Google Analytics Course

Google has published a new course dealing with their analytics platform at this URL .

Kids going back to school

The course is free and quick (maybe an hour, give or take). It contains a concise explanation of how Google Analytics collects, processes, and reports data. It will also give you an inkling of the kinds of customizations you can make. And you’ll earn an electronic certificate of completion.

Just wanted to pass along the information in case someone else is interested. Happy analyzing!

SQL Server

Checking for Missed Database Backups with Microsoft SQL Server, Part 2

When last we met, we had written a SQL Server Agent job to email us whenever a Microsoft SQL Server database had gone too long without backup.

This post contains a couple of refinements compared to the previous post. We’re able to specify different maximum backup delays for log and full backups. And this job automatically checks every database in the SQL Server instance; the job in the previous post checked only those on a list that we provided.

A happy chef

You might prefer this version with the refinements, or you might prefer the earlier simpler version, depending on your application.

Here’s the more automated later version.

declare @dbname varchar(max);
declare @buf varchar(max);
declare @latestbackup datetime;
declare @backuptype varchar(20);

set @buf = '';

declare late_backup_cursor cursor for
select 
  s.name as DBName,
  coalesce(max(b.backup_start_date), '1900-01-01') as LatestBackup,
  t.BackupType
from master.sys.databases s
join msdb.dbo.backupset b
ON s.name = b.database_name
-----------------------------------------------------------------
-- Here are the alarm threshholds, identified as HoursBeforeAlarm 
--   for each type of backup (full and log)
-----------------------------------------------------------------
join (select 'D' as BackupKey, 'FULL' as BackupType, 24 as HoursBeforeAlarm 
  union select 'L', 'LOG', 5) t
on b.type=t.BackupKey
where s.recovery_model in (1)  -- Either full recovery mode
or b.type <> 'L'  -- or not the log backup 
-- (skip log backups for simple recovery mode)
group by s.name, t.BackupType, t.HoursBeforeAlarm
having coalesce(datediff(minute,max(b.backup_start_date),current_timestamp), 
  99999999) / 60.0 > t.HoursBeforeAlarm
order by s.name, t.BackupType
;


open late_backup_cursor;

fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype;

WHILE @@FETCH_STATUS = 0
  begin
  set @buf += 'There has been no ' + @backuptype + 
    '  backup for ' + @dbname + ' since ' + 
    convert(char(19), @latestbackup, 121) + '
'; fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype; end; close late_backup_cursor; deallocate late_backup_cursor; if @buf <> '' begin set @buf = '' + @buf + ''; EXEC msdb.dbo.sp_send_dbmail @profile_name='YourDatabaseMailProfile', @recipients='recipient1@yourcompany.com;recipient2@yourcompany.com', @subject='Database backup warning', @body_format='HTML', @body=@buf ; end;

Hope this is helpful!

SQL Server

Checking for Missed Database Backups with Microsoft SQL Server

Our third party backup software occasionally errors out.

A worried person

Oh no!

We’ve developed a SQL Server Agent job to make sure we never go longer than 5 hours without a backup. If this job finds any databases have gone longer than 5 hours without a full or log backup, it sends an email with the details.

declare @dbname varchar(max);
declare @buf varchar(max);
declare @latestbackup datetime;
declare @backuptype varchar(20);

set @buf = '';

declare late_backup_cursor cursor for
select 
  s.name as DBName,
  coalesce(max(b.backup_start_date), '1900-01-01') as LatestBackup,
  t.BackupType
from master..sysdatabases s
join msdb..backupset b
ON s.name = b.database_name
join (select 'D' as BackupKey, 'FULL' as BackupType 
  union select 'L', 'LOG') t
on b.type=t.BackupKey
where s.name in ('YourDatabase1','YourDatabase2','YourDatabase3')
group by s.name, t.BackupType
having coalesce(datediff(minute,max(b.backup_start_date),current_timestamp), 
  99999999) / 60.0 > 5
order by s.name, t.BackupType
;

open late_backup_cursor;

fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype;

WHILE @@FETCH_STATUS = 0
  begin
  set @buf += 'There has been no ' + @backuptype + '  backup for ' + @dbname + 
    ' since ' + convert(char(19), @latestbackup, 121) + '
'; fetch next from late_backup_cursor into @dbname, @latestbackup, @backuptype; end; close late_backup_cursor; deallocate late_backup_cursor; if @buf <> '' begin set @buf = '' + @buf + ''; EXEC msdb.dbo.sp_send_dbmail @profile_name='YourDatabaseMailProfile', @recipients='person1@yourcompany.com;person2@yourcomnpany.com', @subject='Database backup warning', @body_format='HTML', @body=@buf ; end;

This is a pretty basic script but maybe (I hope) it will help someone else in a similar situation.

Dynamics GP logo

Using Foreign Data in a Dynamics GP SmartList

It is difficult to mix data from another server into a Dynamics GP SmartList because of how the data link is opened within Dynamics GP. In fact I was unable to find a good general purpose solution in my search.

Oh no!

So I wanted to mention a work around that I haven’t seen proposed elsewhere. We used SQL Server one-way transactional replication to set up a copy of the foreign data inside our Dynamics GP database, and used that copy in the SmartList. It works well.

A happy chef

Setting up transactional replication and including additional tables (or views) in a SmartList are topics that are well covered elsewhere. I just wanted to mention that transactional replication can fill this need. Perhaps someone will come across this note in the future and be saved a little time.