A customer reported the following issue, in his ConfMgr2007 R2 environment (mixed mode) with one affected Win2008 SP2 ConfMgr2007 client, among other affected clients.
There were several servers (clients) which should be patched by ConfMgr, but the specific server that we examined, could not download the security updates (the client cache was almost empty).
During the troubleshooting phase, the customer had the ConfMgr client reinstalled, but the issue remained.
By the time the customer switched from WSUS to SCCM for the patch operations (several months ago), for some clients the patching operations did not work.

While researching on the data , these were the most interesting lines that we found:

Cannot insert more than 1 WSUS update source with different unique ids  WUAHandler

Failed to Add Update Source for WUAgent of type (2) and id ({##-##-##-####}). Error = 0x80040694.        WUAHandler
Its a WSUS Update Source type ({##-##-##-####}), adding it.  WUAHandler 

In these kind of issues, most of the times the reason for this behavior is: a previous installation or reinstallation of the site server, which left stale record or for some reason, the hierarchy between the site server has changed (uninstallation - new installation, attaching a site server to a different site), etc..

According to the above log lines, there was a duplicate WSUS registration in the site’s database. The CI_UpdateSources table in the site’s database, probably contains aged data – a stale record.In order to disable the inactive entry, we need to execute the following steps against the site database:

select * from CI_UpdateSources

The above query should ideally return 1 result. If you get two results, then you have an additional entry of the update source from a previous installation.
Both the entries may have the IsTombstoned value = 0 which is not correct. 
You need to find the active id by: either comparing the DateCreated and Modified values in the table or the UpdateSource_UniqueID, which should be the same as the one that the client is trying to run the scan against, that we see in the log:

Failed to Add Update Source for WUAgent of type (#) and id ({##-##-##-####}). Error =0x80040694.  WUAHandler        

 To disable the inactive entry, we need to run the following command :

 update CI_UpdateSources set IsTombstoned = 1 where UpdateSource_ID = '#'