在T-SQL中使用环境变量

How can I read the value of a system environment variable in a T-SQL script?

如何在T-SQL脚本中读取系统环境变量的值?

This is to run on SQL Server 2005.

这是在SQL Server 2005上运行。

6 个解决方案

#1

This should give you a list (provided you allow people to execute xp_cmdshell)

这应该给你一个列表(假设你允许人们执行xp_cmdshell)

exec master..xp_cmdshell 'set'

exec master..xp_cmdshell'set'

Note: xp_cmdshell is a security hazard ...

注意:xp_cmdshell存在安全隐患......

You could also do this with a managed stored proc an extended stored proc or via a com component.

您还可以使用托管存储过程扩展存储过程或通过com组件执行此操作。

#2

xp_cmdshell is generally best avoided for security reasons.

出于安全原因,通常最好避免使用xp_cmdshell。

You're better off using a CLR assembly. Here's a good introduction to creating a CLR assembly.

你最好使用CLR程序集。这是创建CLR程序集的一个很好的介绍。

You can use System.Environment.GetEnvironmentVariable() in C# - you'll find more info on how to do that here.

您可以在C#中使用System.Environment.GetEnvironmentVariable() - 您将在此处找到有关如何执行此操作的更多信息。

#3

Hey, if you want to get the server name, just call SELECT @@SERVERNAME

嘿,如果你想获得服务器名称,只需调用SELECT @@ SERVERNAME即可

#4

To "read the value of a system environment variable in a T-SQL script" you can set SQL Management Studio to use "sqlcmd Mode".

要“读取T-SQL脚本中系统环境变量的值”,可以将SQL Management Studio设置为使用“sqlcmd模式”。

Then you can use like this:

然后你可以像这样使用:

Print '$(TEMP)'

:r $(Temp)\Member.sql
go

I'm not sure how this is done outside of "SQL Management Studio" but it should be hard to find out.

我不确定这是如何在“SQL Management Studio”之外完成的,但应该很难找到。

#5

Thanks for the answers. They helped me get to a working solution, although this is probably not the most advanced method:

谢谢你的回答。他们帮助我找到了一个有效的解决方案,尽管这可能不是最先进的方法:

declare @val varchar(50)
create table #tbl (h varchar(50))
insert into #tbl exec master..xp_cmdshell 'echo %computername%'
set @val = (select top 1 h from #tbl)
drop table #tbl

Specifically I was trying to get the hostname, the echo %computername% could be replaced with the hostname system command. But this now works for any environment variable.

具体来说,我试图获取主机名,echo%computername%可以用hostname系统命令替换。但这现在适用于任何环境变量。

#6

To determine a specific environment variable in T-SQL (MS SQL Server) you can do something like:

要在T-SQL(MS SQL Server)中确定特定的环境变量,您可以执行以下操作:

Grant Security Permissions

授予安全权限

use [master]

execute sp_configure 'show advanced options', 1
reconfigure
go

execute sp_configure 'xp_cmdshell', 1
reconfigure
go

grant execute on xp_cmdshell to [DOMAIN\UserName]

grant control server to [DOMAIN\UserName]
go

Source: https://*.com/a/13605864/601990

Use Environment Variables

使用环境变量

-- name of the variable 
declare @variableName nvarchar(50) = N'ASPNETCORE_ENVIRONMENT'

-- declare variables to store the result 
declare @environment nvarchar(50)
declare @table table (value nvarchar(50))

-- get the environment variables by executing a command on the command shell
declare @command nvarchar(60) = N'echo %' + @variableName + N'%';
insert into @table exec master..xp_cmdshell @command;
set @environment = (select top 1 value from @table);

-- do something with the result 
if @environment = N'Development' OR @environment = N'Staging'
    begin
    select N'test code'
    end
else 
    begin
    select N'prod code'
    end

#1