Hi All,

i am trying to update the alert in MSSQL, while updating the job name in the alert i get below error. please suggest how can i fix this.

system details:
---------------------
OS - Windows 2003
MSSQL - 2008 R2

Job cannot be used by an alert. It should first be associated with a server by calling sp_add_jobserver. 
Microsoft SQL Server, Error: 14527

while i tried checking the link suggested in the error msg, but no luck!! it says no additional information available.

https://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=14527

https://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Alert

Regards,
SQL010

Please post the output of "SELECT @@version" and also what version that is reported for SSMS under Help->About. (Because I am right when I assume that you were using SSMS when this happened, am I?)

Hi Erland,

please find the details-

select @@version 

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

version that is reported for SSMS under 

Microsoft SQL Server Management Studio - 10.50.1600.1

Thanks,

SQL010

Job needs to be associated with a valid job server in order to run.

Can you check Select * from sysjobs in msdb database and check if it is associated to different server. I normally use profiler to troubleshoot such errors.

Hi Balmukund,

i checked the sysjobs table in msdb db. i could see column origiating_server_id all the values in that column shows value 0. 

is this what i need to check?

As per my research, error is raised in stored procedure sp_verify_alert

Here is the place of failure

      -- Check that the job is a local job
      IF (NOT EXISTS (SELECT *
                      FROM msdb.dbo.sysjobservers
                      WHERE (job_id = @job_id)
                        AND (server_id = 0)))
      BEGIN
        RAISERROR(14527, -1, -1, @job_name)
        RETURN(1) -- Failure
      END

Can you check msdb.dbo.sysjobservers

Hi Balmukund/All,

i got the fix. in one of the server i was updating the job name on alert "A". i scripted the same & ran the same in rest of the servers by using CMS. i forgot to modify the server name in all rest of the servers. once the below query was updated with the server name & executed it solved the issue

EXEC msdb.dbo.sp_add_jobserver @job_name=N''DBA_BlockedAlert'', @server_name = N''<server_name>''

GO

Thanks,

SQL010

• Marked as answer by sql010 16 hours 8 minutes ago

Great. Thanks for the update.

*select @@version *Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
Jul  9 2008 14:43:34 
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)


version that is reported for SSMS under 
Microsoft SQL Server Management Studio - 10.50.1600.1

That's the RTM version. You should install Service Pack 4 at first possible occasion.