SQLCMD Mode – Setting Variables in SQL Server

sql serversql-server-2008-r2sqlcmd

Using SQL Server 2008 R2 Enterprise Edition

Consider the following statement:

:setvar source_server_name "SERVERNAME\INSTANCENAME"

Is it possible in SQLCMD mode to get that value using TSQL

Something like: :setvar source_server_name = SELECT @@servername

Thank you

Update 7/15/2013

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.

Best Answer

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.

Related Question