Hotfix for SCVMM 2012 R2 Update Rollup 5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

 
<SPROC>
ALTER PROCEDURE [dbo].[prc_WLC_GetUniqueComputerName]
(
    @NamePattern [nvarchar](255),
    @OutputName  [nvarchar](255) output,
    @FoundUniqueName BIT output
)
AS
DECLARE @error INT
SET @error = 0
SET NOCOUNT ON
DECLARE @RNextIndex INT -- Index for the reversed string
DECLARE @NextIndex INT -- Index for the direct string
DECLARE @VObjectName [nvarchar](255) -- the top name used from the vobject table
DECLARE @RegexPattern [nvarchar](255) -- the name pattern in reg ex format for 'like' queries
SET @RegexPattern = (REPLACE(@NamePattern ,'#', '[0-9]'))
SET @FoundUniqueName = 0
SET @RegexPattern = @RegexPattern + '%'
--Retrieve the latest used name in the pattern
SELECT TOP 1 @VObjectName = Name FROM tbl_WLC_VObject WHERE Name LIKE @RegexPattern ORDER BY Name DESC
SELECT TOP 1 @OutputName = ComputerName FROM tbl_WLC_VMDeploymentConfig WHERE ComputerName LIKE @RegexPattern ORDER BY ComputerName DESC
-- Look at both existing VMs and inflight creations from the vm config, find the last name. 
IF ( @OutputName IS NULL OR (@VObjectName IS NOT NULL AND @VObjectName > @OutputName) )
BEGIN
                SET @OutputName = @VObjectName
END
IF ( @OutputName IS NULL ) -- If the name wasn't found in either the VObject or the Deployment config then start from 0
BEGIN
                SET @OutputName = (REPLACE(@NamePattern ,'#', '0'))
END
IF ( @OutputName = REPLACE(@NamePattern ,'#', '9') ) -- Check if the name is the final one in the series
BEGIN
                -- Final number is used, look for gaps.
                DECLARE @usedPatternNumbers TABLE
                (
                                   NUMBER INT
                )
                DECLARE @NextNumber INT
                -- Gather all the used numbers, this is currently somewhat expensive. Need to think of a better way.
                INSERT INTO @usedPatternNumbers
                SELECT dbo.[fn_WLC_GetSequenceNumberFromPatternName](@NamePattern, Name) FROM
                (SELECT Name FROM tbl_WLC_VObject WHERE Name LIKE @RegexPattern
                UNION
                SELECT ComputerName AS Name FROM tbl_WLC_VMDeploymentConfig WHERE ComputerName LIKE @RegexPattern) AS MergedNames
 
                -- Find the first gap and increment it to get the next number
                SELECT @NextNumber = 
                CASE WHEN MAX(NUMBER) = COUNT(NUMBER)
                                THEN CAST(NULL AS INTEGER)
                WHEN MIN(NUMBER) > 1
                                THEN 1
                WHEN MAX(NUMBER) <> COUNT(NUMBER)
                                THEN (
                                                SELECT MIN(NUMBER) + 1
                                                FROM @usedPatternNumbers
                                                WHERE (NUMBER + 1) NOT IN (SELECT NUMBER FROM @usedPatternNumbers))
                ELSE NULL
                END
                FROM @usedPatternNumbers;
                IF ( @NextNumber IS NOT NULL )
                BEGIN
                                SET @OutputName = dbo.[fn_WLC_GetPatternNameFromSequenceNumber] ( @NamePattern, @NextNumber )
                                SET @FoundUniqueName = 1
                END
END
ELSE
BEGIN
    DECLARE @LastUsedSequenceNumber INT
    SET @LastUsedSequenceNumber = dbo.[fn_WLC_GetSequenceNumberFromPatternName](@NamePattern, @OutputName)
    SET @OutputName = dbo.[fn_WLC_GetPatternNameFromSequenceNumber](@NamePattern, @LastUsedSequenceNumber + 1)
    SET @FoundUniqueName = 1
END
SELECT @error = @@ERROR
SET NOCOUNT OFF
RETURN @error
</SPROC>

<SPROC> ALTER PROCEDURE [dbo].[prc_WLC_GetUniqueComputerName] ( @NamePattern [nvarchar](255), @OutputName [nvarchar](255) output, @FoundUniqueName BIT output ) AS DECLARE @error int SET @error = 0 SET NOCOUNT ON DECLARE @RNextIndex int -- Index for the reversed string DECLARE @NextIndex int -- Index for the direct string DECLARE @VObjectName [nvarchar](255) -- the top name used from the vobject table DECLARE @RegexPattern [nvarchar](255) -- the name pattern in reg ex format for 'like' queries SET @RegexPattern = (REPLACE(@NamePattern ,'#', '[0-9]')) SET @FoundUniqueName = 0 SET @RegexPattern = @RegexPattern + '%' --Retrieve the latest used name in the pattern SELECT TOP 1 @VObjectName = Name FROM tbl_WLC_VObject where Name like @RegexPattern ORDER BY Name DESC SELECT TOP 1 @OutputName = ComputerName FROM tbl_WLC_VMDeploymentConfig where ComputerName like @RegexPattern ORDER BY ComputerName DESC -- Look at both existing VMs and inflight creations from the vm config, find the last name. IF ( @OutputName IS NULL OR (@VObjectName IS NOT NULL AND @VObjectName > @OutputName) ) BEGIN SET @OutputName = @VObjectName END IF ( @OutputName IS NULL ) -- If the name wasn't found in either the VObject or the Deployment config then start from 0 BEGIN SET @OutputName = (REPLACE(@NamePattern ,'#', '0')) END IF ( @OutputName = REPLACE(@NamePattern ,'#', '9') ) -- Check if the name is the final one in the series BEGIN -- Final number is used, look for gaps. DECLARE @usedPatternNumbers TABLE ( Number int ) DECLARE @NextNumber int -- Gather all the used numbers, this is currently somewhat expensive. Need to think of a better way. INSERT INTO @usedPatternNumbers SELECT dbo.[fn_WLC_GetSequenceNumberFromPatternName](@NamePattern, Name) FROM (SELECT Name FROM tbl_WLC_VObject where Name like @RegexPattern UNION SELECT ComputerName AS Name FROM tbl_WLC_VMDeploymentConfig where ComputerName like @RegexPattern) AS MergedNames -- Find the first gap and increment it to get the next number SELECT @NextNumber = CASE WHEN MAX(Number) = COUNT(Number) THEN CAST(NULL AS INTEGER) WHEN MIN(Number) > 1 THEN 1 WHEN MAX(Number) <> COUNT(Number) THEN ( SELECT MIN(Number) + 1 FROM @usedPatternNumbers WHERE (Number + 1) NOT IN (SELECT Number FROM @usedPatternNumbers)) ELSE NULL END FROM @usedPatternNumbers; IF ( @NextNumber IS NOT NULL ) BEGIN SET @OutputName = dbo.[fn_WLC_GetPatternNameFromSequenceNumber] ( @NamePattern, @NextNumber ) SET @FoundUniqueName = 1 END END ELSE BEGIN DECLARE @LastUsedSequenceNumber int SET @LastUsedSequenceNumber = dbo.[fn_WLC_GetSequenceNumberFromPatternName](@NamePattern, @OutputName) SET @OutputName = dbo.[fn_WLC_GetPatternNameFromSequenceNumber](@NamePattern, @LastUsedSequenceNumber + 1) SET @FoundUniqueName = 1 END SELECT @error = @@ERROR SET NOCOUNT OFF RETURN @error </SPROC>