Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Not that I am aware of, or could figure out. –  Shawn Melton Jul 12 '13 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 at 18:28

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
Thank you. Ive already left work so I will try out your suggestion on Monday –  Craig Efrein Jul 12 '13 at 15:32
    
I've updated my question –  Craig Efrein Jul 15 '13 at 11:14
    
This answer proved to be the most useful. –  Craig Efrein Jul 18 '13 at 9:13
    
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 at 16:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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