ALLUSERSPROFILE OF the HOST SQL Server?

Question

I need to write a SQL Server 2008R2 compatible script to create a share. The script will be executed from inside VB6 code but I am pretty sure that's a moot point here.

The following is PSEUDOCODE at the end

CREATE PROCEDURE [dbo].[create_Server_share]
     @TheShare VARCHAR(50),
     @TheDIR VARCHAR(250) = NULL
AS
BEGIN
    IF (@TheDIR IS NULL) -- ALLUSERSPROFILE usually C:\Programdata
        SET @TheDIR = ENVREFERENCE('%ALLUSERSPROFILE%')+ '\XYZ'
    ....

I already see that ENVREFERENCE is NOT available in SQL Server 2008 R2 (which is the oldest version I have to accomodate for our clients)

But I am not married to using ENVREFERENCE either - I just want the HOST MACHINE to give me its environment return for ALLUSERSPROFILE (obviously I should not grab this value from the executing code in the application because I will be getting the CLIENT's value instead of the desired HOST server's value; hence my desire to execute it from the T-SQL script)

So do any SQL guru's have some insight into this?

Thanks in advance.

Harry

Answer 1

Can't say this is completely bulletproof, but I past the first few dozen tests. Thanks to Jeroen Mostert I realized I had my access to %ALLUSERSPROFILES% already on the Host server. the script then became something I could do...

-- create_Server_share.sql written by Harry Abramowski on 6/26/2018
-- we ARE NOT doing these steps in a command line prompt nor in VB6 
-- because this share has to be made **ON** THE SERVER!
-- stored procs are a bitch!
go
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT 1
      FROM   INFORMATION_SCHEMA.ROUTINES
      WHERE  ROUTINE_NAME = 'create_Server_share'
             AND SPECIFIC_SCHEMA = 'dbo')
  BEGIN
    DROP PROCEDURE create_Server_share
  END
go

CREATE PROCEDURE [dbo].[create_Server_share]
 @TheShare   varchar(50),
 @TheDrive   char=null,
 @TheDIR     varchar(250)=null
AS

BEGIN
if (@TheDIR is null)
    set @TheDIR = '%ALLUSERSPROFILE%\XYZ'

if (@TheDrive is null)
    set @TheDrive = 'X'

DECLARE @answer as varchar(MAX)
declare @myString as varchar(1000)
DECLARE @i INT


-- JUST in case its not already set up, let's enable use of the reconfig in SQL
    EXEC sp_configure 'show advanced options', 1; --might not be needed
    RECONFIGURE;

    EXEC sp_configure 'xp_cmdshell',1; -- wont hurt to assume it IS needed
    RECONFIGURE;

-- net share XYZShare=C:\Programdata\XYZ /grant:everyone,FULL
a_redo:
set @myString = ('net share ' + @TheShare  +'=' + @TheDIR + ' /grant:everyone,FULL')
CREATE TABLE #xyzout ([outputtext] varchar(MAX))
Insert into #xyzout (outputtext) EXECUTE  xp_cmdshell @myString
-- what about The system cannot find the file specified.???
if exists(select #xyzout.outputtext from #xyzout where #xyzout.outputtext = 'The system cannot find the file specified.')
    begin
    set @myString = ('mkdir ' + @TheDIR)
    EXECUTE xp_cmdshell @mystring
    print ('The directory ' + @TheDIR + ' was created')
    drop table #xyzout
    goto a_redo -- yeah I know!
    end
 --  was there an error - was it just an "already exists" message? let's see
 set @answer =  (select top 1 outputtext from #xyzout)
 print @answer

-- now update systemProps table so the client machines know there's a share and what drive they should map it to
if charindex('system error',lower(@answer))= 0 
    IF NOT EXISTS (SELECT a.* FROM syscolumns a, sysobjects b
               WHERE  a.name = 'XYZShare' AND
                      a.id = b.id AND
                      b.name = 'systemProps')
            ALTER TABLE system ADD XYZShare NVARCHAR(1000) NULL ;

if charindex('system error',lower(@answer))= 0 
    begin
    update systemProps set XYZShare = (@TheDrive  + '=\\' + 
CAST(serverproperty('MachineName') as varchar)  + '\'  + @TheShare );
    select systemProps.XYZShare from systemProps;
    return 0;
    end
else
    begin
    select * from #xyzout where not(outputtext is null)
    return 1;
    end

EXEC sp_configure 'xp_cmdshell',0; --let's leave that off?
RECONFIGURE;
DROP TABLE #xyzout

---- if you need to delete the SHARE ITSELF you COULD use this: EXEC XP_CMDSHELL 'net share Xshared /delete' 
--HOWEVER you can easily do either from the windows explorer in NETWORK view or My Computer view

END
    GRANT EXECUTE ON dbo.create_Server_share TO PUBLIC

GO

Hope this is useful to someone. You guys always come through for me!