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)

The variable source_server_name would be set to the value in server for id 1.

  • Not that I am aware of, or could figure out.
    – user507
    Jul 12, 2013 at 12:56
  • 1
    What you ask for is not possible per-se, because sqlcmd variables are evaluated by the client before the sql is ever sent to the server. So you are asking for the sql server to set the value of a variable that it never sees. Run a sql profiler trace on a sql script with sqlcmd variables and you'll see they have all already been evaluated.
    – Tim Abell
    Jun 22, 2015 at 18:28

1 Answer 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 + ''''
:OUT stdout
:r $(MyDir)\test.txt
SELECT $(ServerName)

You can see more examples here.

  • Thank you. Ive already left work so I will try out your suggestion on Monday Jul 12, 2013 at 15:32
  • I've updated my question Jul 15, 2013 at 11:14
  • This answer proved to be the most useful. Jul 18, 2013 at 9:13
  • 1
    I guess the important part here is that you have to have separate batches to 1) generate the SETVAR file, 2) execute it to initialise the script variable, and, finally, 3) use the script variable.
    – Andriy M
    Jun 25, 2015 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.