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.

When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL variables.

Problem definition

I want to use the power of SQLCMD's variable expansion to substitute environment specific values in our deploy scripts instead of the existing tsql string building mash I've walked into. With the one exception of current environment, the use of SQLCMD to handle deploys has gone extremely well.

--
-- define 2 sqlcmd variables that will be expanded in scripts
--
:setvar dbServer "DEVA2\DEV2"
:setvar dbNotServer @@servername

SELECT
    '$(dbServer)' AS hard_coded_value
,   @@servername AS [servername]
,   '$(dbNotServer)' AS dbNotServer

And that generates the following results.

hard_coded_value  servername  dbNotServer
DEVA2\DEV2        DEVA2\DEV2  @@servername

Meat Loaf says 2 out of 3 ain't bad, but I'd rather have a 3 out of 3 solution. When that script gets deployed the test server, I don't want to trust the deployment folks with editing the script.

If the only solution to using SQLCMD is to invoke scripts completely from the command line, I can accept that but wanted to throw this out here as I'm green to using SQLCMD.

Desired output

:setvar dbNotServer @@servername
SELECT '$(dbNotServer)' AS worked

Results

worked
DEVA\DEV2

Fruitless pursuits

The first BOL link showed promise, all I had to do was use the SQLCMDSERVER but to no avail. Run within the context of SSMS in SQLCMD mode, it will throw a fatal scripting error

-- A fatal scripting error occurred.
-- Variable SQLCMDSERVER is not defined.
SELECT '$(SQLCMDSERVER)' AS [FatalScriptingError]

Tumbleweed update

2011-08-12 In an attempt to reduct my problem to the simplest form, based on the answers, I oversimplified my queries (my apologies). A portion of the query I used is below. The two reponders are correct in their answers that identify wrapping @@servername in tick marks results in the literal value and to get the expanded value, I'd need to unwrap it from the quotes. My desire was to use sqlcmd variable substitution rather than string building which unwrap would entail.

INSERT INTO
    dbo.SSISCONFIG
(
    ConfigurationFilter
,   ConfiguredValue
,   PackagePath
,   ConfiguredValueType
,   Application
,   Category
,   Subcategory
,   Comment
)
SELECT
    'Default.Accounting' AS ConfigurationFilter
,   'Data Source=$(dbServer);Initial Catalog=Accounting;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[Accounting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment
SELECT
    'Default.$(sqlVersion).CorporateReporting' AS ConfigurationFilter
,   'Data Source=$(dwServer);Initial Catalog=CRDS;Provider=SQLNCLI10.1;Integrated Security=SSPI;Packet Size=32767;' AS ConfiguredValue
,   '\Package.Connections[CorporateReporting].Properties[ConnectionString]' AS PackagePath
,   'String' AS ConfiguredValueType
,   'Defaults' AS Application
,   'Connection' AS Category
,   'Database' AS Subcategory
,   'Default connection string' AS Comment

Command line invocation works (as expected)

C:\>sqlcmd -E -d master -S DEVA2\DEV2 -Q "SELECT '$(SQLCMDSERVER)' AS [servername]"

I've tried and discarded other permutations of the :setvar x @@servername in an attempt to get the evaluated value of the database variable stored into the SQLCMD variable. At this point, I'm fairly certain the sqlcmd variables are susbstituted in queries prior to query compiliation but would love to be proven wrong.

BOL references

share|improve this question
add comment

2 Answers

Unless I'm missing something, doesn't this work, without the apostrophes?:

:setvar dbNotServer @@servername
SELECT $(dbNotServer) AS worked

It returns SERVER\INSTANCE for me in SQLCMD mode through SSMS.

share|improve this answer
add comment

Under "Desired Output", the T-SQL going to the server becomes:

SELECT '@@servername' AS worked

Putting single-quotes around @@servername makes it a string literal and will not be replaced with the actual name, which is why you are seeing @@servername as the value.

If you're looking to display the server name, remove the single-quotes, making your T-SQL:

SELECT @@servername AS worked

I'm not entirely clear I understand the question, but this is how you would get what's in your "Desired Output".

share|improve this answer
add comment

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.