sql - 主机 SQL Server 的 ALLUSERSPROFILE？

问题描述

我需要编写一个与 SQL Server 2008R2 兼容的脚本来创建共享。该脚本将从 VB6 代码内部执行，但我很确定这是一个有争议的问题。

以下是最后的伪代码

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'
    ....

我已经看到它ENVREFERENCE在 SQL Server 2008 R2 中不可用（这是我必须为我们的客户提供的最旧版本）

但是我不喜欢使用ENVREFERENCE任何一个 - 我只是希望主机给我它的环境返回 ALLUSERSPROFILE （显然我不应该从应用程序中的执行代码中获取这个值，因为我将获得客户端的值而不是期望的值HOST 服务器的值；因此我希望从 T-SQL 脚本执行它）

那么是否有任何 SQL 专家对此有所了解？

提前致谢。

哈利

标签： sqlsql-serversql-server-2008environment-variablesallusersprofile

不能说这完全无懈可击，但我通过了最初的几十次测试。感谢Jeroen Mostert，我意识到我已经可以访问主机服务器上的 %ALLUSERSPROFILES% 了。然后脚本变成了我可以做的事情......

-- 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

希望这对某人有用。你们总是为我加油！

sql - 主机 SQL Server 的 ALLUSERSPROFILE？

问题描述

解决方案

推荐阅读