Using SQL Server 2008 R2 Enterprise Edition
Consider the following statement:
:setvar source_server_name "SERVERNAMEINSTANCENAME"
Is it possible in SQLCMD mode to get that value using TSQL
:setvar source_server_name = SELECT @@servername
The two answers offered below did not quite give the desired result, so I’m adding a more relevant exampe.
:setvar source_server_name [myserver]
The variable source_server_name is set to the text string [myserver_1]
I’d like to be able to do this:
create table #tmp( id int identity(1,1), server sysname ) insert into #tmp values('myserver_1'),('myserver_2'); :setvar source_server_name = SELECT server FROM #tmp WHERE id = 1 select '$(source_server_name)' (No column name) myserver_1
The variable source_server_name would be set to the value in server for id 1.
I think you need to output your results to a file and bring them back in. Something like this should help get you close to what you are looking for:
:setvar MyDir "C:scripts" :OUT $(MyDir)test.txt PRINT ':SETVAR ServerName ''' + @@SERVERNAME + '''' GO :OUT stdout :r $(MyDir)test.txt GO SELECT $(ServerName)
You can see more examples here.