Search code examples
sqlsql-serverssmssqlcmd

A fatal scripting error occurred. Variable is not defined

When I run this query in SQLCMD mode in SSMS 2017 and SQL Server DB. It works fine.

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

Why I am getting error?

and how can I make my varibale Global, for example like 

SELECT '$(COMPUTERNAME)'
Solution

  • I don't think you can. SQLCMD variables are a client tool trick, they don't really exist as a SQL Server concept.

    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:

    If more than one type of variable has the same name, the variable with the highest precedence is used.

    1. System level environmental variables

    2. User level environmental variables

    3. Command shell (SET X=Y) set at command prompt before starting sqlcmd

    4. sqlcmd-v X=Y

    5. :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.

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