Stored Procedure to run job based on server name

Question

I have a 4 node global cluster. On each node, I have a stored procedure that is called from a SQL job that uses PowerShell to obtain the server name. (The PowerShell script was not working in the job but does work as a sql script.) Based on the name, I call a pair of backup jobs, one to backup the databases and one for the transaction logs. Two of the servers are "located" in the North and two servers are "located" in the East. It appears that the both DB backup job is running twice. Please let me know what I need to change to get this stored procedure to call the jobs only 1 time. Many thanks in advance!!!

Here is the error I get:

Message
Executed as user: Domain\User1. SQLServerAgent Error: Request to run job Backup_DB - FULL - North (from User Domain\User1) refused because the job is already running from a request by User Domain\User1. [SQLSTATE 42000] (Error 22022).  The step failed.

Here is stored procedure that is giving me the error:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_Server_BACKUP]    Script Date: 07/16/2014 15:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_Server_BACKUP]
AS 
    SET NOCOUNT ON ;


Declare @spconfigValues table
(
[name] varchar(1500),
minimum int,
maximum int,
config_value int,
run_value int
)
insert into @spconfigValues
execute sp_configure
------------------------------------------------------
Declare @rv int
set @rv = (select run_value from @spconfigValues where [name] = 'xp_cmdshell')

If @rv is null
Begin 
set @rv = (select convert(int,value_in_use) from sys.configurations where [name] = 'xp_cmdshell') -- gets the server name
End

if @rv = 0
Begin 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
End
-----------------------------------------------------------------------------------------------------------------
Declare @Server_Name_tbl table
(
[server_name] varchar(50)
)

insert into @Server_Name_tbl
execute xp_cmdshell 'powershell.exe "$(Get-WmiObject Win32_Computersystem).name"'


if @rv = 0
Begin 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
End

--select * from @server_name_tbl

Declare @servernm varchar(50)

SET @servernm = (select lower(server_name) from @Server_Name_tbl where server_name is not null) --sets the server name

--select @servernm
-------------------------------------------------------------------------------------------------------------------
--Based on the server name call the correct set of jobs

IF @servernm = 'server_1234N1'
    Begin
        EXEC msdb.dbo.sp_start_job N'Backup_T_LOGS - North';        
        WAITFOR DELAY '00:02:00';
        EXEC msdb.dbo.sp_start_job N'Backup_DB - FULL - North';
    End

IF @servernm = 'server_1234N2'
    Begin
        EXEC msdb.dbo.sp_start_job N'Backup_T_LOGS - North';        
        WAITFOR DELAY '00:02:00';
        EXEC msdb.dbo.sp_start_job N'Backup_DB - FULL - North';
    End

IF @servernm = 'server_2345N1'
    Begin   
        EXEC msdb.dbo.sp_start_job N'Backup_T_LOGS - East';
        WAITFOR DELAY '00:02:00';
        EXEC msdb.dbo.sp_start_job N'Backup_DB - FULL - East';
    End

IF @servernm = 'server_2345N2'  
    Begin   
        EXEC msdb.dbo.sp_start_job N'Backup_T_LOGS - East';
        WAITFOR DELAY '00:02:00';
        EXEC msdb.dbo.sp_start_job N'Backup_DB - FULL - East';
    End

Answer 1

The problem I had was that within each NetBackup job, I was creating the same .bch file. So, when the t-logs backup job would start, it would create a file called, for example, Backup123.bch. Then when the full backup job would run, it would try to create the same .bch file and give the error, "the job is already running.".

Bottom line, once I changed the .bch file name for each job that Veritas was going to create, the process worked as expected.