Stephan Bourgeois
asked on
How to lock a table waiting for a store procedure is executed
Hi,
I am not a SQL programmer expert and I need some help to complete a store procedure.
We will run a Powershell script to do a TCPIP address mass change and the powershell script is running a MSSQL store procedure to retrieve the next available IP address and it is updating the row to set the computername,
There is a high possibility that the script will run simultaenous on many computers and I want to avoid duplicate address.
I am looking at the WITH (TABLOCK), but it gives me a syntax error when I am writing it like the following line:
[code][/CREATE PROCEDURE [dbo].[sp_GetNewIPTST]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN TRANSACTION
SET @NewIP = (SELECT IP FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewMASK = (SELECT MASK FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewGateway = (SELECT GATEWAY FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SELECT @NewIP, @NewMASK, @NewGateway
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
END;
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;
INSERT INTO [dbo].[NetHosts]([GROUPNAM E],[HOSTNA ME],[PREVI OUS_IP],[N EW_IP],[Ex ecutionDat e]) VALUES (@GroupName,@ComputerName, @PreviousI P,@NewIP,G ETDATE());
COMMIT TRANSACTION
RETURN 0
code]
So I am asking the experts. What would be the best way to avoid simultaneous reading?
Thanks
I am not a SQL programmer expert and I need some help to complete a store procedure.
We will run a Powershell script to do a TCPIP address mass change and the powershell script is running a MSSQL store procedure to retrieve the next available IP address and it is updating the row to set the computername,
There is a high possibility that the script will run simultaenous on many computers and I want to avoid duplicate address.
I am looking at the WITH (TABLOCK), but it gives me a syntax error when I am writing it like the following line:
SET @NewIP = (SELECT IP WITH (TABLOCK) FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
[code][/CREATE PROCEDURE [dbo].[sp_GetNewIPTST]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN TRANSACTION
SET @NewIP = (SELECT IP FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewMASK = (SELECT MASK FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewGateway = (SELECT GATEWAY FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SELECT @NewIP, @NewMASK, @NewGateway
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
END;
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;
INSERT INTO [dbo].[NetHosts]([GROUPNAM
COMMIT TRANSACTION
RETURN 0
code]
So I am asking the experts. What would be the best way to avoid simultaneous reading?
Thanks
ASKER
All the IP are already in the list. The way I am getting the next available IP is by selecting the next empty HOSTNAME field base on a Groupname.
SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0)))
So, if the SELECT is executed simultaenously before the HOSTNAME get updated, it may get the same IP.
SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0)))
So, if the SELECT is executed simultaenously before the HOSTNAME get updated, it may get the same IP.
I meant the other table (NetHosts) where you're going to insert the New IP. Unless you want to let duplicate IPs in that table.
ASKER
NetHosts table is not important. It is just a log.
The important is the table lstIPs. As soon the first SELECT is getting the IP, it's updating the same row to write the HOSTNAME, so the next SELECT for the same GroupName will return the next row.
The important is the table lstIPs. As soon the first SELECT is getting the IP, it's updating the same row to write the HOSTNAME, so the next SELECT for the same GroupName will return the next row.
When do you update lstIPs table with the new ip entry? I can't see that in your SP.
ASKER
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;
You should change the logic of the stored procedure to accomplish your goal.
Even with an update lock, you would end up having systems granted duplicate IPs and the host associated over-written by the last of the systems it was run on in the situation.
This is because you are not selecting on your lock, you have a completely separate selection.
It's not often I suggest this, because reasons, but this is a rare occurrence where a cursor makes a lot of sense to be used in the update process. It will lock the table and allow you to utilize the variables the way you seem keen on here.
Running to a train so I did this a bit slap dash, I we might need to select as in the cursor instead of using the set ..
Even with an update lock, you would end up having systems granted duplicate IPs and the host associated over-written by the last of the systems it was run on in the situation.
This is because you are not selecting on your lock, you have a completely separate selection.
It's not often I suggest this, because reasons, but this is a rare occurrence where a cursor makes a lot of sense to be used in the update process. It will lock the table and allow you to utilize the variables the way you seem keen on here.
Running to a train so I did this a bit slap dash, I we might need to select as in the cursor instead of using the set ..
[/CREATE PROCEDURE [dbo].[sp_GetNewIPTST]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN TRANSACTION
-- Populate Table variable with Commands to Backup Databases
INSERT INTO @Tbl_CMDList
SELECT N'
ALTER DATABASE [' + DBName + N'] SET SINGLE_USER WITH NO_WAIT
DBCC CHECKDB ([' + DBName + N'], REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS
ALTER DATABASE [' + DBName + N'] SET MULTI_USER WITH NO_WAIT
'
FROM
@Tbl_NameList
-- Print and Execute Each backup Command from the Table
DECLARE Cur_ParseCMD CURSOR FOR (
SELECT
SET @NewIP = (SELECT IP FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewMASK = (SELECT MASK FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewGateway = (SELECT GATEWAY FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
END;
SELECT @NewIP, @NewMASK, @NewGateway
)
OPEN Cur_ParseCMD
FETCH Cur_ParseCMD INTO @NewIP, @NewMASK, @NewGateway
WHILE @@Fetch_Status = 0 BEGIN
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;
INSERT INTO [dbo].[NetHosts]([GROUPNAME],[HOSTNAME],[PREVIOUS_IP],[NEW_IP],[ExecutionDate]) VALUES (@GroupName,@ComputerName,@PreviousIP,@NewIP,GETDATE());
FETCH Cur_ParseCMD INTO @NewIP, @NewMASK, @NewGateway
END
CLOSE Cur_ParseCMD
DEALLOCATE Cur_ParseCMD
COMMIT TRANSACTION
RETURN 0
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;You are not updating the IP meaning that every time you running this you'll get the same IP unless you're updating/inserting a new IP in other process or stored procedure.
ASKER
The lstIPs table contain all the available IP address associated to a GROUPNAME, MASK and GATEWAY. Almost 3000 entries.
IP_ID GROUPNAME IP MASK GATEWAY HOSTNAME
2 Production North 10.30.0.2 255.255.254.0 10.30.0.1 MYHOSTNAME
3 Production North 10.30.0.3 255.255.254.0 10.30.0.1 MYHOSTNAME2
4 Production North 10.30.0.4 255.255.254.0 10.30.0.1 MYHOSTNAME_ETC
5 Production North 10.30.0.5 255.255.254.0 10.30.0.1 NULL
6 Production North 10.30.0.6 255.255.254.0 10.30.0.1 NULL
7 Production North 10.30.0.7 255.255.254.0 10.30.0.1 NULL
8 Production North 10.30.0.8 255.255.254.0 10.30.0.1 NULL
9 Production North 10.30.0.9 255.255.254.0 10.30.0.1 NULL
10 Production North 10.30.0.10 255.255.254.0 10.30.0.1 NULL
11 Production North 10.30.0.11 255.255.254.0 10.30.0.1 NULL
12 Production North 10.30.0.12 255.255.254.0 10.30.0.1 NULL
13 Production North 10.30.0.13 255.255.254.0 10.30.0.1 NULL
14 Production North 10.30.0.14 255.255.254.0 10.30.0.1 NULL
15 Production North 10.30.0.15 255.255.254.0 10.30.0.1 NULL
16 Production North 10.30.0.16 255.255.254.0 10.30.0.1 NULL
17 Production North 10.30.0.17 255.255.254.0 10.30.0.1 NULL
.
.
.
412 Production North 10.30.1.157 255.255.254.0 10.30.0.1 NULL
413 Production North 10.30.1.158 255.255.254.0 10.30.0.1 NULL
414 Production North 10.30.1.159 255.255.254.0 10.30.0.1 NULL
415 Production North 10.30.1.160 255.255.254.0 10.30.0.1 NULL
417 Production South 10.30.2.2 255.255.254.0 10.30.2.1 NULL
418 Production South 10.30.2.3 255.255.254.0 10.30.2.1 NULL
419 Production South 10.30.2.4 255.255.254.0 10.30.2.1 NULL
420 Production South 10.30.2.5 255.255.254.0 10.30.2.1 NULL
421 Production South 10.30.2.6 255.255.254.0 10.30.2.1 NULL
422 Production South 10.30.2.7 255.255.254.0 10.30.2.1 NULL
423 Production South 10.30.2.8 255.255.254.0 10.30.2.1 NULL
424 Production South 10.30.2.9 255.255.254.0 10.30.2.1 NULL
.
.
.
824 Production South 10.30.3.155 255.255.254.0 10.30.2.1 NULL
825 Production South 10.30.3.156 255.255.254.0 10.30.2.1 NULL
826 Production South 10.30.3.157 255.255.254.0 10.30.2.1 NULL
827 Production South 10.30.3.158 255.255.254.0 10.30.2.1 NULL
828 Production South 10.30.3.159 255.255.254.0 10.30.2.1 NULL
830 0-1st floor tower 10.30.4.2 255.255.254.0 10.30.4.1 NULL
831 0-1st floor tower 10.30.4.3 255.255.254.0 10.30.4.1 NULL
832 0-1st floor tower 10.30.4.4 255.255.254.0 10.30.4.1 NULL
833 0-1st floor tower 10.30.4.5 255.255.254.0 10.30.4.1 NULL
834 0-1st floor tower 10.30.4.6 255.255.254.0 10.30.4.1 NULL
835 0-1st floor tower 10.30.4.7 255.255.254.0 10.30.4.1 NULL
Here are my new version of the SP inspired by intensive research on the net.
Would this way, the table will lock until COMMIT TRANSACTION is executed?
Thanks Ben for the answer. Your way is quite compicated and I am not sure I would like to try such a procedure the day before doing the big change.
IP_ID GROUPNAME IP MASK GATEWAY HOSTNAME
2 Production North 10.30.0.2 255.255.254.0 10.30.0.1 MYHOSTNAME
3 Production North 10.30.0.3 255.255.254.0 10.30.0.1 MYHOSTNAME2
4 Production North 10.30.0.4 255.255.254.0 10.30.0.1 MYHOSTNAME_ETC
5 Production North 10.30.0.5 255.255.254.0 10.30.0.1 NULL
6 Production North 10.30.0.6 255.255.254.0 10.30.0.1 NULL
7 Production North 10.30.0.7 255.255.254.0 10.30.0.1 NULL
8 Production North 10.30.0.8 255.255.254.0 10.30.0.1 NULL
9 Production North 10.30.0.9 255.255.254.0 10.30.0.1 NULL
10 Production North 10.30.0.10 255.255.254.0 10.30.0.1 NULL
11 Production North 10.30.0.11 255.255.254.0 10.30.0.1 NULL
12 Production North 10.30.0.12 255.255.254.0 10.30.0.1 NULL
13 Production North 10.30.0.13 255.255.254.0 10.30.0.1 NULL
14 Production North 10.30.0.14 255.255.254.0 10.30.0.1 NULL
15 Production North 10.30.0.15 255.255.254.0 10.30.0.1 NULL
16 Production North 10.30.0.16 255.255.254.0 10.30.0.1 NULL
17 Production North 10.30.0.17 255.255.254.0 10.30.0.1 NULL
.
.
.
412 Production North 10.30.1.157 255.255.254.0 10.30.0.1 NULL
413 Production North 10.30.1.158 255.255.254.0 10.30.0.1 NULL
414 Production North 10.30.1.159 255.255.254.0 10.30.0.1 NULL
415 Production North 10.30.1.160 255.255.254.0 10.30.0.1 NULL
417 Production South 10.30.2.2 255.255.254.0 10.30.2.1 NULL
418 Production South 10.30.2.3 255.255.254.0 10.30.2.1 NULL
419 Production South 10.30.2.4 255.255.254.0 10.30.2.1 NULL
420 Production South 10.30.2.5 255.255.254.0 10.30.2.1 NULL
421 Production South 10.30.2.6 255.255.254.0 10.30.2.1 NULL
422 Production South 10.30.2.7 255.255.254.0 10.30.2.1 NULL
423 Production South 10.30.2.8 255.255.254.0 10.30.2.1 NULL
424 Production South 10.30.2.9 255.255.254.0 10.30.2.1 NULL
.
.
.
824 Production South 10.30.3.155 255.255.254.0 10.30.2.1 NULL
825 Production South 10.30.3.156 255.255.254.0 10.30.2.1 NULL
826 Production South 10.30.3.157 255.255.254.0 10.30.2.1 NULL
827 Production South 10.30.3.158 255.255.254.0 10.30.2.1 NULL
828 Production South 10.30.3.159 255.255.254.0 10.30.2.1 NULL
830 0-1st floor tower 10.30.4.2 255.255.254.0 10.30.4.1 NULL
831 0-1st floor tower 10.30.4.3 255.255.254.0 10.30.4.1 NULL
832 0-1st floor tower 10.30.4.4 255.255.254.0 10.30.4.1 NULL
833 0-1st floor tower 10.30.4.5 255.255.254.0 10.30.4.1 NULL
834 0-1st floor tower 10.30.4.6 255.255.254.0 10.30.4.1 NULL
835 0-1st floor tower 10.30.4.7 255.255.254.0 10.30.4.1 NULL
Here are my new version of the SP inspired by intensive research on the net.
USE [IPRENUM]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNewIP] Script Date: 2017-04-13 07:29:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetNewIP]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN TRANSACTION
SELECT * FROM lstIPS
WITH (TABLOCK)
SET @NewIP = (SELECT IP FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewMASK = (SELECT MASK FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SET @NewGateway = (SELECT GATEWAY FROM lstIPs WHERE IP_ID = (SELECT DISTINCT MIN(IP_ID) FROM lstIPs WHERE (GROUPNAME = @GroupName) AND ((HOSTNAME IS NULL) OR (LEN(HOSTNAME) = 0))))
SELECT @NewIP, @NewMASK, @NewGateway
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
END;
UPDATE [dbo].[lstIPs] SET [HOSTNAME] = @ComputerName WHERE IP = @NewIP;
COMMIT TRANSACTION
INSERT INTO [dbo].[NetHosts]([GROUPNAME],[HOSTNAME],[PREVIOUS_IP],[NEW_IP],[ExecutionDate]) VALUES (@GroupName,@ComputerName,@PreviousIP,@NewIP,GETDATE());
RETURN 0
GO
Would this way, the table will lock until COMMIT TRANSACTION is executed?
Thanks Ben for the answer. Your way is quite compicated and I am not sure I would like to try such a procedure the day before doing the big change.
You just need to assure that the operation is fast enough to reduce to 0.000000001% the probability of having repeating values:
ALTER PROCEDURE [dbo].[sp_GetNewIP]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
SELECT @NewIP =IP, @NewMASK = MASK, @NewGateway = GATEWAY
FROM lstIPs
WHERE IP_ID = (SELECT MIN(IP_ID)
FROM lstIPs
WHERE GROUPNAME = @GroupName AND (HOSTNAME IS NULL OR LEN(HOSTNAME) = 0))
SELECT @NewIP, @NewMASK, @NewGateway
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
END;
UPDATE [dbo].[lstIPs]
SET [HOSTNAME] = @ComputerName
WHERE IP = (SELECT MIN(IP_ID)
FROM lstIPs
WHERE GROUPNAME = @GroupName AND (HOSTNAME IS NULL OR LEN(HOSTNAME) = 0))
INSERT INTO [dbo].[NetHosts]([GROUPNAME],[HOSTNAME],[PREVIOUS_IP],[NEW_IP],[ExecutionDate]) VALUES (@GroupName,@ComputerName,@PreviousIP,@NewIP,GETDATE());
GO
Hey just an FYI I looked over the code this AM and it was as I said slap-dash, I'm just trying to deliver my daughter to daycare and get a train.
basically I left a table creation in not needed, and put a select verb where not needed and left the loop fetch in not needed
in all the intention was just to wrap your existing code into the cursor so it would only be an extra 3 lines instead of re-wriring your code to be more efficient since I had to head out..
In actuality you should really be doing your select as part of the update and using an update lock, instead of using separated sets into variables.
I tried to update this from the phone this morning but wasn't able to so I just wanted to give you a heads up on that.
basically I left a table creation in not needed, and put a select verb where not needed and left the loop fetch in not needed
in all the intention was just to wrap your existing code into the cursor so it would only be an extra 3 lines instead of re-wriring your code to be more efficient since I had to head out..
In actuality you should really be doing your select as part of the update and using an update lock, instead of using separated sets into variables.
I tried to update this from the phone this morning but wasn't able to so I just wanted to give you a heads up on that.
Hey @Sergio
I had time to re-write this as a more efficient Stored procedure instead of wrapping your code in a Cursor.
Turns out you had some typos etc in your code that were probably due to the source you used which I didn;t notice until I really looked at it.
I noticed you sent data so I took that and added a few more entries of my own so that I could test appropriately a few scenarios and made sure my code functions as expected for you.
Note I named my DB "IPDB", so in my code where it says "USE [IPDB]" replace "IPDB with your Database name.
Here is the Drop and Create for the fixed Stored Procedure:
Here is the Code I used for testing:
[u]Example output from one of the tests:
Here are Samples of my lstIPs table BEFORE Testing:
Here are samples of my lstIPs table and my NetHosts Table AFTER Testing:[/u]
lstIPs
NetHosts
I had time to re-write this as a more efficient Stored procedure instead of wrapping your code in a Cursor.
Turns out you had some typos etc in your code that were probably due to the source you used which I didn;t notice until I really looked at it.
I noticed you sent data so I took that and added a few more entries of my own so that I could test appropriately a few scenarios and made sure my code functions as expected for you.
Note I named my DB "IPDB", so in my code where it says "USE [IPDB]" replace "IPDB with your Database name.
Here is the Drop and Create for the fixed Stored Procedure:
USE [IPDB]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNewIPTST] Script Date: 4/13/2017 11:31:07 AM ******/
DROP PROCEDURE [dbo].[sp_GetNewIPTST]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNewIPTST] Script Date: 4/13/2017 11:31:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetNewIPTST]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN
DECLARE @tDate_Time DateTime
IF @GroupName = 'DHCP'
BEGIN
SET @NewGateway = 'AUTOMATIC';
SET @NewIP = 'AUTOMATIC';
SET @NewMASK = 'AUTOMATIC';
SET @tDate_Time = GETDATE();
END
IF @GroupName <> 'DHCP'
BEGIN
UPDATE
[dbo].[lstIPs]
with
(updlock)
SET
[HOSTNAME] = @ComputerName, @NewIP = IP, @NewMask = MASK, @NewGateway = GATEWAY, @tDate_Time = GETDATE()
WHERE (
IP_ID = (
SELECT DISTINCT MIN (IP_ID)
FROM lstIPs
WHERE ( GROUPNAME = @GroupName
AND ( HOSTNAME IS NULL
OR ( LEN(HOSTNAME) = 0
)
)
)
)
)
END
INSERT INTO
[dbo].[NetHosts]([GROUPNAME],[HOSTNAME],[PREVIOUS_IP],[NEW_IP],[ExecutionDate])
VALUES
(@GroupName,@ComputerName,@PreviousIP,@NewIP,@tDate_Time);
RETURN
END
GO
Here is the Code I used for testing:
USE [IPDB]
GO
DECLARE @return_value int,
@NewIP nvarchar(50),
@NewMASK nvarchar(50),
@NewGateway nvarchar(50)
EXEC @return_value = [dbo].[sp_GetNewIPTST]
@ComputerName = N'''Testing_SP''',
@PreviousIP = N'9.9.9.9',
@GroupName = N'Production South',
@NewIP = @NewIP OUTPUT,
@NewMASK = @NewMASK OUTPUT,
@NewGateway = @NewGateway OUTPUT
SELECT @NewIP as N'@NewIP',
@NewMASK as N'@NewMASK',
@NewGateway as N'@NewGateway'
SELECT 'Return Value' = @return_value
GO
DECLARE @return_value int,
@NewIP nvarchar(50),
@NewMASK nvarchar(50),
@NewGateway nvarchar(50)
EXEC @return_value = [dbo].[sp_GetNewIPTST]
@ComputerName = N'Testing_SP',
@PreviousIP = N'7.7.7.7',
@GroupName = N'Production South',
@NewIP = @NewIP OUTPUT,
@NewMASK = @NewMASK OUTPUT,
@NewGateway = @NewGateway OUTPUT
SELECT @NewIP as N'@NewIP',
@NewMASK as N'@NewMASK',
@NewGateway as N'@NewGateway'
SELECT 'Return Value' = @return_value
GO
DECLARE @return_value int,
@NewIP nvarchar(50),
@NewMASK nvarchar(50),
@NewGateway nvarchar(50)
EXEC @return_value = [dbo].[sp_GetNewIPTST]
@ComputerName = N'The_Stanley_Parable',
@PreviousIP = N'8.8.8.8',
@GroupName = N'0-1st floor tower',
@NewIP = @NewIP OUTPUT,
@NewMASK = @NewMASK OUTPUT,
@NewGateway = @NewGateway OUTPUT
SELECT @NewIP as N'@NewIP',
@NewMASK as N'@NewMASK',
@NewGateway as N'@NewGateway'
SELECT 'Return Value' = @return_value
GO
[u]Example output from one of the tests:
Here are Samples of my lstIPs table BEFORE Testing:
Here are samples of my lstIPs table and my NetHosts Table AFTER Testing:[/u]
lstIPs
NetHosts
ASKER
I tried your code with the following
I am getting the following error.
(0 row(s) affected)
Msg 515, Level 16, State 2, Procedure sp_GetNewIPTST, Line 46
Cannot insert the value NULL into column 'NEW_IP', table 'IPRENUM.dbo.NetHostsTST'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
USE [IPRENUM]
GO
DECLARE @return_value int,
@NewIP nvarchar(50),
@NewMASK nvarchar(50),
@NewGateway nvarchar(50)
EXEC @return_value = [dbo].[sp_GetNewIPTST]
@ComputerName = N'VMWINXP',
@PreviousIP = N'172.16.21.174',
@GroupName = N'0-1st floor tower',
@NewIP = @NewIP OUTPUT,
@NewMASK = @NewMASK OUTPUT,
@NewGateway = @NewGateway OUTPUT
SELECT @NewIP as N'@NewIP',
@NewMASK as N'@NewMASK',
@NewGateway as N'@NewGateway'
SELECT 'Return Value' = @return_value
GO
I am getting the following error.
(0 row(s) affected)
Msg 515, Level 16, State 2, Procedure sp_GetNewIPTST, Line 46
Cannot insert the value NULL into column 'NEW_IP', table 'IPRENUM.dbo.NetHostsTST';
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Hey Serge,
testing it on my own copy of the DB.
Are you certain there is a free IP in that range?
The stored procedure doesn;t have any error handling for a scenario where it can't match
In my DB I do allow for this by allowing NULL to be a valid result int he NetHosts table for IP
Would it perhaps make more sense to capture this as something else like "Unable_To_Obtain_IP"?
Ben
testing it on my own copy of the DB.
Are you certain there is a free IP in that range?
The stored procedure doesn;t have any error handling for a scenario where it can't match
In my DB I do allow for this by allowing NULL to be a valid result int he NetHosts table for IP
Would it perhaps make more sense to capture this as something else like "Unable_To_Obtain_IP"?
Ben
Yup, I confirmed this works fine for me until the table runs out of IPs, then it returns NULLs because it can;t match anything
My table gets updated just fine because I accept NULLs, however, it would probably be best to handle this gracefully.
My table gets updated just fine because I accept NULLs, however, it would probably be best to handle this gracefully.
Here you go Serge,
I am now assigning "NO_IP_AVAILABLE" to be put into Nethosts for all fields in the case where the IP returned by the update is NULL because it can not be matched.
Furthermore, I'm now catching if any error occurs with the update (Note: not updating because SQL finds no valid records to update is NOT a failure, that is a success. Trying to put a value into the lstIPs table that isn't valid for the column IS a failure) and output a note to that effect to the NetHostsTable as well.
Example Output:
I am now assigning "NO_IP_AVAILABLE" to be put into Nethosts for all fields in the case where the IP returned by the update is NULL because it can not be matched.
Furthermore, I'm now catching if any error occurs with the update (Note: not updating because SQL finds no valid records to update is NOT a failure, that is a success. Trying to put a value into the lstIPs table that isn't valid for the column IS a failure) and output a note to that effect to the NetHostsTable as well.
USE [IPDB]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNewIPTST] Script Date: 4/13/2017 11:31:07 AM ******/
DROP PROCEDURE [dbo].[sp_GetNewIPTST]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNewIPTST] Script Date: 4/13/2017 11:31:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetNewIPTST]
@ComputerName nvarchar(50),
@PreviousIP nvarchar(50),
@GroupName nvarchar(50),
@NewIP nvarchar(50) OUTPUT,
@NewMASK nvarchar(50) OUTPUT,
@NewGateway nvarchar(50) OUTPUT
AS
BEGIN
DECLARE @tDate_Time DateTime
IF @GroupName = 'DHCP'
BEGIN
Select @NewIP = 'AUTOMATIC', @NewMask = 'AUTOMATIC', @NewGateway = 'AUTOMATIC', @tDate_Time = GETDATE()
END
IF @GroupName <> 'DHCP'
BEGIN Try
UPDATE
[dbo].[lstIPs]
with
(updlock)
SET
[HOSTNAME] = @ComputerName, @NewIP = IP, @NewMask = MASK, @NewGateway = GATEWAY, @tDate_Time = GETDATE()
WHERE (
IP_ID = (
SELECT DISTINCT MIN (IP_ID)
FROM lstIPs
WHERE ( GROUPNAME = @GroupName
AND ( HOSTNAME IS NULL
OR ( LEN(HOSTNAME) = 0
)
)
)
) AND IP_ID IS NOT NULL
)
END Try
Begin Catch
Select @NewIP = 'Update_Error', @NewMask = 'Update_Error', @NewGateway = 'Update_Error', @tDate_Time = GETDATE()
End Catch
IF @NewIP IS NULL
BEGIN
Select @NewIP = 'NO_IP_AVAILABLE', @NewMask = 'NO_IP_AVAILABLE', @NewGateway = 'NO_IP_AVAILABLE', @tDate_Time = GETDATE()
END
INSERT INTO
[dbo].[NetHosts]([GROUPNAME],[HOSTNAME],[PREVIOUS_IP],[NEW_IP],[ExecutionDate])
VALUES
(@GroupName,@ComputerName,@PreviousIP,@NewIP,@tDate_Time);
RETURN
END
GO
Example Output:
ASKER
If I execute this part individualy:
It returned a row, the next available IP. When I run the whole procedure, it failed.
SELECT DISTINCT MIN (IP_ID)
FROM lstIPs
WHERE ( GROUPNAME = @GroupName
AND ( HOSTNAME IS NULL
OR ( LEN(HOSTNAME) = 0
)
)
)
It returned a row, the next available IP. When I run the whole procedure, it failed.
Serge,
Your error you showed is about inserting into the NetHosts DB
The error is not about picking from the lstIPs table.
Can you check the lstIPs table for a valid IP match and show it to me? because in my table I only return a NULL IP when there is NO hostname slot that is empty.
If there are hostname values there which are empty but not being returned then give me a few and I'll see if I can replicate your issue because I can only cause a NULL value to return when there is no IP available within that group.
Also did you update with the new proc that will distinguish between an error in each step?
Your error you showed is about inserting into the NetHosts DB
The error is not about picking from the lstIPs table.
Can you check the lstIPs table for a valid IP match and show it to me? because in my table I only return a NULL IP when there is NO hostname slot that is empty.
If there are hostname values there which are empty but not being returned then give me a few and I'll see if I can replicate your issue because I can only cause a NULL value to return when there is no IP available within that group.
Also did you update with the new proc that will distinguish between an error in each step?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Ben for your help. That help me a lot
Hello Serge, glad to help :)