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

2 Answers 2

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

This will work using SQLCMD mode in SSMS :

:setvar source_server_name  @@servername
SELECT $(source_server_name) AS source_server_name 

Refer to below links for more details :

  1. Use sqlcmd with Scripting Variables
  2. SQL Server Management Studio: Hacking SQLCMD Mode
  3. The SQLCMD workbench

EDIT:

You need to declare an extra variable to do it :

declare @source_server_name sysname
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp;
create table #tmp(
id int identity(1,1),
server sysname
)
insert into #tmp values('myserver_1')
insert into #tmp values('myserver_2')

select @source_server_name = server from #tmp where id = 1
:setvar source_server_name @source_server_name
select $(source_server_name) as output

output
--------------------------------------------------------------------------------------------------------------------------------
myserver_1
share|improve this answer

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.