When I run this query in SQLCMD mode in SSMS 2017 and SQL Server DB. It works fine.
当我在SSMS 2017和SQL Server DB中以SQLCMD模式运行此查询时。它工作正常。
--SQLCMD mode
--DECLARE Global variables
:SETVAR daysBack -1
SELECT $(daysBack)
Result: -1
If I run only 1 line
如果我只跑一行
SELECT $(daysBack)
in the same window on the same connection, or in another tab. I am getting error
在相同连接的同一窗口中,或在另一个选项卡中。我收到了错误
fatal scripting error occurred. Variable daysBack is not defined.
发生了致命的脚本错误。变量daysBack未定义。
Why I am getting error?
为什么我收到错误?
and how can I make my varibale Global, for example like
以及如何使我的varibale Global,例如
SELECT '$(COMPUTERNAME)'
1 个解决方案
#1
1
I don't think you can. SQLCMD variables are a client tool trick, they don't really exist as a SQL Server concept.
我认为你不能。 SQLCMD变量是一个客户端工具技巧,它们并不真正作为SQL Server概念存在。
The connection and server are not aware of their existence and the system doesn't persist knowledge of variables used in previous batches when you submit a new batch.
连接和服务器不知道它们的存在,并且当您提交新批处理时,系统不会保留先前批次中使用的变量的知识。
From sqlcmd - Use with Scripting Variables:
从sqlcmd - 与脚本变量一起使用:
If more than one type of variable has the same name, the variable with the highest precedence is used.
如果多个类型的变量具有相同的名称,则使用具有最高优先级的变量。
System level environmental variables
系统级环境变量
User level environmental variables
用户级环境变量
Command shell (SET X=Y) set at command prompt before starting sqlcmd
在启动sqlcmd之前,在命令提示符下设置命令shell(SET X = Y)
sqlcmd-v X=Y
:Setvar X Y
:Setvar X Y.
Of these, the first three are effectively taken from when a particular process was launched, unless that process has specific tooling to allow environmental variable to be edited. Neither SSMS nor SQLCMD has such functionality so you cannot create a variable in these categories once the tool is already running.
其中,前三个是从特定过程启动时有效获取的,除非该过程具有允许编辑环境变量的特定工具。 SSMS和SQLCMD都没有这样的功能,因此一旦工具运行,您就无法在这些类别中创建变量。
The fourth is SQLCMD specific and has no equivalent in SSMS. Only the last one (using :setvar) remains, and this does not create a "global" variable (which are in fact just the environmental variables and the SQLCMD built-in variables).
第四个是SQLCMD特定的,在SSMS中没有等价物。只剩下最后一个(使用:setvar),这不会创建一个“全局”变量(实际上只是环境变量和SQLCMD内置变量)。
#1
1
I don't think you can. SQLCMD variables are a client tool trick, they don't really exist as a SQL Server concept.
我认为你不能。 SQLCMD变量是一个客户端工具技巧,它们并不真正作为SQL Server概念存在。
The connection and server are not aware of their existence and the system doesn't persist knowledge of variables used in previous batches when you submit a new batch.
连接和服务器不知道它们的存在,并且当您提交新批处理时,系统不会保留先前批次中使用的变量的知识。
From sqlcmd - Use with Scripting Variables:
从sqlcmd - 与脚本变量一起使用:
If more than one type of variable has the same name, the variable with the highest precedence is used.
如果多个类型的变量具有相同的名称,则使用具有最高优先级的变量。
System level environmental variables
系统级环境变量
User level environmental variables
用户级环境变量
Command shell (SET X=Y) set at command prompt before starting sqlcmd
在启动sqlcmd之前,在命令提示符下设置命令shell(SET X = Y)
sqlcmd-v X=Y
:Setvar X Y
:Setvar X Y.
Of these, the first three are effectively taken from when a particular process was launched, unless that process has specific tooling to allow environmental variable to be edited. Neither SSMS nor SQLCMD has such functionality so you cannot create a variable in these categories once the tool is already running.
其中,前三个是从特定过程启动时有效获取的,除非该过程具有允许编辑环境变量的特定工具。 SSMS和SQLCMD都没有这样的功能,因此一旦工具运行,您就无法在这些类别中创建变量。
The fourth is SQLCMD specific and has no equivalent in SSMS. Only the last one (using :setvar) remains, and this does not create a "global" variable (which are in fact just the environmental variables and the SQLCMD built-in variables).
第四个是SQLCMD特定的,在SSMS中没有等价物。只剩下最后一个(使用:setvar),这不会创建一个“全局”变量(实际上只是环境变量和SQLCMD内置变量)。