Month: October 2014

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

Posted on

This post helped me with my issue on one of my jobs. Thanks

Mohsin's DBA Blog

Issue: When trying to delete a Scheduler job which was created as part of a maintenance plan, we get the following error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Job 'manual_db_backups.Subplan_1'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated. (Microsoft SQL Server, Error: 547) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

We were getting the error even after removing the maintenance plan from SSIS.

To Resolve I did the following:

USE [MSDB]
GO

-- View the Maintenance plan subplans 
select * from sysmaintplan_subplans

-- View the Maintenance plan logs 
select * from sysmaintplan_log

To Delete the subplan:

USE [MSDB]
go

--Delete the Log history for the maintenance plan…

View original post 34 more words

Advertisements

Workaround for syspolicy_purge_history step 3 error on SQL 2012

Posted on Updated on

I have started setting up a couple of servers with SQL 2012 SP2.  And I noticed that the syspolicy_purge_history job is failing on Step 3 with the following error message:

A job step received an error at line 1 in a Powershell script.  The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’.  Correct the script and reschedule the job.

SQL2012_syspolicy

So far I have not seen any valid resolution online, so as a workaround, I have setup the Execution policy to UNDEFINED on the following registry entry:

Computer\HKLM\Software\PowerShell\1\Shelllds\Microsoft.SqlServer.Management.PowerShell.sqlps110
Execution Policy, REG_SZ,Undefined

And as a warning, changing the execution policy to UNDEFINED may open security holes on your server.  If you found a fix for this issues, please feel free to leave a comment so I can update this post.

Update:  I found a Microsoft KB article that talks about adding a GPO to correct the issue.  http://support.microsoft.com/kb/2995870

Thank you

 

How to create a list of servers to be managed in SSMS

Posted on

Most of the time when you try to connect to SQL servers, you often have to connect and enter in the name of the server/instance or you have to click on the drop down to show the previous server you have connected to.  That works for me most of the time, but I am going to show you how to add those servers in such a way that you can easily connect to them by just clicking on the server name.  And you can even setup different types of authentication used and connection properties.

First, you have to open SSMS and select View, then Registered Servers.  And then you can add connections to the servers and instances via the roles assigned on your SQL server.

Registered_Servers

Select the ‘Database Engine’ icon if you would like to connect to the main database, right-click and select ‘New Server Registration’.   And setup the server connection properties.

Server_Registration_Properties