Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [DPH_BANK_ADMIN_MULTIPLE]
- GO
- /****** Object: StoredProcedure [dbo].[DRIVEPOOL2] Script Date: 21-07-2018 10:34:39 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- --EXEC [dbo].[DRIVEPOOL] @OUT_MAIN_ERROR = ''
- ALTER PROCEDURE [dbo].[DRIVEPOOL2]
- AS
- -----Changes for SAN
- DECLARE @cnt INT = 1
- Declare @driveList varchar(500),
- @cmdline varchar (500),
- @ADJ_REPORT_RCODE int,
- @ServerDrives varchar (500),
- @SeparateDrives varchar(50),
- @individualDrives varchar(50),
- @Wholelength varchar(60),
- @COUNTTEMP int,
- @ReturnCode int,
- @OUT_MAIN_ERROR varchar(50) ,
- @OPERATION varchar(10)='OPEN',
- @DRIVEVALUE varchar (10) = 'D'
- Declare @DriveCheck varchar(10)
- Declare @PassDrive varchar(50)
- Declare @Printin varchar(10)
- CREATE TABLE #temp (SomeCol VARCHAR(500) )
- --================ ADJ
- SELECT @cmdline = 'fsutil fsinfo drives'
- --select @cmdline ADJ
- BEGIN
- Set Nocount ON
- --select @cmdline commands
- IF @OPERATION = 'OPEN'
- BEGIN
- --dump result into temp table
- INSERT #temp
- EXEC @ADJ_REPORT_RCODE = master.dbo.xp_cmdshell @cmdline
- select @ServerDrives = SomeCol from #temp where SomeCol Like '%Drives%'
- --select @ADJ_REPORT_RCODE kali
- --select @ServerDrives Symbols
- SELECT @ServerDrives = REPLACE(@ServerDrives, ':\ ', '');
- --select @ServerDrives withoutSymbols
- SELECT @ServerDrives = REPLACE(@ServerDrives, 'Drives: ', '');
- SELECT @individualDrives = REPLACE(@ServerDrives, ' ', '');
- select @Wholelength = LEN(@individualDrives);
- --select @Wholelength withoutSymbolsLength
- WHILE @cnt <= @Wholelength
- BEGIN
- -- PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';
- -- select @individualDrives KKKKKKK
- -- select @cnt LLLLLL
- SELECT @DriveCheck = SUBSTRING(@individualDrives, @cnt,1) ;
- -- select @DriveCheck PPPPPPPP
- UPDATE [dbo].[Drive_Pool] SET [Drives] = @DriveCheck ,[Status] = '1'
- WHERE [Drives] = @DriveCheck
- SET @cnt = @cnt + 1;
- END;
- select top 1 @PassDrive = Drives from [dbo].[Drive_Pool] where Drives <> @DriveCheck and Status = '0'
- --select @PassDrive kaliss
- --Return @PassDrive
- if @PassDrive IS NULL
- Begin
- SET @OUT_MAIN_ERROR='WAIT'
- SELECT @OUT_MAIN_ERROR
- -- PRINT 'Done IF - Return wait and run';
- End
- Else
- Begin
- -- PRINT 'Done Else - Found the Drive and Returning';
- -------------------------
- -----Changes for SAN
- DECLARE @Text AS VARCHAR(100), @Cmd2 AS VARCHAR(100), @KeyGUID UNIQUEIDENTIFIER ,@open nvarchar(max) , @close nvarchar(max) , @cmd varbinary(max), @Data Varchar(50),
- @sanip VARCHAR(500), @sanuser VARCHAR(500),
- @sanlocation VARCHAR(500), @sanpass VARCHAR(500), @Question varbinary(max) , @checkguid varchar(500) , @Drive_Opener VARCHAR(50)
- ----Changes for SAN
- DECLARE @NOOFDRIVE int = 1
- -------DRIVE CODE
- --Paramters for SAN
- select @sanip = SANIP from dbo.IPS_IPSCONFIG_MASTER;
- select @sanuser = SANUSERNAME from dbo.IPS_IPSCONFIG_MASTER;
- select @sanlocation = SANLOCATION from dbo.IPS_IPSCONFIG_MASTER;
- select @Question = SANPASSWORD from dbo.IPS_IPSCONFIG_MASTER;
- select @checkguid = KEY_GUID('TestKey') from sys.symmetric_keys;
- --Paramters for SAN
- -------------------------
- --select @OUT_MAIN_ERROR = 'N'
- --SET @OUT_MAIN_ERROR = @PassDrive
- Select @PassDrive
- End
- drop table #temp
- END
- ELSE
- BEGIN
- --PRINT 'Connection Going to be Closed';
- --Create temp table to hold result
- CREATE TABLE #conclose (SomeCol VARCHAR(500))
- --=============================ADJ
- SELECT @cmdline = 'net use '+@DRIVEVALUE+': /delete /Y'
- --select @cmdline polos
- --dump result into temp table
- INSERT #conclose
- EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
- SELECT @COUNTTEMP = COUNT(*) FROM #conclose WHERE SomeCol like '%was deleted successfully.%';
- IF @COUNTTEMP = '1'
- Begin
- SET @OUT_MAIN_ERROR='OK'
- SELECT @OUT_MAIN_ERROR
- End
- Else
- Begin
- SET @OUT_MAIN_ERROR='ERR'
- SELECT @OUT_MAIN_ERROR
- End
- --PRINT 'Done Else Successful return a Drive which is available';
- END
- Set Nocount OFF
- END --main
Add Comment
Please, Sign In to add comment