Rather annoyingly today, I had this error message pop up when trying to view a Stored Procedure.
The error is generated from SMO (). I knew that the stored procedure hadn’t been updated in a long time and my first thought was that there was some form of corruption.
Read more…
Ever had this really annoying error pop up when you are trying to view or edit a step within a Job? It happened to me again today.
I found a nice simple fix in the end.
Open up the command line tool Navigate to your Microsoft SQL Server\100\DTS\binn folder. From there run regsvr32 dts.dll
You should get a popup stating “DLLReigsterServer in dts.dll succeeded.” You’ll then need to restart Management Studio.
I discovered a small issue in production today. Our new DR server which is currently going through the rigors of testing had all of the Scheduled Jobs left enabled. I was confused why I was getting two alerts for some data validation processing we perform overnight with two different sets of data.
After a few minutes thought, I realised it could only be the DR system and I wanted a quick way to disable all of the jobs rather than shut down the Agent service which would trigger an alert in our monitoring software.
Here is my simple query to disable all jobs as well as one to re-enable, although our DR process is a little more sophisticated to only online jobs which are enabled in production.
/* Disable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 0;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)
/* Enable All Jobs */
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ”
SELECT @SQL = @SQL + N’EXEC msdb.dbo.sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(36)) + ”’, @enabled = 1;’
FROM msdb.dbo.sysjobs
EXEC (@SQL)