convert_share_to_drive

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.convert_share_to_drive') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.convert_share_to_drive
GO

CREATE PROCEDURE convert_share_to_drive
     @share VARCHAR(30) = ' ', 
     @drive VARCHAR(5) = ' ' OUTPUT, 
     @path VARCHAR(50) = ' ',
     @source VARCHAR(30) = ' ',
     @sourcePassword VARCHAR(30) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(30)

/*
  Procedure convert_share_to_drive will return a logical 
  drive name for a valid network share name.
*/

IF    (@share = ' ') 
   OR (@path = ' ') 
   OR (@source = ' ')
  RETURN 1

  SELECT @myName = 'admin.convert_share_to_drive' 

  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"master..xp_cmdshell ' + "'net share " + 
                @share + ">" + @path + "\convert.share'" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus

  EXEC hold_semaphore 'textreader'
    TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @path + 
                    '\convert.share -c -Usa -P' + @saPassword + 
                    ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                    ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                    @path + "\convert.share'" + '"'
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      SELECT @drive = SUBSTRING((SELECT DISTINCT RTRIM(LTRIM(SUBSTRING(text,
                                 CHARINDEX('path',text) + DATALENGTH('path'), 
                                 DATALENGTH(text)))) 
                                 FROM textreader WHERE text LIKE 'PATH%'),1,2)
    TRUNCATE TABLE textreader
  EXEC release_semaphore 'textreader' 

RETURN