SQL Server Performance

Accessing Environmental variables

Discussion in 'General Developer Questions' started by DBA_Newbie, Jul 7, 2005.

  1. DBA_Newbie New Member

    Hi,

    I have created a environmental system variable that has some file path. Does anyone know how to read this system variable using system stored procedures. I know that we can use xp_regread, but I need specify the full path 'HKEY_LOCAL_MACHINE', 'SYSTEMControlSet001ControlSession ManagerEnvironment' to access the variable.

    declare @outVar varchar(50)
    EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEMControlSet001ControlSession ManagerEnvironment', 'MyVar', @outVar OUTPUT

    Is there any stored proc, where in I can just specify the environmental variable name ?

    Any help is highly appreciated.

    Thanks in advance..
  2. Adriaan New Member

    You could try something along these lines ...

    SET NOCOUNT ON
    DECLARE @outVar VARCHAR(50)
    CREATE TABLE #TMP (MyVar VARCHAR(50))
    INSERT INTO #TMP EXEC master.dbo.xp_cmdshell 'MyVar'
    SELECT @outVar = MyVar FROM #TMP WHERE MyVar IS NOT NULL

    I've tried this with 'Path' instead of 'MyVar' and it works, as long as the variable is the correct size (PATH can be rather long). There's also a Null row being returned, which you need to suppress.
  3. DBA_Newbie New Member

    SET NOCOUNT ON
    DECLARE @outVar VARCHAR(500)
    CREATE TABLE #TMP (MyVar VARCHAR(500))
    INSERT INTO #TMP EXEC master.dbo.xp_cmdshell 'MyVar'
    --SELECT @outVar = MyVar FROM #TMP WHERE MyVar IS NOT NULL
    select * from #tmp

    If I use PATH this works, but when I use MyVar it gives the following error

    'MyVar' is not recognized as an internal or external command,
    operable program or batch file.
    NULL

    Any pointers ?
  4. Adriaan New Member

    Try this one:

    INSERT INTO #TMP EXEC master.dbo.xp_cmdshell 'ECHO %MyVar%'
  5. DBA_Newbie New Member

    Nope ! No luck

    I get the output as
    %MyVar%
    NULL
  6. DBA_Newbie New Member

    I created a User Variable instead but I get the same output. However, I can get the same code work for the existing System/user variables. Anything new, I create, it doesnt work..

    thanks in advance,
  7. Adriaan New Member

    INSERT INTO #TMP EXEC master.dbo.xp_cmdshell 'SET MyVar'

    This returns:

    MyVar="<whatever>"

    ... in exactly the same format as the command line that set the environment variable - so you'll have to check the string to see the starting point of the value.
  8. DBA_Newbie New Member

    Cool ! this one works. Many Thanks.
    I will supress LHS and take only the path from the RHS after '='

Share This Page