April 5, 2017 at 6:05 am
Team,
IN my database table i have 500 Servers. If server status "1" means active and live. If server status "0" means its offline server.
Now i need to create an SP based on my requirement for updating the servers status from "0 to 1 " based on server name .
Ex : Servr Status
ServA 0
ServB 0
ServC 1
ServD 0
Now i need to Update Server B & D with status 1. with help of SP. MY Stored procedure should have two parameters.
1. with Server name(Servr ) 2. WIth server status(Status).
Please find the my database table structure with sample data for this.
------------------------------------------------------------------------------------------------------
Table :
/****** Object: Table [dbo].[KeepAliveStatus] Script Date: 4/5/2017 10:25:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KeepAliveStatus](
[ServerName] [varchar](25) NOT NULL,
[KeepAliveStatusProdA] [tinyint] NOT NULL,
[KeepAliveStatusProdB] [tinyint] NULL,
[KeepAliveStatusSoakA] [tinyint] NULL,
[KeepAliveStatusSoakB] [tinyint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-------------------------------------------------------------------------
data :
USE [Infra_Support]
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y01P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y02P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y03P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y04P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y05P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y06P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y07P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y08P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y09P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y10P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y11P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y12P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y13P', 0, 0, 0, 0)
GO
INSERT [dbo].[KeepAliveStatus] ([ServerName], [KeepAliveStatusProdA], [KeepAliveStatusProdB], [KeepAliveStatusSoakA], [KeepAliveStatusSoakB]) VALUES (N'CDTAPP-AS-Y14P', 0, 0, 0, 0)
GO
Now i want an SP with 2 parametres which can change the server status from 0 to 1 OR 1 to 0 based on my servername.
NOte : i need to update the server values in "KeepAliveStatusProdA ' with either 0 or 1. rest all columns can be ignorable.
Thanks,
pavan P
April 5, 2017 at 6:07 am
Please don't post multiple threads for the same question.
No replies here. Direct replies to https://qa.sqlservercentral.com/Forums/1869043/Help-required-for-creating-the-Stored-procedure-for-below-logic
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply