daily pastebin goal
3%
SHARE
TWEET

Untitled

a guest Jul 21st, 2018 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [DPH_BANK_ADMIN_MULTIPLE]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[DRIVEPOOL2]    Script Date: 21-07-2018 10:34:39 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9.  
  10. --EXEC [dbo].[DRIVEPOOL] @OUT_MAIN_ERROR = ''
  11.  
  12. ALTER PROCEDURE [dbo].[DRIVEPOOL2]
  13.  
  14.  
  15.  
  16. AS
  17.  
  18. -----Changes for SAN
  19.  DECLARE @cnt INT = 1
  20.  Declare @driveList varchar(500),
  21.  @cmdline varchar (500),
  22.  @ADJ_REPORT_RCODE int,
  23.  @ServerDrives varchar (500),
  24.  @SeparateDrives varchar(50),
  25.  @individualDrives varchar(50),
  26.  @Wholelength varchar(60),
  27.   @COUNTTEMP int,
  28.   @ReturnCode int,
  29.      @OUT_MAIN_ERROR varchar(50) ,
  30.       @OPERATION varchar(10)='OPEN',
  31.    @DRIVEVALUE varchar (10) = 'D'
  32.  
  33. Declare @DriveCheck varchar(10)
  34. Declare @PassDrive varchar(50)
  35. Declare @Printin varchar(10)
  36.  
  37.  CREATE TABLE #temp (SomeCol VARCHAR(500) )
  38.  
  39.  --================ ADJ
  40. SELECT @cmdline = 'fsutil fsinfo drives'
  41. --select @cmdline ADJ
  42. BEGIN
  43. Set Nocount ON
  44.  
  45. --select @cmdline commands
  46.  
  47. IF @OPERATION = 'OPEN'
  48. BEGIN
  49.  
  50.  
  51. --dump result into temp table
  52. INSERT #temp
  53. EXEC @ADJ_REPORT_RCODE = master.dbo.xp_cmdshell @cmdline
  54.  
  55. select  @ServerDrives = SomeCol from #temp where SomeCol Like '%Drives%'
  56. --select @ADJ_REPORT_RCODE kali
  57.  
  58.  
  59.  
  60. --select @ServerDrives Symbols
  61.  
  62. SELECT @ServerDrives =  REPLACE(@ServerDrives, ':\ ', '');
  63.  
  64. --select @ServerDrives withoutSymbols
  65.  
  66. SELECT @ServerDrives =  REPLACE(@ServerDrives, 'Drives: ', '');
  67.  
  68. SELECT @individualDrives =  REPLACE(@ServerDrives, ' ', '');
  69.  
  70.  
  71. select @Wholelength = LEN(@individualDrives);
  72.  
  73.  
  74. --select @Wholelength withoutSymbolsLength
  75.  
  76. WHILE @cnt <= @Wholelength
  77. BEGIN
  78.  --  PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';
  79.  
  80.    
  81.  -- select @individualDrives KKKKKKK
  82. -- select @cnt LLLLLL
  83.    
  84.    SELECT  @DriveCheck =  SUBSTRING(@individualDrives, @cnt,1) ;
  85.  -- select @DriveCheck PPPPPPPP
  86.    
  87.    UPDATE [dbo].[Drive_Pool]  SET [Drives] = @DriveCheck ,[Status] = '1'
  88.  WHERE [Drives] = @DriveCheck
  89.  
  90.  SET @cnt = @cnt + 1;
  91.  
  92. END;
  93.  
  94.  select top 1 @PassDrive =  Drives from [dbo].[Drive_Pool] where Drives <> @DriveCheck and Status = '0'
  95.  
  96.  --select @PassDrive kaliss
  97.  
  98.  --Return @PassDrive
  99.  
  100.    if @PassDrive IS NULL
  101.       Begin
  102.  
  103.       SET  @OUT_MAIN_ERROR='WAIT'
  104.       SELECT @OUT_MAIN_ERROR
  105.  
  106.  -- PRINT 'Done IF - Return wait and run';
  107.  
  108.    End
  109.    Else
  110.    Begin
  111.  
  112.   -- PRINT 'Done Else - Found the Drive and Returning';
  113.  
  114.  
  115.  
  116.    -------------------------
  117.    -----Changes for SAN
  118.  
  119. DECLARE @Text AS VARCHAR(100), @Cmd2 AS VARCHAR(100), @KeyGUID UNIQUEIDENTIFIER ,@open nvarchar(max) , @close nvarchar(max) , @cmd varbinary(max), @Data Varchar(50),
  120.   @sanip VARCHAR(500),   @sanuser VARCHAR(500),
  121. @sanlocation VARCHAR(500),  @sanpass VARCHAR(500),  @Question varbinary(max) , @checkguid varchar(500) , @Drive_Opener VARCHAR(50)
  122.  
  123.  
  124. ----Changes for SAN
  125.    DECLARE  @NOOFDRIVE int = 1
  126. -------DRIVE CODE
  127.  
  128.  
  129.  
  130. --Paramters for SAN
  131. select @sanip =  SANIP from dbo.IPS_IPSCONFIG_MASTER;
  132. select @sanuser =  SANUSERNAME from dbo.IPS_IPSCONFIG_MASTER;
  133. select @sanlocation =  SANLOCATION from dbo.IPS_IPSCONFIG_MASTER;
  134. select @Question =  SANPASSWORD from dbo.IPS_IPSCONFIG_MASTER;
  135. select @checkguid =  KEY_GUID('TestKey') from sys.symmetric_keys;
  136.  
  137. --Paramters for SAN
  138.  
  139.  
  140.  
  141.  
  142.  
  143.  
  144.  
  145.    -------------------------
  146.  --select  @OUT_MAIN_ERROR =  'N'
  147.  
  148.  --SET  @OUT_MAIN_ERROR =  @PassDrive
  149.  
  150.  Select @PassDrive
  151.  
  152.    End
  153.  
  154.    
  155. drop table #temp
  156.  
  157. END
  158.  
  159. ELSE
  160.  
  161. BEGIN
  162.  
  163.  
  164. --PRINT 'Connection Going to be Closed';
  165.  
  166.    --Create temp table to hold result
  167. CREATE TABLE #conclose (SomeCol VARCHAR(500))
  168.  
  169.  
  170.  
  171. --=============================ADJ
  172. SELECT @cmdline = 'net use '+@DRIVEVALUE+': /delete /Y'
  173. --select @cmdline polos
  174.  
  175. --dump result into temp table
  176. INSERT #conclose
  177. EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
  178.    
  179.  
  180.  
  181.     SELECT @COUNTTEMP = COUNT(*)  FROM #conclose WHERE SomeCol like '%was deleted successfully.%';
  182.  
  183.  
  184. IF @COUNTTEMP = '1'
  185.     Begin
  186.      
  187.      SET  @OUT_MAIN_ERROR='OK'
  188.  
  189.  SELECT @OUT_MAIN_ERROR
  190.  
  191.  
  192.  
  193.     End
  194.     Else
  195.         Begin
  196.          SET  @OUT_MAIN_ERROR='ERR'
  197.  
  198.  SELECT @OUT_MAIN_ERROR
  199.  
  200.     End
  201.  
  202. --PRINT 'Done Else Successful return a Drive which is available';
  203.  
  204.  
  205.  
  206. END
  207.  
  208.  
  209.  
  210. Set Nocount OFF
  211. END  --main
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top