13

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.

  • 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 '15 at 18:28
9

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.

  • 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
  • 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 '15 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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