sanderstad (6/5/2009)
When I get execute the jobs I get the following error by starting the powershell script:
Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable)Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable). Process Exit Code 0. The step succeeded.
Try running update_server_dim_sp this wil update the server_dim.max_backupset_id column. Also keep in mind the primary key is defined as server_name and backup_set_id. The server name value is obtained from @@servername, so its possible to have duplicate names of your SQL Servers in your environment. Check @@servername on all servers you are polling and verify there are no duplicates.