Using SQLCMD Variables

Question

Post reply

Using SQLCMD Variables

Steve Jones - SSC Editor

SSC Guru

Points: 734434
More actions
February 14, 2020 at 12:00 am

#3707066

Comments posted to this topic are about the item Using SQLCMD Variables

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

That does not match with this example:

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15

and the example is:

:setvar SQLCMDLOGINTIMEOUT 60

:setvar server "testserver"

:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE AdventureWorks2012;

SELECT FirstName, LastName

FROM Person.Person;

...there aren't any quotes. Is there a different behavior depending on the SQL version?

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 2

Good catch. Added the quotes to the SELECT.

That example doesn't need quotes for the connect. However, in selecting a value, it needs to be valid. In this case, the original question has this code executing:

select log.txt, myid, myname, mychar from dbo.MyTable;

Unless there's a column named log.txt, this is invalid. I've added quotes tot the SELECT. In the example you show, there isn't a need for quotes in the first variable, and neither part of the :connect line needs quotes.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

The question was what should replace xxx in " SELECT 'xxx', ... ".

There are already quotes around the xxx. Why would I include another set of quotes around the SQLCMD variable reference?

You can go ahead and try SELECT ''$(DirectoryR00tPath)'', but I don't think you'll be happy with the result.

Ryan C. Price Ten Centuries Points: 1184 More actions · Answer 4

Scott Coleman wrote:

The question was what should replace xxx in " SELECT 'xxx', ... ".
There are already quotes around the xxx. Why would I include another set of quotes around the SQLCMD variable reference?

this.

Having just spent a day writing code using sqlcmd variables (including variables for file/folder names), I answered with confidence, only to be told I was wrong ?!?!?!

This won't even parse:

:setvar DirectoryRootPath "Log.txt"


SELECT ''$(DirectoryRootPath)''

This reply was modified 4 years, 1 month ago by Ryan C. Price.

George Vobr SSCrazy Eights Points: 9915 More actions · Answer 5

I had more time on Sunday, but I didn't believe I'd spend nearly half a day with it. I will try to explain this using the examples below, which I compiled on SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64). I included notes in the script code that helped me understand the correct answer. And here is a link to DOCS "sqlcmd - Use with Scripting Variables".

--========================================
-- Set Query in SQLCMD Mode First --
--========================================
-- Examples according to QotD 2020-03-20:
------------------------------------------

:setvar DirectoryRootPath "C:\Log.txt"
--SELECT $(DirectoryRootPath);-- Error because it's like SELECT C:\Log.txt
SELECT '$(DirectoryRootPath)';  -- O.K. as SELECT 'C:\Log.txt'
GO

:setvar DirectoryRootPath 'C:\Log.txt'
SELECT $(DirectoryRootPath);--  O.K. as SELECT 'C:\Log.txt'
--SELECT '$(DirectoryRootPath)';-- Error because it's like SELECT ''C:\Log.txt''
GO

:setvar DirectoryRootPath C:\Log.txt
--SELECT $(DirectoryRootPath);-- Error because it's like SELECT C:\Log.txt
SELECT '$(DirectoryRootPath)';  -- O.K. as SELECT 'C:\Log.txt'
GO

:setvar DirectoryRootPath """C:\Log.txt"""
--SELECT $(DirectoryRootPath);-- Error Invalid column name 'C:\Log.txt'.
SELECT '$(DirectoryRootPath)';  -- O.K. as SELECT '"C:\Log.txt"'
GO
--====================================================================================
-- Examples according to DOCS:
------------------------------

:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title Ms.
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = '$(title)'-- O.K. as SELECT 'Ms.'
GO
--=================================================================
/*
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title Ms.
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = $(title)-- Error because it's like SELECT Ms.
GO
*/
--=================================================================
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title "Ms."
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = '$(title)'-- O.K. as SELECT 'Ms.'
GO

:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title 'Ms.'
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = $(title)-- O.K. as SELECT 'Ms.'
GO

This reply was modified 4 years, 1 month ago by George Vobr.
This reply was modified 4 years, 1 month ago by George Vobr.

hbrest Old Hand Points: 373 More actions · Answer 6

I totally agree with Scott and Ryan.

The question was not written properly.

I also work a lot with SQLCMD variables and the given answer from this QotD is not correct.